How To Build WHERE Criteria with Web Form Search Fields in MySQL?

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.

⇒ Query Multiple Tables Jointly in MySQL

⇐ Key Word Search in Tables in MySQL

⇑ Managing Tables and Running Queries with PHP for MySQL

⇑⇑ MySQL Database Tutorials