Simple Search Engine with PHP and MySQL FILED UNDER FEATURED, TUTORIALS, WEB DEVELOPMENT. - Notes

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:

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.

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.

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

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.

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:

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.

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.

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.

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.

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

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.

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.

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.

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.

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

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.