How write dynamic query staring using PHP.

Question

Dear experts, Let say I have to search 3 co-related options 1)name 2)address 3)Phone. So, I have to write 8[eight] separate queries.pls see the example below….

  $name= $_POST['name'];      
  $address = $_POST['address'];
  $phone= $_POST['phone'];
  if($name!=""&& $address==""&& $phone=="")
 {  $searching_query="SELECT id,name,address from info  where info LIKE '%$info%'";}        
   .....................................    
   ......................................
   ..........................................
   else if($name!=""&&$address==""&&$phone!="")
 {  $searching_query="SELECT id,name,address  from  info where name LIKE '%$ name%' AND phone LIKE '$phone' ";  }   
  else if ($name!=""&&$address!=""&&$phone!="")
 {  $searching_query="SELECT id,name,address  from  info where name LIKE '%$ name%' AND address LIKE '%$address%' AND phone LIKE '$phone' ";}

So, if the correlated searching option is more than that then I have to write more and more queries.

Now my question is: how can I write the exact query dynamically. I mean WHERE clause of query will be generated based on posted values.

Please help! Thx,riad

Answer 1

You can always 'join' array entries to a string with a separator. If in this case you would use "AND" as your separator, you may be helped:

$like_clauses = array();

$fields = array( "phone", "address", "name" ) ;

foreach( $fields as $field) {
  if( !array_key_exist( $field, $_POST ) ) continue;

  $like_clauses[$field]=
    $field." LIKE '%"
    // thanx to @darko petreski's comment:
    .mysql_real_escape_string($_POST[$field]) 
    ."%'";

}

$query = "SELECT id,name,address from info where "
         .implode( "AND", $like_clauses );

(note: untested - grab the idea)

EDIT -- added mysql_real_escape_string to avoid injection vulnerability

Answer 2

Use arrays! and escape user input with vendor specific functions to protect against SQL injections. mysql_real_esacpe_string() should be used in this case.

$where = array();
foreach(array('name','address','phone') as $key) {
    if(isset($_POST[$key]) && !empty($_POST[$key])) {
        $where[] = $key . " LIKE '%" . mysql_real_escape_string($_POST[$key]) . "%'";
    }
}
$query = "SELECT id,name,address FROM info";
if(empty($where)) {
    $query .= "WHERE info LIKE '%" . mysql_real_escape_string($info) . "%'";
} else {
    $query .= "WHERE " . implode(' AND ',$where);
}