Creating A Wikipedia Style Search
Creating an intelligent search (like Wikipedia’s exact match search), at first glance, looks really difficult. However with a little bit of thought & a break down of it’s features you can easily build one using PHP & MySQLi. I am not going to support the old MySQL connections here because I think everyone should try & use MySQLi now. It’s nicer, cleaner & much easier to use.
I am not going to do any styling or anything, just the basic bare bones code needed to make the search work. Why? Well you are only going to style it to match whatever project you’re using it for so why clutter the code.
Objective Break Down
Let’s break down what we actually need from this search:
- Database connectivity
- Direct to result on exact match
- Direct to search results on multiple matches
- No results message
Those are the basic things we are looking for from this search. This tutorial is all about the PHP code needed to make this work, but for giggles let’s look at a simple form for the front end.
Search Form
If you want to match the form to my code, so that you don’t have to make any alterations, here is the HTML code needed:
|
1 2 3 4 |
<form name="search" action="path/to/php/script" method="get"> <input type="text" name="q" id="search_input" value="Type To Search" /> <input type="submit" value="Search" id="submit" name="submit" /> </form> |
Again it’s no frills, but it is set up to match the PHP script. Just remember to change the path in the action field.
The Search Script
Ok, on we go to the PHP that will make our search work:
|
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 |
//Check for our query if(isset($_GET['q']) && !empty($_GET['q'])) { //Store the query in a variable, url decode it, and wrap it in % for wildcarding. $search = '%'.urldecode($_GET['q']).'%'; //Open a mysqli connection $mysql = new mysqli('host', 'user', 'pass', 'database'); //Prepare the query for execution using a prepare statement. Returns the statement object. $stmt = $mysql->prepare("SELECT name, occupation FROM ajaxdelete WHERE name LIKE ?"); //Bind search parameter $stmt->bind_param('s', $search); //Execute the prepared statement $stmt->execute(); //Store the result or num_rows will NOT work $stmt->store_result(); //Bind the result variables $stmt->bind_result($name, $occupation); //If number of rows is greater than 1 if($stmt->num_rows() > 1) { //load search page //In this example we will just output the results while($stmt->fetch()) { echo "<strong>Name:</strong> {$name} ({$occupation})<br/>"; } } elseif($stmt->num_rows() == 1) { //If num rows is equal to 1 //Redirect to page info... Whatever you want to do. Simple redirect would be... //header('location: http://example.com/'); } else { echo 'Ooops! There were no results for that query'; } $stmt->close(); //Free statement memory $mysql->close(); //Free mysqli memory } |
This may take some explaining so please bare with me. First of all we check to see if a query has been sent, in this case our variable is sent via $_GET so the search can be bookmarked. Next we use urldecode() to change any URL encoded characters back to normal (spaces, hyphens etc) & at the same time surround our query with percentage signs (%). These are wildcard operators in MySQL, and tell it to look for the word in any occurrence throughout other words & the word itself.
Next we open a MySQLi connection. We set up our MySQL query in a prepare statement. My table was a simple example I use when playing around with MySQL, it contains a list of actors, actresses, singers, and fictional characters & their occupations. You will, of course, need to customize the query for your purposes. The question mark (?) is a placeholder for bind_param(), you will need to place one at every point there will be user input placed into the query.
Next we move on to bind_param(). You must provide the type of data in each variable & the same amount of variables as question marks (?) in the prepared query or you will trigger an error. In my case I have one variable & it is a string. If you had multiples (say a string and an integer) it would look like this:
|
1 |
$stmt->bind_param('si', $string, $integer); |
The types are ‘s’ for string, ‘d’ for double (float), ‘b’ for BLOB, and ‘i’ for integer. We then execute the prepared query (statement) & store the results. If you do not store the results num_rows() will always return 0, in my experience anyway.
Next we bind our result variables. In the same way that binding parameters required the same amount of variables as question marks, this requires the same amount of variables as selected fields. In this case that is 2 (name & occupation).
Now we head onto our conditional statement that basically makes the search intelligent. If we have more than 1 row, we need to give the user a choice. Normally we would show a search results page of some sort, but for this tutorial I have just outputted the results we received from the database. Some may consider using echo here a crime, so here is a printf() version:
|
1 2 3 |
while($stmt->fetch()) { printf('<strong>Name:</strong> %s (%s)<br/>', $name, $occupation); } |
If we only get 1 result from our database it means we have an exact match. What you do here is entirely dependent upon your website/application, you could redirect to a page, using something like the header example, or something else entirely. Just remember if you do redirect using the header function you cannot output anything to the browser or it will fail with a ‘headers already sent’ error.
Finally if we get 0 results we show some sort of no results page, in this case I have just echoed a simple message. Right at the end we trigger close on both the statement object & the mysqli object to free up the memory used.
Well that’s it. Unfortunately I can’t provide an example thanks to the strain on my already taxed MySQL database, but it really does work. If you have any questions, comments, or improvements (suggestions) let me know in the comments.
3 Comments
Chicago Limousine Service
Your right the new MySQLi is much better than the old one. I also suggest the new MySQLi. Great code.
Tony
This looks great, good post and well commented. I have a question, how would you go about integrating this with WordPress? Want it to list page titles with exact match as the user types.
Paul Robinson
Hi Tony,
Wow! Honestly I don’t know. The only filter I know that effects the search in WordPress is
the_search_queryorget_search_query.It sounds like what you are looking for though is a autocomplete. Rather than this example. You’d have to use an autocomplete such as the one included with jQuery UI and WordPress’s AJAX actions to populate the results.
I guess that’s not much help, but I’ve never really thought about an autocomplete/autosuggest search within WordPress before.