MySQL Tutorial - Build WHERE Criteria with Web Form Data

Home >> FAQs/Tutorials >> MySQL Tutorials

By: FYIcenter.com

How To Build WHERE Criteria with Web Form Search Fields?

If your PHP script is linked to a Web form which takes search key words for multiple data fields. For example, your Web form asks your visitor to search for Website links with a URL search field, a Website title search field, a description search field, and a comment search field.

Now you have to build a nice WHERE criteria string that meets the following requirements:

Search fields with no data entered by visitors should not be included in the criteria.
Search values entered by visitors should be trimmed to remove leading and trailing space characters.
Empty search values after trimming should not be included in the criteria.
Single quote (') characters in search values should be protected.
backslash (\) characters in search values should be protected.

The tutorial script below shows you a good sample that meets the above requirements:

<?php
  $_REQUEST = array("title"=>"  Joe's brother\'s ",
    "description"=>"c:\windows\system ",
    "comment"=>" best   ");

  $sql = "SELECT * FROM siteLinks WHERE 1=1";
  $url = getFormParam("url");
  $title = getFormParam("title");
  $description = getFormParam("description");
  $comment = getFormParam("comment");
  if (strlen($url) > 0) 
    $sql .= " AND url LIKE '%".$url."%'";
  if (strlen($title) > 0) 
    $sql .= " AND title LIKE '%".$title."%'";
  if (strlen($description) > 0) 
    $sql .= " AND description LIKE '%".$description."%'";
  if (strlen($comment) > 0) 
    $sql .= " AND comment LIKE '%".$comment."%'";
  print("SQL statement:\n");
  print($sql."\n");
 
function getFormParam($p) {
   if (isset($_REQUEST[$p])) {
      return str_replace("\\", "\\\\", 
         str_replace("'", "''", 
         trim($_REQUEST[$p])));
   } else {
      return "";
   }
}
?>

If you run this script, you will get something like this:

SQL statement:
SELECT * FROM siteLinks WHERE 1=1 
  AND title LIKE '%Joe''s brother\\''s%'
  AND description LIKE '%c:\\windows\\system%'
  AND comment LIKE '%best%'

You should learn a couple of things in this script:

isset($_REQUEST[$p]) is used to detect if the visitor has actually entered any value or not to a field.
trim($s) is used to trim off leading and trailing space characters.
str_replace("'", "''",$s) is used to replace single quote (') characters with ('').
str_replace("\\", "\\\\",$s) is used to replace backslash (\) characters with (\\). You need to repeat backslashes because PHP string literals can not take backslashes as is.
getFormParam($p) is created do all the input value processing work in a single function. getFormParam($p) also makes sure that all input values are defined as strings, even if they are not defined.
The WHERE clause is initialized with a dummy condition "1=1", so that all other conditions can be prefixed with the key word "AND".
$_REQUEST() is created for testing purpose only. You need to remove it, when you move this script to a real Web page.
The final WHERE criteria generated in the output SQL statement seems to be correct.

(Continued on next topic...)

MySQL Tutorials:

More...