One of the most important advantages of creating a database driven sites is the ability to perform search queries on the database. Could you imagine searching WeberDev.com if it was written using plain HTML? Not that it couldn't be done, just we would have to rebuild an index of all the pages every time someone adds an example, article and so on.
Now, performing searches on a database driven site is a totally different story (and thankfully much easier).
In order to understand and work a bit with searches we will need a small MySQL driven site. I will use the structure we built at my "Beginners guide to PHP/MySQL - Creating a simple guest book" article. Actually, we will write a search page for the guest book described in the above tutorial, so go on and take a brief look at the PHP code for the guest book, I'm waiting.
Notice that we have a file named links.x, which holds the links to the guest book pages. We will modify it slightly to include a link to the search page (the third <li> statement).
Ok, done with that.
Searching the database
To tell the truth, we don�t actually search the database, but rather select records from it that correspond to a string we choose. Lets assume we want to search all the records where the users' name matches the search string:
This html is rather simple. Just a small form that sends a search string variable to srch.php3.
Some explanations. This scripts performs the following tasks:
Clearing all the mumbo jumbo, the actual code that we need to work on is:
Yes, this line does all the work. We will play with it a bit later.
Ok, this query gets all the records where the Name field is equal to the string search. Please note that an exact match is needed.
Lets assume we want to search for a partial string match (i.e. where the search string appears in the filed but as part of the string and not an exact match). We will have to modify the script as follows:
The LIKE comparison argument will return '1' if the Name field has a partial value of $search. Note that I modified $search and added "%" on both ends. This allows to search for the search to ignore the leading characters and the characters following the search string.
Ok, now lets assume we want to search all the field of the table and not only the Name field. In order to do that we need to choose the records with Name LIKE $srch or Last LIKE $srch etc. The translation to MySQL query is:
The complete srch.php3 script top to bottom should look like: