Keyword search using AJAX in MySQL databaseWe can use Ajax to send keywords as we type to the database to get the matching records. The advantage of this is we are not waiting for the full keyword to be typed and then submit to get the matching records. Here as we type the text in a text box the data is posted to our PHP script using Ajax and the result is displayed below the text box.
By watching the result returned by the database we can modify our search keyword.
This is a simple one field matching search only. We have not used other fields or dynamic keyword combinations to get the result from the table.
Our search query uses SQL Like statement to get the matching records from the table.
You can learn the basics of using GET method in Ajax to post form data here This script uses two files, one is the htm file which uses Ajax to send the data to PHP script. Here is the code.
Sorce Code : ajax-search-demo.php
Our PHP script connects to database and uses Like query to get the data, the same data is returned to the htm script by using Ajax.
Sorce Code : ajax-search-demock.php
Advance keyword search using Ajax PHP and MySQLTo understand this script you must know how to use Ajax, Json, Paging of records and SQL. In addition to this you must read how we break a string and prepare keyword linked query.
Using the above concept we can develop a script to search database. To prepare the table we have used the title tags of php section of this site. We kept all the text inside title tag of PHP Section tutorials and apply search to this.
Suggesting matching records by using a datalistWhile we type the words in the textbox, we can post them to backend search script and get the matching records which can be used as options of the datalist. You can read more on basic of datalist and how to add options to datalist by using records from MySQL here.
Searching with multiple keywords.It is likely that users will search for multiple keywords where we have to return any matching word. For example if we search for the sentence plus2net demo then our script should find out records having word plus2net or word demo in the title field. So the query string should be like this.
So we need to extend our above query depending on the number of words used for searching.
Multiple keywordsUsers are likely to search multiple keywords. Say for example we search for the words PHP MySQL Ajax. Now the best result is where all three words present in the page. So we will search for pages where all keywords present and place them ahead of other results. After placing these results then we will search for records where any one of the keyword is present. Now we will join these two results and return to visitors.
Here we have not kept any algorithm to rank search results. We will match only keywords in title or description column. That can be developed … That is the technique used by search engines and success depends on indexing and developing complex search algorithm to give better user experience.
Paging of RecordsWe should not show all the records (results ) in a single page. It is better we show limited records pages ( say 10 records ) and allow visitors to navigate forward or backward to other pages by using buttons.
JsonAlong with records we will also return several other data to the users so we will use Json for returning data to users.
Database tableFor this tutorial we indexed our PHP section and kept it in a table. How to read file and index it ( spider ) is not a part of this tutorial. In your downloaded file some sample data is kept for you to test the script. Try searching for Ajax PHP MySQL script etc.
Ajax & PHP scripts Creating XMLHttp object in different browsers Sample Code: Get Method of Ajax form submission Server Clock:: Display real-time date and time of server Dependant drop down list box using Ajax & PHP Email validation using Ajax in a form Getting customer details by entering customer id using Ajax Progress Bar using Ajax Progress Bar using MySQL PHP & Ajax Displaying Message at client side using Ajax & PHP Web page HTML form validation using Ajax and PHP Form validation with onBlur event using Ajax and PHP
Json support in PHP json_encode to generate Json string from PHP Array data Json Data formatting to return database records to main script Searching MySql database as we type using Ajax Displaying all records based on selection of a drop down list box