In this tutorial, we’ll be teaching how to search for multiple keywords in PHP and MySQL. For example, we have a search bar, and we wanted to search for multiple keywords ie. “PHP and MySQL”. Normally, without a search engine, PHP will take the input “PHP and MySQL” as one keyword. So if it cannot find the exact keyword “PHP and MySQL”, then it would not return a result even if there is a word PHP or MySQL word in the database. The same happens when we are adding commas or spaces in the search term ie. “PHP,MySQL” or “PHP MySQL”.
So technically, what we are gonna do here is make a simple search engine. Something that can break the keyword down to multiple keywords and show multiple results that match the keywords. Makes sense?
In this tutorial, we are going to assume that you already know the basics of PHP and MySQL, and you have some experience with it.
The Solution
To search for multiple keywords and get multiple results, we need to invoke the LIKE operator in MySQL to get multiple results and explode function in PHP to separate the keywords in the search term. We may also have to sanitize our search term for even better results.
Let us assume we have a database named “testdb” with a table called links and a column called search_item, and we are going to search that database for multiple keywords. In search_item column, we have the entries “PHP”, “MySQL”, “Apple”, “Windows”, “PHPMyAdmin”.
Now, let our search term be included in $_POST[‘keywords’]. Note that we have to always escape our search term. Also, we might have to do some extra sanitizing. For this tutorial, we’ll be removing more than one white spaces, any tab or a new line (line break). This makes sure that our search term won’t have any extra white spaces, tabs or new lines that can mess up with our results. In addition, we might have to trim so all whitespaces before and after will be remove from our search string. To do this:
$queried = mysqli_real_escape_string($conn, preg_replace('/\s+/', ' ',trim($_POST['keywords'])));
mysqli_real_escape_string will escape special characters in a string for use in an SQL statement. For instance we have a ‘, that will be converted to \’. The reason for this is that in a MySQL query, we use special characters like ‘. Having another ‘ in the query can be problematic as it will cancel some opened parameters that are using ‘, thereby breaking our query and causing an error.
preg_replace is use here to replace some undesired values in the query using regex (regular expression). Note that in our code, we are using \s+, which means whitespace (space, tab or newline) followed by one or more whitespace. Our code means replace two or more whitespace with a single space. The same will be done to a tab or a new line.
Now, that we have sanitize our query, we further sanitize it by converting commas into spaces. Otherwise, commas will be included in our query and this can impact the results. To allow our query to work with commas, we create these conditions:
if(strpos($queried,", ")){
$query = str_replace(", "," ",$queried);
$keys = explode(" ",$query);
} else if(strpos($queried,",")){
$query = str_replace(","," ",$queried);
$keys = explode(" ",$query);
} else {
$keys = explode(" ",$queried);
}
str_pos finds the position of the first occurrence of a substring in a string. If it finds the occurrence, it will return a value of true else false. We are using this function to search for commas and its other variant — comma with space in a search term. We begin the condition searching with comma with space rather than only a comma since if we begin with comma only, and our query has comma with space, it would met the comma only condition since comma with space query also has a comma. If the condition is met then the second condition and the else condition will be ignored leaving us doing a method for comma only, the space will not be handled by this method. But if we do comma with space in the first condition, then it has to find comma with space and comma only will not suffice. So if we are searching for a query with only a comma included, it would fail the first condition and go to the second condition for a different processing.
str_replace replaces all occurrences of the search string with the replacement string. We are using this to search the search term for commas and convert them into spaces.
So our condition goes like this, if we find comma with space (, ) then we do a process that replaces comma with space with a whitespace in the search term. Then our search term will be split by explode into multiple values using whitespace as the delimiter (our cue), so every time PHP encounters a whitespace at the end of the word, it will separate that word from the others. Else if the first condition was not met then it will search for comma in the search term. If found, PHP will replace all commas with whitespace then separate the words using whitespace as the delimiter. Else, it will simply separate the search term into several keywords using whitespace as the delimiter.
Now that we sanitized our search term and broke them into pieces, it’s time to do the MySQL query.
Note that our database table name is links and our column to search is search_item. To search search_item for the keywords:
$sql = "SELECT * FROM links WHERE search_item LIKE '%$queried%' ";
The query means select all columns from table links where the column search_item has something like our search items. LIKE allows us to search for something that resembles the keyword rather than look for the exact keyword. Also note that % has to be included. This operator is called the modulus. In the query, it will search for the keyword or the keyword + anything attached into the keyword. For example, we have PHPMyAdmin in the database, and we search for PHP, the modulus will look for any word that has “PHP” attached into it and therefore, it would return PHPMyAdmin since it has the word PHP in it.
Our query, however, is not enough. We didn’t gave room for other keywords (assuming our search term has more than one). So, we need another condition to handle the keywords and include them in the query.
Since it’s for every keyword, then we have to create a loop. For this one, let’s use foreach.
foreach($keys as $key){
$sql .= " OR search_item LIKE '%$k%' ";
}
Our code here means that for each keyword ($keys), it will be treated as a single entity ($key), and for that every key, concatenate the $sql variable that contains an additional sql query that searches search_item for the keyword that resembles another key (keyword). This loop will create as many extra conditions and attach it to our main MySQL search query as long as there are extra keywords. Be careful though, you might have to check the length of the search term, otherwise, if the query becomes too big, it could crash the database. For this, it is best to limit the characters that can be contained in the search bar.
After that, it’s time to request the query.
$result = mysqli_query($conn, $sql);
Our variable $conn, by the way, should be containing our database credentials. Then $sql is our query.
Finally, to output our results, we need to create another loop. This time, we’ll use while to display all the matches our search query has found.
while($resulti = mysqli_fetch_assoc($result)){
echo $resulti['search_item'] . '<br>';
}
The code means that while there is an output, it will print or display the result in our page. mysqli_fetch_assoc fetches a result row as an associative array. In short, it will associate our results with the column header name so that our array can be called by column name. Since our column name is search_item, and our container variable is $resulti, then we can use $resulti[‘search_item’] to display the contents of our output.
The complete code will look something like this:
$queried = mysqli_real_escape_string($conn, preg_replace('/\s+/', ' ',trim($_POST['keywords'])));
if(strpos($queried,", ")){
$query = str_replace(", "," ",$queried);
$keys = explode(" ",$query);
} else if(strpos($queried,",")){
$query = str_replace(","," ",$queried);
$keys = explode(" ",$query);
} else {
$keys = explode(" ",$queried);
}
$sql = "SELECT * FROM links WHERE search_item LIKE '%$queried%' ";
foreach($keys as $k){
$sql .= " OR search_item LIKE '%$k%' ";
}
$result = mysqli_query($conn, $sql);
while($resulti = mysqli_fetch_assoc($result)){
echo $resulti['search_item'] . '<br>';
}
Here is a demonstration of how our code works:
Closing Remarks
This tutorial can also be found in Stackoverflow. However, that has been made years ago and back then they were using mysql instead of mysqli function. We have develop the code and updated it so it would be compatible to the latest version of PHP — PHP 7.1 as of this writing.
Follow me at:
