Hackosis is an Open Blog. You Can Participate.

  • 06
  • NOV


There might be many reasons why you may be storing data in a database. As Google has showed us, searching data is an invaluable tool that can help minimize the time that it takes to find the information you need.

I would like to show you how you can build your own PHP search engine to find the information you seek in a MySQL database.

First you will need a LAMP to work from. For Windows users a quick and dirty way is to download and install XAMPP. For Linux users try searching Google for a tutorial.

OK, now we will skip to the part where you have a working LAMP install and a database that needs searching from a web interface. I will assume that you have basic knowledge of and where to put your PHP files and also some basic SQL query syntax.

NOTE: I am not including “<?php” at the beginning of documents, nor “?>” at the end I will assume that you will know that these are the operators to signify php code within a file. You may also download the zip file that contains all of the code examples listed below at the end of this document.

Step 1: We are going to create a config.php file with our database information. Please fill in the correct information for your MySQL database and save the file:

  1. $dbhost=‘′;
  2. $dbusername=‘db_username’;
  3. $dbuserpass=‘db_password’;
  4. $dbname = ‘db_name’;

Step 2: Construct the search.php file.

It is always a good practice to make verbose comments in your code. If someone else comes along they might have some questions and may need to contact you.

  1. /**************************************************************************************
  2. * Main Search Page – search.php
  3. * Author: Your Name <[email protected]>
  4. * This file searches the database
  5. **************************************************************************************/

Next, we are going to call the config.php file that we created in Step 1. We could just use ‘include’ instead of ‘require’, but this file is critical to our application, so we want the script to fail if it cannot find the ‘config.php’ file. You would also want to store this in a different directory if our PHP application had many files, but since there are only two, we will put it in the same place as search.php.

  1. //Get variables from config.php to connect to mysql server
  2. require ‘config.php’;

After that, we are going to initiate a connection to our database. This pulls the variables from the config.php that we specified. If it cannot connect, it will print ‘Cannot select database’.

  1. // connect to the mysql database server.
  2. mysql_connect ($dbhost$dbusername$dbuserpass);
  3. //select the database
  4. mysql_select_db($dbname) or die(‘Cannot select database’);

Now lets insert some code to pull a variable from the search form that we will create later in the file. This basically says, if the search variable is set through the form, retrieve it and store it. There is no sense in storing it if it is not set. Also, the curly brackets are the enclosure for the if statement.

  1. //search variable = data in search box or url
  2. if(isset($_GET[’search’]))
  3. {
  4. $search = $_GET[’search’];
  5. }

We don’t want extra whitespace in the search variable as it will make a pure wild card and return all results; so we trim it like this:

  1. //trim whitespace from variable
  2. $search = trim($search);
  3. $search = preg_replace(‘/\s+/’‘ ‘$search);

Here comes the fun part. What we are doing here is seperating the words of the search variable, delimited by spaces and storing them in an array inside the keywords variable. I told you it was fun.

  1. //seperate multiple keywords into array space delimited
  2. $keywords = explode(" "$search);

Now, just to double check ourselves we are going to make sure that no arrays are empty, as said before, if this is the case, it will return every row because of the syntax of our SQL query. If there is any empty arrays, we are ridding of them.

  1. //Clean empty arrays so they don’t get every row as result
  2. $keywords = array_diff($keywordsarray(""));

OK, we are ready to set the MySQL query. This is saying if the search variable is ‘null’ or is ‘%’ (MySQL wildcard) do nothing, else the MySQL query is whatever we set it to. Depending on the number of keywords, it will loop until it has competed the query for all words entered. You might have a preference for how your query is ordered, so replace the “ORDER BY column1″ with the column that you would like to sort by. You will have to replace table_name and column[1-4] with the table that you are searching in your database. If you have more columns that you are searching you will have to enter more OR statements. Some tweaking may be required here.

  1. //Set the MySQL query
  2. if ($search == NULL or $search == '%'){
  3. } else {
  4. for ($i=0$i<count($keywords)$i++) {
  5. $query = "SELECT * FROM table_name " .
  6. "WHERE column1 LIKE '%".$keywords[$i]."%'".
  7. " OR column2 LIKE '%".$keywords[$i]."%'" .
  8. " OR column3 LIKE '%".$keywords[$i]."%'" .
  9. " OR column4 LIKE '%".$keywords[$i]."%'" .
  10. " ORDER BY column1";
  11. }

Now we will run the query and store it in a variable named result. If the query fails it will print the MySQL error message. The curly bracket at the end will close our if statement from the previous code snippet.

  1. //Store the results in a variable or die if query fails
  2. $result = mysql_query($query) or die(mysql_error());
  3. }

In the following code we are going to count the number of rows retrieved from the MySQL query.

  1. if ($search == NULL or $search == '%'){
  2. } else {
  3. //Count the rows retrived
  4. $count = mysql_num_rows($result);
  5. }

Now is when we start printing the HTML code. I have included the HTML inside of echo statements to show you how it works. Notice how you MUST provide "\" before any double quotes. Notice how the method of our form is "GET"? That must match how we retrieved the variable earlier. The body onload statement is a little javascript that will automatically place the cursor in the search box to avoid repetitive mouse gestures. Also, we will print the keywords that the user searched for below the search box. Customize the form to your liking.

  1. echo "<html>";
  2. echo "<head>";
  3. echo "<title>Your Title Here</title>";
  4. echo "<link rel=\"stylesheet\" type=\"text/css\" href=\"style.css\" />";
  5. echo "</head>";
  6. echo "<body onLoad=\"self.focus();\">";
  7. echo "<center>";
  8. echo "<br /><form name=\"searchform\" method=\"GET\" action=\"search.php\">";
  9. echo "<input type=\"text\" name=\"search\" size=\"20\" TABINDEX=\"1\" />";
  10. echo " <input type=\"submit\" value=\"Search\" />";
  11. echo "</form>";
  12. //If search variable is null do nothing, else print it.
  13. if ($search == NULL) {
  14. } else {
  15. echo "You searched for <b><FONT COLOR=\"blue\">";
  16. foreach($keywords as $value) {
  17.    print "$value ";
  18. }
  19. echo "</font></b>";
  20. }
  21. echo "<p> </p><br />";
  22. echo "</center>";

Next, we can print some error messages if need be telling the user if there is nothing in the search box or a wildcard, and if the search returned no rows from the database.

  1. //If users doesn't enter anything into search box tell them to.
  2. if ($search == NULL){
  3. echo "<center><b><FONT COLOR=\"red\">Please enter a search parameter to continue.</font></b><br /></center>";
  4. } elseif ($search == '%'){
  5. echo "<center><b><FONT COLOR=\"red\">Please enter a search parameter to continue.</font></b><br /></center>";
  6. //If no results are returned print it
  7. } elseif ($count <= 0){
  8. echo "<center><b><FONT COLOR=\"red\">Your query returned no results from the database.</font></b><br /></center>";
  9. //ELSE print the data in a table
  10. } else {

What we are going to print next, after the else above, is a table header. This is optional, especially if you choose not to display the results in a table.

  1. //Table header
  2. echo "<center><table id=\"search\" bgcolor=\"#AAAAAA\">";
  3. echo "<tr>";
  4. echo "<td><b>COLUMN 1:</b></td>";
  5. echo "<td><b>COLUMN 2:</b></td>";
  6. echo "<td><b>COLUMN 3:</b></td>";
  7. echo "<td><b>COLUMN 4:</b></td>";
  8. echo "<td><b>COLUMN 5:</b></td>";
  9. echo "<td><b>COLUMN 6:</b></td>";
  10. echo "<tr>";
  11. echo "</table></center>";

Now we can start printing the data that was returned by the SQL query in a table. We are including some code to alternate colors in the table for readability. The code following is saying while there are rows in the result, store them in a variable named row and print the data. Also, the end if is from the error messages two code snippets above. The names column[1-6] should match the names of the columns in your MySQL database that you are wishing to display in the HTML table. If you are wishing to not display your results in a HTML table, feel free to change the HTML to something more fitting.

  1. //Colors for alternation of row color on results table
  2. $color1 = "#d5d5d5";
  3. $color2 = "#e5e5e5";
  4. //While there are rows, print it.
  5. while($row = mysql_fetch_array($result))
  6. {
  7. //Row color alternates for each row
  8. $row_color = ($row_count % 2) ? $color1 : $color2;
  9. //table background color = row_color variable
  10. echo "<center><table bgcolor=".$row_color.">";
  11. echo "<tr>";
  12. echo "<td>".$row['column1']."</td>";
  13. echo "<td>".$row['column2']."</td>";
  14. echo "<td>".$row['column3']."</td>";
  15. echo "<td>".$row['colomn4']."</td>";
  16. echo "<td>".$row['column5']."</td>";
  17. echo "<td >".$row['column6']."</td>";
  18. echo "</tr>";
  19. echo "</table></center>";
  20. $row_count++;
  21. //end while
  22. }
  23. //end if
  24. }

The last thing we are going to do is clear the memory of the result and close the body and html tags.

  1. echo "</body>";
  2. echo "</html>";
  3. if ($search == NULL or $search == '%') {
  4. } else {
  5. //clear memory
  6. }

There are better ways to handle some of the operations in the PHP code in this example. The main point here was to split it up so it is more easily understandable. I hope this was a good exercise for you. Please be aware that the input IS NOT PROPERLY SANITIZED, so if you set the user in your config.php file as a user that can write to the database, there is a probability that SQL injection could occur.

Download the config.php and search.php files here.