Instructobit
With the ever increasing amount of data on the internet, it important for users to still be able to find the information they need. Many modern websites have search functions that allow their users to search through the site's content for something specific.
Basic search functions allow users to enter a query which is compared against a set of titles, descriptions, tags or other content. Content that matches any of the terms in the query are fetched and ranked in order of relevance to the query.
If you have you website content stored on a MySQL database, one of the built-in functions you can make use of- to make the job of creating search functionality on your website much easier- is the
statement. This statement makes use of
indexes to efficiently search through your database for a search term.
Creating FULLTEXT indexes in your database
Before you can make use of the MATCH AGAINST statement, you will need to add at least one FULLTEXT index into the table you wish to search. If you use the MATCH AGAINST statement on a column that is not a FULLTEXT index, then the statement will result in an error.
FULLTEXT indexes can be added to columns on table creation as well as to existing columns after a table has been created, so you can create search functionality for any tables that you already have on your website without making any drastic changes.
Adding a FULLTEXT index on table creation:
Within your CREATE TABLE statement, you can add a new FULLTEXT index using:
FULLTEXT ( column, column ... )
Using this statement you can also name your index and specify whether it is a key or an index, but we'll be keeping it simple for this tutorial.
As an example, let's create a new table that will store posts on a blogging site. We'll have 4 columns: postID (primary key), title, description and URL (location on your website for the post), the title and description columns will serve as our FULLTEXT index.
CREATE TABLE post(
postID INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR( 150 ),
description VARCHAR( 250 ),
url VARCHAR( 100 ),
FULLTEXT ( title, description )
);
This table will now allow you to use the MATCH AGAINST statement for the title and description columns.
Adding a FULLTEXT index after table creation using ALTER TABLE:
If you have an existing table such as the one we created in the above example (without existing FULLTEXT indexes) then you can add a FULLTEXT index using an ALTER TABLE statement like so:
ALTER TABLE tableName ADD FULLTEXT( column, column ... );
So if you had a table with the existing columns: postID, title, description and content, then you can add a FULLTEXT index to the title and description columns like so:
ALTER TABLE post ADD FULLTEXT( title, description );
Searching your MySQL database for a search query using MATCH AGAINST
Once you have set up your FULLTEXT indexes within your table, you can make use of the MATCH AGAINST statement to search your database for a particular term.
The basic format of a MATCH AGAINST statement looks like this:
SELECT columns FROM table WHERE MATCH( columns ) AGAINST( "search term" );
All columns within a table that have been included in your FULLTEXT index should be included in the MATCH statement, if you omit one or more of the columns included in the index, then the statement will not work.
By default the MATCH AGAINST statement uses
natural language mode
which generally returns more relevant results, but can be problematic with smaller data-sets as it omits words that occur in 50% or more of the rows, stop words such as "the" or "is", and words that are too short (less than 4 characters).
An alternative to natural language mode is
boolean mode
but this does not sort rows by relevance which can be a problem for search functionality.
For this scenario we'll make use of natural language mode. Let's say we had the following populated table called "post":
postID title description url
1 Greetings world fun times example.com/post1
2 Seventh spark exciting example.com/post2
3 Hello world Blue sky example.com/post3
4 Lonely ice Winter cold example.com/post4
5 Rise into The tower example.com/post5
The title and description columns have been declared as our FULLTEXT index. If we wanted to search for the term "greetings world" from this table, then we could use the following MATCH AGAINST statement:
SELECT * FROM post WHERE MATCH( title, description ) AGAINST( "greetings world" );
This would return the following result:
postID title description url
1 Greetings world fun times example.com/post1
3 Hello world Blue sky example.com/post3
Although there was only one row that containing all the words in our query "greetings world" it also returned the row with "hello world" as it contains at least one of the words. MATCH AGAINST using natural language mode does order the rows by relevance so it will return the rows that match more of the words in the query first.
Creating PHP code to fetch search results from your database
Now that you have your SQL statement to fetch the results of a search query, you can now create your server-side code to pass on these results.
For this scenario we will create a PHP script that uses
to query the database for results to a search request, it will fetch 10 results at a time starting from the last loaded result (provided in the request). The results will be stored in an associative array that will be converted to JSON format for easier processing on the clients-side.
searchDB.php
<?php
// search query
$search = $_GET[ "search" ];
// number of previously loaded results
$offset = $_GET[ "loaded" ];
// declare database credentials
$host = "localhost";
$user = "root";
$password = "";
$dbName = "dbName";
// connect to database
$con = new mysqli( $host, $user, $password, $dbName );
// query the database, limiting results to 10 at a time starting from last loaded result
$sql = 'SELECT * FROM post WHERE MATCH( title, description ) AGAINST( "' . $search . '" ) LIMIT ' . $offset . ', 10;';
$result = $con->query( $sql );
// declare array variable to store results
$output = array();
if( $result->num_rows > 0 ) {
while( $row = $result->fetch_assoc() ){
// add row to output array in the form of an associative array
$output[] = array( "title" => $row[ "title" ], "description" => $row[ "description" ], "url" => $row[ "url" ] );
}
}
$con->close();
// convert to JSON and output
echo( json_encode( $output ) );
?>
To test this PHP file, you will need to host it on a server then enter its location into the URL bar in your browser followed by a query string containing the necessary parameters for the search query and number of loaded posts.
Example:
localhost/mySite/searchDB.php?search=world&loaded=0
This will result in the following output:
[{"title":"Greetings world","description":"fun times","url":"example.com\/post1"},{"title":"Hello world","description":"Blue sky","url":"example.com\/post3"}]
Important note:
The example PHP script does not take any precautions against SQL injection and is highly vulnerable to it, this script should only be used on your local server. To use this on a live website you should first validate the inputs and implement
.
Creating Javascript code to fetch and display results
Now that you've created a server-side script to query the database for a search term and return the results, you can now focus on creating client-side code to request the script, fetch the output and convert it into a Javascript object that will then be used to display the results in HTML.
To get the output of our PHP script from within Javascript we'll be making use of an
AJAX method that uses Javascript's XMLHttpRequest object
to request the script and return the output without having to reload the page.
As the output is in the form of a JSON string, we'll convert it into a Javascript object and iterate over it to process and display each result. Your HTML should contain an input for the search query, a way to activate the search function (such as a button) and a container for the search results like so:
<input id="search_input" type="text" placeholder="Search...">
<button onclick="searchPosts(0)">Search</button>
<div id="search results"></div>
We can now create a function that is activated when the search button is clicked, it will fetch the value of the search input and add it to the request.
function searchPosts( loadedResults ){
var query = document.getElementById( "search_input" ).value;
var resultsContainer = document.getElementById( "search_results" );
// clear results container if no previous results have been loaded
if( loadedResults === 0 ){
resultsContainer.innerHTML = "";
}
// create XMLHttpRequest object
var xmlhttp = new XMLHttpRequest();
// create function that is called when request is completed
xmlhttp.onreadystatechange = function() {
if ( xmlhttp.readyState === 4 && xmlhttp.status === 200 ) {
// fetch response text
var response=xmlhttp.responseText;
var outputPosts;
// parse response if it is valid JSON
try{
outputPosts = JSON.parse( response );
}
catch( e ){
return;
}
// iterate over results
for( var i = 0; i < outputPosts.length; i++ ){
// append result to result container, link to url of post
resultsContainer.innerHTML += "<div id='result_" + i + "'><a href='http://" + outputPosts[ i ].url + "'><h3>" + outputPosts[ i ].title + "</h3>" + outputPosts[ i ].description + "</a><div>";
}
// add button to load more results starting from the last loaded result (remove any existing button first if one exists)
try{
document.getElementById( "load_button" ).remove();
}
catch( e ){
return;
}
finally{
resultsContainer.innerHTML += "<br><button id='load_button' onclick='searchPosts( " + ( loadedResults + outputPosts.length ) + " )'>Load more</button>";
}
}
};
// send request to fetch searchDB.php
xmlhttp.open( "GET", "searchDB.php?search=" + query + "&loaded=" + loadedResults, true );
xmlhttp.send();
}
Testing this function with the search query "greetings world" will result in the following:
It may look a tad rough at the moment but there's nothing a bit of CSS can't fix!
If you have any questions or feedback feel free to leave a comment :)
