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

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:

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.