Ajax Search Box in PHP and MySQL

In this tutorial, we are going to Build Ajax Search Box using PHP as core language. Ajax Search booms the usage of web projects such as one Google uses. Searching through Ajax and also maintaining less load on Server is a big challenge.

I have already posted about Ajax Search Box in Node, we are replacing Node back-end script with PHP. Format of data coming from Server is JSON itself. We will call the Search web service using GET request.

Database Design:

Open up your PHPMyadmin and create database of any name. Inside that database execute following query.

CREATETABLE'user_name'(
user_id INT(20)PRIMARYKEY,
first_name TEXT);

Put some dummy data in it using INSERT command.

Designing Front-end :

I am using Twitter typeahead JavaScript library to perform Ajax call to PHP file. It’s neat and clean library specially designed for this kind of purpose. Just add the path to HEAD section and you are good to go.

Retrieving data from MySQL and returning as JSON:

Here goes the main task. In PHP file first, we have to fetch the GET value which stored the search parameter. Then we have to form a connection to MySQL database and fire query to get a relevant result.

Once the result is been fetch, we have to pack it in JSON format and return back to front-end to display it as result.

even i say thousand of thanks,it will not sufficient at all.. i was searching this for a long time! but not a single 1 was running correctly! Finally Your work saves me from getting supple in the exam!!
I have no words to thank you! wish U a vry Hppy life.. M grtfl To U.

Hi! First of all thank you very much for taking your time and effort sharing your knowledge with ours.
I would like to learn how to build a blog using php mad MySQL
Have you done a tutori like this one before or do you have any source code please.
Kind regards

am working on my music lyrics website php front and mysql back end, i wanna use one search box to extract tracks,genres,artists,lyrics,group etc hope u understand me well,any help in php script i welcome it.thanks

To hide that, use click event listener in jQuery and use preventdefault() to actually redirect it, then fetch href value using .attr and redirect the page to that value which is address clicked by user. Hope that helps.

This is an excellent tutorial/script. I have everything working and I am able to search my desired query. Forgive my ignorance, but I’m not sure how to utilize the data once I have selected the desired record. Can you use the input field as part of a form to enter the selected record into another table? Hope that makes sense. Thanks again.

please i need a quick response, i need to search through multiple tables in my database using just one command line. i tried

$perform_query=”SELECT * FROM apple_tv,cam_coder,category,core_network,document_cam,i_pad,macbook,mac_mini,monitors,projector,still_cam,windows_devices WHERE bar_code LIKE ‘%$search%’OR username LIKE ‘%$search%’ OR system_name LIKE ‘%$search%’OR serial_number LIKE ‘%$search%’ ORDER BY grade DESC “;

but i get this error “where clause is ambiguous”. is there any other way i can search through all the tables in my database? URGENT

A few people have asked how to add multiple form fields and search to separate databases.

You can create 2 search files checking different databases/tables etc.
so search.php and search2.php

Then put this into the head of the page you have your form on replacing the “name of person” with what ever the name of 1 of your form field is and do the same for “city” to what ever other form field you want to search for.

Hey Stoogle, Thanks for that extra information, it was what i needed to get this working!
I have one extra tip for people that want to use thit also. You don’t need to use 2 remote search scripts. If you add an additional get variable to it you can put all your search queries in 1 php file.
Fi:
1st part: remote:’search.php?do=nameofperson&key=%QUERY’,
2nd part: remote:’search.php?do=city&key=%QUERY’,

Nice code sir. 🙂 I need a little help. I need to get multiple database values . Say for example i select a certain item in the live search and assuming it has a corresponding id in database, so whenever i select the certain name its it will be save in a hidden input type. How can i achieve that ? THanks 🙂

Hi Sir, I don’t get it. For example , if I try to retrieve ID and name from my db using a query, but only want to show the name in the result and hide the ID to retrieve the correspondig once you have selected a name in the live search. How can I do it?

Fantastic and simple bit of code – thank you, however I have the query set to show the search of column A in a table, but I want to also show the related data in column B in a separate box once chosen from the drop down list.

Each time I try to add this additional code the whole scripts stops working, so I’m not sure what code I should be adding and where!

I have a limited knowledge of php & html so an example would be really helpful!

If you want to achieve this i think you need to call different PHP file which returns the values from DB related to option selected. So use jQuery to fetch the select box value and call php file in that run SELECT query to retrieve values from table and populate them in another element using jQuery itself.

1: You can do select to name field as well in SQL query along with ID. For each row there is ID and Name you can then associate it.
2: You can use OR , AND query to search for more then coloumns.
3: I am not sure about arabic. Apology.

sorry i repeated one of the questions, I would like to know about the rest of the questions.
1. I am using it in a form for id and name. what I would like to do is If I write the id Then it automatically adds the name in the next field.

2 I would like to know how to make it multi lingual support. How to show the arabic data in the search box currently it is showing ??????????

Thanks a lot for your code. May you have a wonderful Life filled with lots of fun.

1: You need to either call SQL query again with reference to ID or store the Data returned by search in some variable and use it in Form.
2: Multilangual is something i need to figure out yet. As a suggestion please have a look at MySQL language support. You may get some idea.

Hi Rowel,
This error comes up when SELECT query failed ! Are you sure you are putting right name of database along with i guess you changed the file name too, make sure you reflect the changes where it is required.

Have query if you can help out with:
There is a attribute in my form that uses typeahead and populate values from the database. However, the attribute should have a feature where it can be added by the user as many times as wanted i.e. multiple rows. In the API I have provided an option using JQUERY where new rows can be added in the table pertaining to this particular field. The query goes like this:

I have a website that displays bus route and bus stop names. I need to make these routes and bus stop names as link to make them click-able. For example, I have route no 56. If anyone click on 56 then it will open a other second page. Here, I have to pass on this variable (56) to other page. At present, I pass this variable thorough URL (example second page example.com/routedisplay.php?routeno=56) and get this variable on second page by GET command.

But I want to remove question mark and make it clean URL (such as example.com/route/56). How to get this? I tried to create route folder and put a index.php file there to process but nothing works.

Hi,
You need to develop REST API’s to get URL like the way you wanted. So if say get routes for bus 465 your URL will be like example.com/routes/465. If you need something like this and you want only specific in PHP please look at Slim framework for PHP.

This tutorial is great you have save me with my time but need little a help here i’m using this to search in inventory management system so, i’m using it for search items for each item there is db fields called project name, category, sub category so i need to filter the search results according to those columns in the database, i’m selecting those fields earlier page and passing to current page by post method.
please help me on this it’s very important to me

I want to ad the returned value from the query to an anchor tag which will send it to another page using a GET request, but when i concatenate the value from the json it won’t work, any solution in mind? Thanks in advance 🙂

thanks for this script is very helpfull. I have one question, is there a way to add an if or validation.
If the query found results then show the dropdown like is working right now, and if not show a “not found” text?

Also many tnx for this example.
The base is working but i struggle with the return data.
In my situation i want to get back a list of data (that works) but also per data an Id which should not be visible, but
which i can use as post data in the submit.
I probably have to change the json array but i then i don’t know how to seperate the data in the post form and use the id as hidden id.
To clearify my example: I have a table with strings (can be duplicate) and unique number id’s.
I want the list to contain the strings and i want to submit with just the id.
Additional i’d like to submit immediatly on selecting.

I used it to search company names and looking for small functionality if some select the company name , then it should all details from database, like address, name, contact person means whatever details is associated with that company from database.

Since you need dynamic data on each click i will suggest you to develop a API which accept company name as parameter and returns you all response from Database. Then you can display that info in UI wherever you wish.

where on earth do i get typeahead.min.js?? i have been searching all over the web and can not find this or anything compatible with this code, i get search.php to work, but the html and typeahead will not work no matter what i do.

i have done everything correctly but the only thing that’s not happeniing is that the live search is not working.When i run the php page the jason_encode prints all the name there that means that it is taking data from the databse but still live search is not working.

my last step is not working ie(Once the result is been fetch, we have to pack it in JSON format and return back to front-end to display it as result.)
please help me i need to get over this problem soon.

Great tutorial. One question is there some limitations with bigger database. I have got 5000 job titles in the DB but up to now I got it just working with one keyword (cust -> Customer service). Even though I have many Management Jobs “man” does not return anything ??

Hi Shahid,
Thanks very much for this, everything works fine.
I would like to output the results as a select box,
echo json_encode($array); at the end of search.php
outputs a text list.
Can you suggest how I can output this instead as an html select box.

Hello Shahid,
I was hoping you could help me with a small bug in my code. I downloaded your code and simply edited search.php to fit the specifics of my database. My issue is that I recieve no response when I type into the search box. I have already verified that the issue is not with my connection to the database because when I go to search.php, all my rows are outputted. The following is what appears in my logs if it becomes useful:

Sorry I will try and Clarify, above the input I have added a select field. Inside of the select field are options with values that correspond to column names in the database. In your example the column name in which you are searching is already known. I am curious on how to set the column name from the select field and then use the input to match words from that column. I have tried something like the $key variable

$term = $GET[‘term’] /*I was not sure how to pass this variable like you did with the key variable*/

$query=mysql_query(“select * from table_name where %$term% LIKE ‘%{$key}%'”);

But this did not work.

I hope that helps to clarify my question.
Again I appreciate any help/insight you can give.

Basically why you are not getting the term is because it’s Ajax search and we are listening and sending key inputs every single time user is typing.

You have two options :

1 : Get rid of Ajax feature and use the checkbox selection and search input as search criteria, call another php file and load result into next page.

2 : Keep the ajax, then you need to listen for two events.

2.1 : Listen for checkbox selection, upon selection call php file via AJAX and check whether column name exists or not in DB ?
2.2 : Upon typing event send checkbox value along with typed input into two separate keys and do the query using AND operator.

could you help me ?
My issue is that I recieve no response when I type into the search box. I have already verified that the issue is not with my connection to the database because when I go to search.php, all my rows are outputted

Hi man great work but could you please help me with one thing ? I anticipate that everything works as it is supposed to do. I would like the results coming from the ajax query to be displayed inside a and not just under the one. Would it be possible to use the input box only to type data for the search and display the results in another element (in this case a ).

hey shahid,thanks for help,i want a favour from you,can you just show me the way to upload an image in folder and path in database using javascript and ajax,without using any html(Form) tag but you only allow to use div only,actually i created a project,and i really don`t understand how to make,it done.. pls dude,your help really appreciated pls reply soon…

Hi,
I want to show expanded info about search result.
I created this array:
$array[] = ” . $row[‘name’] . ‘ – ‘ . $row[‘surname’] . ”;
but when I select row from result in search box placed all html tags.
How to clear selected in search box?

hi thanks a lot for your easier coding code you gave us, I downloaded the source code i followed recommendation in readme file , but it doesn’t work like I would like ….
will you help me please, I would make it with MVC architecture

Hi shahid,
Hope your doing great , I have a question regarding node oracle . I entered details in my sql developer database and it can display values in node console but not in my html webpage . I tried to figure out but could not find solution can u help me out at your convinience.
Thanks ,

Hello,
I have used your tutorial to successfully creat a search box that works as it is supposed to.

I now want to get the value of the selected name and put it in a hidden form field so that I can use it on another php page using GET. I have looked at many examples on the net and am very frustrated by the various versions of Typehead and Bootstrap. I am using your typeahead.min.js and bootstrap.min.js 3.3.6.

i have downloaded ajax-search-box-php-mysql. But it is not working in the localhost..I have created the database and added table and given values to its attribute…I have changed the db name and table name in search.php..But nothing is showing when i type in the box..please help me

Now it’s working sir..please I need one more help sir..when I type something the corresponding name is coming and I need “when I click on that name all the balance details in the database corresponding to that name is to be shown on my website”..please help.me sir

I have completed the setups using your scripts and some of the follow up suggestion in the comments section but I still cannot get the results to pop up in the search box, please see my scripts below and help if possible.

FYI: I uploaded ONLY the file typeahead.min.js to my root directory
I know the database connection works as when I run the link below I get a list of names from the database (although curiously the first record is missing)http://www.aceitnow.com/projects/php/default/ajax_search.php
===========================================================================
auto_search.html
=========================================================================

please I need one more help sir..when I type something the corresponding name is coming and I need “when I click on that name all the balance details in the database corresponding to that name is to be shown on my website”..please help.me sir

please anyone help me…I have two textbox(typeahead and typeahead1) in my website and implemented ajax search box successfully in this two textbox by creating two search file(search.php and search1.php).what i needed is Before typing in the second textbox, i need to pass the value of first textbox(typeahead) into search1.php file and need to store in a variable there..

I have question that was asked by 2 people however no one responded:
Could you please help on below repeat question:
I have everything working and I am able to search my desired query. But I’m not sure how to utilize the data once I have selected the desired record. Can you use the selected value in the search box as part of a form text box and post it to get different php page?

hi.. its awesome code.. See, currently your code is just selecting search entry item, could you pls provide me a code where, After selecting a value from the search, it should display the result in ‘text field’ (provided just below search box).
If i want to select PHP, JAVA, .NET from the lsit one after the other, Selected items should display in

hey, i want to create a advance search filter box ,like serach by city ,role and category,and can i use multiple page for multiple categories,like i am developing a job portal project,i want to show jobs by category and i will use icons for each category,plese ghelp me out to create the advance search filter box..

Helleo there ..!
how should i get an image by searching the name of an image
$key=$_GET[‘key’];
$array = array();
$con=mysqli_connect(“localhost”,”root”,””,”watches”);
$db=mysqli_select_db($con,”watches”);
$query=mysqli_query($con,”select images from data where name LIKE ‘%{$key}%'”);
while($row=mysqli_fetch_assoc($query))
{
$array[] = $row[‘images’];

Thanks for the post, it was really helpful. One small tweak I may suggest is including class=”typeahead” in your input box. I found that my code didn’t work until I associated the input field to the class typeahead. I believe your source code has it, but just in case people are copying and pasting from your page (like I was).

I have a table and am fetching the user details from MySQL db. I have created a search box and button without using ajax. it works well enough . I am displaying the search results in a separate page and then gave a link to redirect back.
I want to change my code to ajax search box and display the search result in the same page as that of table, I need to hide the other users info and only display search results. Can you suggest how to go about it.
Do I need to write separate html code for doing so? How do I hide the users who are not on search criteria?

Hi Shahid,
I have tried your code and it worked so fine. Thank you for that. but now i want to use this search setting for another input text on the same page. I have tried but everytime it calls the same function of first input type instead of second one. Can you explain any solution for this problem?

I have 2 fields in a mysql database. One field is an Article name and the other field is the link to the article. Currently the Article name is coming up in the search results. When I click on the Article name in the search results, I want it to take me to the link. How do I code this?

I downloaded and tried on my site. Did not work and did not make any changes. when I tried the search I got no results. I changed the query data and have data populating in the array. when going directly to the search.php there are results but search on index.html does nothing. any ideas?

Hi Shahid, although your code works fine when applied standalone with a slight change (adding class to a input tag since you didnt use $(‘input[name=typeahead]’)), when i try to add to my website i get this error in a console:

Could i use this as a custom field on a product page for woocommerce? using the database list to add a ‘school’ for example and then also have a search function to search for this data/product on front end?