Realtime Search with PHP, MYSQL, and Prototype

July 23rd, 2008 by Jeremy

I wanted to have a live search (much like Apple Spotlight) for a project using php/mysql, so I thought I’d try my hand at figuring it out on my own. If you have any suggestions to make this better, I’m all ears!

In this example, I have a mysql database table with the names of all 43 U.S Presidents, and the number president that they were. I want to search the database for that president and have the search result link to that president’s page on wikipedia.
You can view the demo here. You can download the sample files here.

First, you will need the Prototype javascript framework. We will use this for making the search realtime. Alright, now that you’ve got Prototype, lets get started!

Let’s start by creating an html file called search.html

<form id="searchform" method="post" onsubmit="return false;">
<input type="textbox" id="searchbox" onkeyup="sendRequest()" name="name" autocomplete="off" />

Since we want the search to happen in realtime, we don’t want the user to be able to submit the form, so we added onsubmit=”return false” to prevent this from happening.
We want the results to update as we are typing, so for this we add onkeyup=”sendRequest()”, which will call our javascript function that will send the results to the server every time you type in the text box.
autocomplete=”off” will keep the users browser from auto-completing the form. autocomplete is not valid, so you will need to find an alternative method to solve this if that concerns you (which it should, its just beyond the scope of this entry).

<div id="show_results"></div>

This is the div where our results will appear. You can place this anywhere on your page that you want the results to show up.

<script type="text/javascript" src="prototype.js"></script>
function sendRequest() {
new Ajax.Updater('show_results', 'search.php', {
method: 'post', parameters: $('searchform').serialize()

This is our javascript function that will send the data to our php file that will perform the query and return our results.

Now, create a php file called search.php

$name = $_POST['name'];
if(empty($name)) {
echo “”; //This is what will display when there is no test in the text box
} else {
echo “<div id=’results’>”;
$sql = “SELECT id, name FROM live_search WHERE name LIKE ‘%$name%’ OR id LIKE ‘$name%’ “;
$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result)) {
$id = $row['id'];
$name = $row['name'];
echo “<a href=’$name’ target=’_blank’>$id $name</a> <br />”;
echo “</div>”;

Your query can be whatever you need it to be, you can research this according to your needs. This particular query will pull all results that have any of the characters in it that the user has type into the search field. For example, if you start by typing the letter J, all of the presidents that have the letter J anywhere in their name will be displayed. I can also type the number of the president I am looking for, and it will show me those results as well.


Posted in Web Development

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.