Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - Managing Tables and Running Queries with PHP Scripts

By: FYIcenter.com

Part:   1  2  3  4  5  6   7  8 

(Continued from previous part...)

How To Perform Key Word Search in Tables?

The simplest way to perform key word search is to use the SELECT statement with a LIKE operator in the WHERE clause. The LIKE operator allows you to match a text field with a keyword pattern specified as '%keyword%', where (%) represents any number of any characters. Any single quote (') in the keyword needs to be protected by replacing them with two single quotes (''). The tutorial exercise below shows you how to search for records whose "notes" contains "e":

<?php
  include "mysql_connection.php";

  $key = "e";
  $key = str_replace("'", "''", $key);
  $sql = "SELECT id, url, notes FROM fyi_links"
    . " WHERE notes LIKE '%".$key."%'";
  $rs = mysql_query($sql, $con);
  while ($row = mysql_fetch_assoc($rs)) {
    print($row['id'].", ".$row['url'].", "
    . $row['notes']."\n");
  }
  mysql_free_result($rs);

  mysql_close($con); 
?>

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

102, dba.fyicenter.com, Nice site.
202, www.yahoo.com, It's another search engine!
301, netscape.com, Added long time ago!
302, myspace.com, Added today!

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:

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

Part:   1  2  3  4  5  6   7  8