Generate sql string to query MySQL DB ?

[Flyier]

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 ) ?

I can check if $_POST values are set but what if one of them is not set ? And what if there are more fields to check ?

Example :

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


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 . ";";

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

Thanks.

• Mitko Kostov

[johanafm]

First off, make sure you enter whitespaces as needed in your query string.

Let's assume $POST['name'] and ['title'] contains the strings "Name" and "Title".

$query = "(...) "
$query.="AND name LIKE '%$_POST['name']'"
$query.="AND title LIKE '%$_POST['title']'"

$query will now contain:
"(...) AND name LIKE 'NameAND title LIKE 'Title'

As such, the second line of code above would have to be:

$query.="AND name LIKE '%$_POST['name']' "

You could just as well have a leading whitespace in the third line. Whichever way you prefer doing it will work. Also, do note that there is no reason not to allways use trailing whitespace as well as a leading whitespace. It will still generate valid SQL questions, and then it does not matter if a whitespace should be lacking in some other part of the code, yours or someone elses that you use.

To reduce how much coding you need to do you can easily use a loop. One simple way is to use the same form control names for all controls and just append a digit to them. Then you can use your loop index to specify each of them. That may however, reduce readability somewhat, so you might want to go with an array instead and keep your old form names. Besides, you stil must keep track of what form control corresponds to what database table field:

$form_controls = array(1=>'name', 2=>'title');

To deal with unset controls, you'd have to make sure you deal with any special cases in the query string:
// in this case you're good to go with a loop and: .= AND next clause
if $query contains: " WHERE some_field = some_value "

// however, in other cases you might need to do something else first
if $query is null or contains: "" (empty string)

And there are various ways to deal with that:
2 loops:

// assuming the controls are named the same as the fields
// and using $form_controls from above
$index = 0
while ($query == "") {
	if (isset($_POST[$form_controls[$index++]])
		$query = " WHERE $form_controls[$index] =
				$_POST[$form_controls[$index]] ";
}

$i_max = count($form_controls);

for ($index++; $index < $i_max; $index++) {
	if (isset($_POST[$form_controls[$index]])
		$query .= " AND $form_controls[$index] =
				$_POST[$form_controls[$index]] ";
}

Just a few suggestions. Code above is written on the fly in here and untested, so don't assume it's actually working and especially make sure $index is used appropriately etc.

[Flyier]

First i want to thank johanafm for the help.

Second I wrote a simple function for my purpose :

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;

}