PHP Real-Time Search Using MySQL and AJAX

Recently I decided that instead of posting all of my links directly on my homepage (It was becoming a long list, and annoying to have to edit the HTML every time to add a link) I would create a php based search engine, that used AJAX for real-time results.

After using Google for an extended period of time, finally found my first link: http://woork.blogspot.com/2007/11/simple-search-engine-in-ajax-and-php.html  (I DON'T recommend this unless you know what you are doing, it is full of errors/missing syntax/db connections).  After following this guide (and fixing mistakes) I realized this did not work as I had hoped.  When I searched for my keyword, it did not appear to matter what keyword I searched it pulled all the results up.  Since I am not a php expert, went back to Google and found the following article: http://blogs.nightglass.com/jeremy/2008/07/23/realtime-search-with-php-mysql-and-prototype/

The second article was a much better guide on how to set up real time search, now I just had to edit it to my needs... here is what I wanted.

A search engine that would search my MySQL database of internal website links based on 3 keywords that I specify upon creation of the link.  So I created a MySQL database, lets take a look at the layout.  (Please note I am not even close to proficient with MySQL, which is why I'm using phpmysql).

MySQL Database Setup

The first field is the Index, a simple auto-increment field just to make my links have a unique number.  The next field is "link_text" which is just the html link.  Then I have my three tag fields, the next is the current timestamp, if for example I wanted to show the latest links that were added.

Form.html - The search box setup

The "Form.html" sets up the basic search box, and calls the javascript file, lets take a look:

 
  
  
 
 
  Keyword Search:
  


  



  
 

Fairly simple, ignore the first half, that just is styling for the search box.  The part we need starts at "from id="searchform"".  That creates the search box.  Notice "onkeyup" this updates the results every time a key is let up.  The "name=searchq" is also tied to our next php script, so remember that name.

Prototype.js is the javascript that creates the search results, which can be found here.

Search.php - Where the magic happens

Lets take a look at our "search.php" document, which is the main part we need to know.

 


"; //this div is used to contain the results. Mostly used for styling.
   
 //This query searches the name field for whatever the input is.
 $sql = "SELECT link_text FROM Links WHERE link_tag_1 LIKE '%$searchq%' OR link_tag_2 LIKE '%$searchq%' OR link_tag_3 LIKE '%$searchq%' ";
   
 $result = mysql_query($sql);
 while($row = mysql_fetch_assoc($result)) {
  $id = $row['link_text'];
  echo "$id";
                echo "br"; 
  }   
  echo "div";
 }
?>

*Note: I have not yet cleaned the input, so make sure you do that to avoid MySQL injection. Also, because blogger hates me, had to remove the formatting from the last div tag, and there is some odd formatting at the very top.

The first lines are standard, you are connecting to your MySQL server, connecting to your link database and table.

The interesting part is when you grab your search query from form.html "$searchq = $_POST['searchq'];". If it is empty, you will display nothing. Else, you will start getting search results. Next is your sql command. You are selecting the link_text that matches any of your tags for that link. The "%" signs specify any characters before or after the search term, so you don't have to type out the entire tag to get a match, you can type one letter and it will match any of the letters in all of the search terms, and will narrow it down as you type.

Next, we set $id to equal row "link_text" which is just the fully formatted html link, so this displays in your popup box. If you don't like the popup box, go ahead and delete or reformat it in Form.html, I did not like the look so changed it to a simple blue link that appears below search results.

EDIT: The files how now been uploaded to google docs, link to download here. Let me know if you have any problems.

Okay, you should be ready to go, happy searching. Next time I hope to post about adding/changing entries in your mysql database from a web page.