Php query help

[rajeesh_rsn]

Hai I had a search script in my web page. There are two inputs

1. Type
2. Location ( list with values --> Any,India,China)
And location is not mandatory for search.

When user submit search in database and show results.
my query is like this

"SELECT * from dbname where type='$type' and location='$location'"

Works fine. And if user select location "any" then this will work

"SELECT * from dbname where type='$type'"

. I simply do it with "if" statement.

if($location=="any"){
do
}else {
do
}

But now I had more than 4 fields like gender, married,with photo....

How can I do it with a simple query.. I think if is not possible.

Please give me a little help
Thanks
Rajeesh

[cwarn23]

If you meen to check 4 more fields in the mysql query then you will need to specify them in one long string like the following:

mysql_query('SELECT * from dbname WHERE type="'.mysql_real_escape_string($type).'" AND location="'.mysql_real_escape_string($location).'" AND gender="'.mysql_real_escape_string($gender).'" AND married="'.mysql_real_escape_string($married).'" AND photo="'.mysql_real_escape_string($photo).'"');

I think that's what your talking about - is there any way of making the mysql_query shorter and the answer is not unless you want the query to be able to select a wider range of rows by specifying fewer columns after the where clause.

[rajeesh_rsn]

Ok Thankyou for your help.

My doubt is if location = any then we need to avoid location from query. And if gender= any then also we need to avoid that. I think we can use " if " statement if up to 2-3 conditions. Now I need to compare all and search

Please help

Thanks
Rajeesh

If you meen to check 4 more fields in the mysql query then you will need to specify them in one long string like the following:

mysql_query('SELECT * from dbname WHERE type="'.mysql_real_escape_string($type).'" AND location="'.mysql_real_escape_string($location).'" AND gender="'.mysql_real_escape_string($gender).'" AND married="'.mysql_real_escape_string($married).'" AND photo="'.mysql_real_escape_string($photo).'"');

I think that's what your talking about - is there any way of making the mysql_query shorter and the answer is not unless you want the query to be able to select a wider range of rows by specifying fewer columns after the where clause.

[JRSofty]

Hi Rajeesh,

What you are attempting to do is kind of like producing a dynamic SQL statement. Actually it's not as hard as it seems and you are on the right path.

Basically you should start with the main part of the query before the WHERE. Then gradually check what the values are and add them to the WHERE unless they == 'any' then you can just skip it. Here's my example:

<?php
     $query = "SELECT * FROM table ";
     $where = "";
     if($type != 'any'){
          $where .= "type='{$type}'";
     }
     if($location != 'any'){
          $where .= " AND location='{$location}'";
     }
     if($gender != 'any'){
          $where .= " AND gender='{$gender}'";
     }
     if($married != 'any'){
          $where .= " AND married='{$married}'";
     }
     if($photo != 'any'){
         $where .= " AND photo='{$photo}'";
     }
     // now we put it all together
     if($where != ''){
          $query .= " WHERE {$where}";
     }

     $qid =  mysql_query($query);
?>

My code above assumes that you have already ran your variables (user inputs) through a mysql_real_escape_string() function call).