Home >> FAQs/Tutorials >> MySQL Tutorials

MySQL Tutorial - Build WHERE Criteria with Web Form Data

By: FYIcenter.com

(Continued from previous topic...)

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

  1. How To Create a New Table?
  2. How To Get the Number of Rows Selected or Affected by a SQL Statement?
  3. How To Insert Data into an Existing Table?
  4. How To Fix the INSERT Command Denied Error?
  5. How To Insert Multiple Rows with a SELECT Statement?
  6. What Is a Result Set Object?
  7. How To Query Tables and Loop through the Returning Rows?
  8. How To Break Query Output into Pages?
  9. How To Update Existing Rows in a Table?
  10. How To Delete Existing Rows in a Table?
  11. How To Quote Text Values in SQL Statements?
  12. How To Quote Date and Time Values in SQL Statements?
  13. How To Display a Past Time in Days, Hours and Minutes?
  14. How To Perform Key Word Search in Tables?
  15. How To Build WHERE Criteria with Web Form Search Fields?
  16. How To Query Multiple Tables Jointly?
  17. How To Define the ID Column as Auto-Incremented?
  18. How To Get the Last ID Assigned by MySQL?

MySQL Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...