Simple SQL Search Using PHP and MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

<?php
// Set up our error check and result check array
$error = array();
$results = array();
 
// First check if a form was submitted. 
// Since this is a search we will use $_GET
if (isset($_GET['search'])) {
   $searchTerms = trim($_GET['search']);
   $searchTerms = strip_tags($searchTerms); // remove any html/javascript.
 
   if (strlen($searchTerms) < 3) {
      $error[] = "Search terms must be longer than 3 characters.";
   }else {
      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.
   }
 
   // If there are no errors, lets get the search going.
   if (count($error) < 1) {
      $searchSQL = "SELECT sid, sbody, stitle, sdescription FROM simple_search WHERE ";
 
      // grab the search types.
      $types = array();
      $types[] = isset($_GET['body'])?"`sbody` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['title'])?"`stitle` LIKE '%{$searchTermDB}%'":'';
      $types[] = isset($_GET['desc'])?"`sdescription` LIKE '%{$searchTermDB}%'":'';
 
      $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked)
 
      if (count($types) < 1)
         $types[] = "`sbody` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked
 
          $andOr = isset($_GET['matchall'])?'AND':'OR';
      $searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `stitle`"; // order by title.
 
      $searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");
 
      if (mysql_num_rows($searchResult) < 1) {
         $error[] = "The search term provided {$searchTerms} yielded no results.";
      }else {
         $results = array(); // the result array
         $i = 1;
         while ($row = mysql_fetch_assoc($searchResult)) {
            $results[] = "{$i}: {$row['stitle']}<br />{$row['sdescription']}<br />{$row['sbody']}<br /><br />";
            $i++;
         }
      }
   }
}
 
function removeEmpty($var) {
   return (!empty($var)); 
}
?>

Although the code may look "complex" it really is not. First we start off by defining our arrays for errors and results. This will prevent any errors from coming of error or results not being an array, because we defined them as an array already.

Next we test to see if the form was submitted by checking to see if "search" was populated. This will prevent issues from occurring in case this is the first page load. Trimming the value of the form search will guarantee that it will not search for white spaces at the beginning or end of the terms. Stripping the tags will remove any HTML code present (you may want to omit this, it depends on what your need is). Finally we do some validation checks to make sure that some terms were actually passed through and that they contained enough characters to not return everything.

Once we are passed the validation it is time to take a precautionary measure by sanitizing our data with mysql_real_escape_string for database input to prevent SQL Injection from occurring. Next we will setup our SQL statement. We start out by defining the statement. Next we setup an array for types, this is because the search can be done on multiple fields. By setting up the search this way you just need the user to select a checkbox and those fields will be included in the search. Once we have populated the types array we want to remove any of the types that were not populated. This is being done by the function removeEmpty() which is custom defined. The removeEmpty function in use with array_filter will go through each item in the array and test if that item has a value using empty. If not it removes it from the array, which makes less code and work for us. The filtering of the array is to prevent a whitespace being used as a condition and returning everything.

Now if the types contained more than 1 item in the array, we can do our search. Imploding the array types will allow us to use our checkbox to see what the user wants matched. To do this we define a dynamic variable $andOr which changes depending on what the user selects. If that want it any one of the fields we use " OR " if they want it in all of the fields we use " AND " which ensures that we get the results the users want. Finally we run the query and grab our results, populate the results array and viola. We have our search results!