Monday, October 24, 2011

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:

 
  Live Search!
  
 
 
  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.



16 comments:

  1. Hi,

    Is this 100% working?

    I will give it a try...

    ReplyDelete
  2. Hi,

    Do you have a complete file for this, including the database? So that I can try if it's working and easy for me to change values in the codes.

    I will just import your sample database to my xampp and try to run it if it's working fine.

    Much preferable in .rar file.

    Hoping for you response, A.S.A.P.

    Thanks.

    ReplyDelete
    Replies
    1. I did upload the files, link is available near the end of the post, but it is in .zip format.

      Delete
    2. Thanks... It will help me for sure....

      Delete
    3. Would you please send me the link to download the zip file for the same thanks...

      Delete
    4. It is in the post, near the bottom.. but here you go as well.. https://docs.google.com/open?id=0Bz170qNApm2AOGQzYzU0NzQtZTc1YS00ODVkLThhYzYtYWY4MWU5ODk1NzE4

      Delete
  3. Hi again,

    Do you know what algorithm did you used for this kind of search? Or do this real-time search of yours have algorithm name?

    Is this reliable if the items in the database is thousands?

    Sorry for my questions, i really need to answer those. Please help.

    Thank you so much.

    ReplyDelete
    Replies
    1. This algorithm is a simple SQL "Like" function, nothing fancy. I'm not sure about the reliability if the database is large.

      Delete
    2. Thank you for your response. I will give it a try.

      Delete
  4. Thank you for posting this! This is coming in handy to help me with a searching issue I was having.

    ReplyDelete
  5. I was looking for this.
    Thankyou

    ReplyDelete
  6. Can you show us some screenshots of this example to have a better understanding. Please!!! I m looking something like this for a long time. please

    ReplyDelete
    Replies
    1. The screenshot is just a simple search query box with results. I did upload a screenie to google docs: https://docs.google.com/open?id=0Bz170qNApm2AaWRrak5WNnZ1cFE

      I added a fourth keyword called "all" to every single entry, which is why when I type "all" into the box it populates everything on the list (with scroll bars).

      Delete
  7. here's a good tutorial that also uses the combination of PHP and AJAX, you can seach and the results are quick it also have limiters. http://www.upgradedtutorials.info/tutorials/fast-search-and-sorting-data-in-mysql-using-php-and-ajax/

    ReplyDelete
  8. There's an excellent live search tutorial at https://kodesmart.com/kode/live-search-with-jquery-php-and-mysql

    ReplyDelete