For the longest time I used the MySQL ‘LIKE’ statement style for searching by keywords, which it is probably the most reliable and fastest option. But today I’m going to talk about another option you have to create a more effective keyword search and get the result from MySQL order by keywords relevance.
For this tutorial you will require (unless you work directly in your web hosting):
- xampp or any other Web Server installed in your system. (Apache/MySQL/PHP).
- PHPMyAdmin – Browser Based PHP MySQL Administrator.
- Some knowledge in PHP and MySQL is a plus but not a most.
- Variable: $input (variable that holds the user request), $string ($input exploded in an array), $sql (the SQL request string variable).
Alright let’s first create a table in MySQL to test the code:
CREATE TABLE IF NOT EXISTS `cds` (
`id` INT NOT NULL AUTO_INCREMENT ,
`title` VARCHAR(45) NULL ,
`year` VARCHAR(45) NULL ,
`tags` VARCHAR(255) NULL ,
PRIMARY KEY (`id`),
FULLTEXT (tags(255))
)
ENGINE = MyISAM;
Notice that I created an FULLTEXT Index using the `tags` column. This is going to be our search indexed column.
Now let’s populate the table `cds` with some information:
INSERT INTO `cds` (`id`, `title`, `year`, `tags`)
VALUES (NULL, 'What about Now', '1996', 'Christian,The Kry,Rock,Alternative,1996'),
(NULL, 'The Singles', '2000', 'Pop,80''s,Rock,Hard Rock,Corey Hart,2000'),
(NULL, '18 ''til I die', '1996', 'Bryan Adams,Pop,1996,Hard Rock'),
(NULL, 'Almas Unidas', '2002', 'Cristiano,Perucho,Christian,Rock,Pop,Ballad'),
(NULL, 'Falta Amor', '1992', 'Pop,Maná,Latino,Rock');
With PHPMyAdmin installed you only need to select and Schema then select SQL to run both this codes. You can run it both at once all each by itself. If you run both at once then make sure CREATE is the first code and there’s a semicolon (;) after line ENGINE = MyISAM.
Previously, when I made a search with a list of keywords I had to explode a string line in an array then run a dynamic list of LIKE statements, for example:
<?php
//exploding user input into an array
$list = explode(' ',$input);
$sql = "SELECT * FROM `cds` WHERE";
$sqlOption = array();
//--
//creating tags keywords search
foreach($list as $keyword){
$sqlOption[] = "tags LIKE '%".addslashes($keyword)."%'";
}
//--
// joining the array to look something like
// tags LIKE 'keyword1' OR tags LIKE 'keyword2' etc...
$sql .= " ".join(' OR ',$sqlOption).";";
?>
If the user input would’ve be something like "1996 80's Rock" the previous SQL would looks like:
SELECT * FROM cds WHERE tags LIKE '%1996%' OR tags LIKE '%80\'s%' OR tags LIKE '%Rock%';
Even though this way is faster in some cases it doesn’t give you the option to order the results by relevance of the keywords entered. Now you might ask, how can I do that? Well, you can make a couple lines of codes with PHP to do this, all you can just use the MATCH/AGAINST functionality included with MySQL. Let see the code:
<?php
//join the previous array $list this time
//using the + sign that means that tags must include
//and * that's like % in the LIKE statement
$string = join('* +',$list);
//--
//We are using now the MATCH AGAINST function from MySQL
$sql = "SELECT *,MATCH(tags) AGAINST(+$string*) AS `relevance` FROM `cds` WHERE MATCH(tags) AGAINST('+$string*' IN BOOLEAN MODE) ORDER BY relevance DESC;";
?>
As you see this code is a little bit cleaner, but this is not why I prefer it. Look closer the SQL statement, I created a column called relevance holding the result from the MATCH/AGAINST statement, then run it again as the filter for the search but this time using IN BOOLEAN MODE which will determine a value from 0 to 1 in relevance, the most precise results will be closer to 1 and the less relevant will be closer to 0, meaning the most relevant will be in top because we set the ORDER BY as DESC (descendant).
The downside is that multiple columns search tend to be a hassle, even just for the slightest mistake won’t work, sometimes throw INDEX errors even though there are FULLTEXT indexes set by each column. Remember, any column that you would like to search need to have a FULLTEXT index in order the MATCH/AGAINST statement to work. Meaning that might slow down performance or increase bandwidth. The recommended way to do it is to create a column or an indexed table to which you run the code against.
By the way, this is just basic functionality, remember to always secure your code with whatever extension you use, PDO, MySQLi, etc…
If you would like more information about this functionality from MySQL you can visit:
Well, I hope this would help anyone… HAPPY CODING!