How To Build WHERE Criteria with Web Form Search Fields

Q

How To Build WHERE Criteria with Web Form Search Fields? - MySQL FAQs - Managing Tables and Running Queries with PHP Scripts

✍: FYIcenter.com

A

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.

2007-05-11, 6033👍, 0💬