Generate sql string to query MySQL DB ?

[MitkOK]

Hi folks.

I Have a question :

I'm interested how you generate query string to search DB with more than 1 form with LIKE.

For example :

We have $_POST['name'], $_POST['title'], $_POST['phone'], $_POST['email']. How do you generate sql query to search with two criteria ( name and phone ) ?

Thanks.

- Mitko Kostov.

[Cerberus]

Do you just mean the sql statement?

Something like this...

$sql="select * from table where name = ' " . $_POST[name] . " ' ";
$sql = $sql . " and phone = ' " . $_POST[phone] . " ';";

[MitkOK]

Yes, that's right. But what if $_POST['name'] is not set ? And what if there are more fields to check ?

if (isset($_POST['name'])) { $query.="WHERE name LIKE '%$_POST'name']'"; } 
 else { $query.="AND name LIKE '%$_POST['name']'"; } 

if (isset($_POST['title'])) { $query.="WHERE title LIKE '%$_POST['title']'"; } 
 else { $query.="AND title LIKE '%$_POST['title']'"; } 

if (isset($_POST['email'])) { $query.="WHERE email LIKE '%$_POST['email']'"; } 
 else { $query.="AND email LIKE '%$_POST['email']'"; }

Now think that we have 15 fields to check. Is there an easy way to check ( loop maybe ) ?

[MitkOK]

The previous post is wrong, I'm sorry.

I cannot edit it.

[Cerberus]

this is really rough but i think you mean something along these lines

$sql = "select * from table name where ";
$start;

if(isset $_POST['name'])
{
    $sql = $sql . $_POST['name'];
    $start = 1;
}

if(isset $_POST['phone'])
{
    if($start ==1)
    {
        $sql = $sql . "and '". $_POST['name'] ."'";
    }
    else
    {
        $sql = $sql . $_POST['name'];
    }
}

$sql = $sql . ";";

[MitkOK]

I want elegant way to check and generate.

Now think that we have 15 fields to check. Is there an easy way to check ( loop maybe ) ?

[Cerberus]

I don't know, i'll have to have a think about it. Perhaps using an associative array.

[MitkOK]

This I wrote a function for my purpose, it simple but works for me :

function genQuery() {

  $row_names = array("name","email","phone","title");

  $post_values = array($_POST['name'], $_POST['email'], $_POST['phone'], $_POST['title']);

  $num = count($post_values);

  $sqlString = "SELECT * FROM t WHERE";

for ($i=0;$i<$num;$i++) {

  if (empty($post_values[$i]))  {   $sqlString.=""; } 
  
   if (($post_values[$i]) && ($i==0))  {   $sqlString.=" $row_names[$i] LIKE '%$post_values[$i]%'";   }
  
   if (($post_values[$i]) && ($i!=0))  {  $sqlString.=" $row_names[$i] LIKE '%$post_values[$i]%'";   }
     
 }
	 
  $sqlString = preg_replace("/%'/", "%' AND ", $sqlString);
  $len = strlen($sqlString);
  $len=$len-4;
  $sqlString = substr($sqlString, 0 , $len);     
	
  return $sqlString;
		
}

[Cerberus]

Good solution.

[MitkOK]

Hi.

I don't think it's good, but at least works form me.

- Mitko Kostov

[dezignwork]

Have POST as an argument to the function and then run a loop:

foreach($_POST as $key => $value) {
if(!empty($_POST[$key])) {
$sql_where.=" AND $_POST[$key] LIKE '$_POST[$value]'";
}
}

... or something like that. I think it's best to have your custom functions that you know by heart. They always work best :)