Create a Simple Search Script with Very Accurate Results in PHP
Logic (if you are not interested in the logic skip down below)
One of the most important features, but often overlooked is the search functionality of a site.
I see many tutorials which just use LIKE="%search%" but the problem with that is it will look for the entire string. For example, if I typed in "php mysql tutorial", you will almost certainly never get a result using that because no tutorial would have "php mysql tutorial" in that exact order in the title.
To overcome that problem I have seen solutions which just make use of the PHP explode() function, which splits the the search into an array of keywords, and then putting each keyword in the query string, like this LIKE="%$keyword[0]%" OR LIKE="%$keyword[1]%"... and so on, although this will not work very well because it will just show the result in the order they are in the database, of the tutorials that are related to the search. So if I typed in "php login script", I would get every single tutorial with the words "php", "login", and "script". So if the tutorial about making a login script in PHP was say the 3000th tutorial then I would get every tutorial with "php" or "script" in it that comes before the 3000th tutorial.
With that being said, I feel the need to show a more effective method. So to get started, we need to make a database, to hold our data and two example results. First make a database, if you haven't already then run this script once and delete it.
Change the MySQL login data accordingly. If you don't know how, read below.
Code Breakdown:
mysql_connect ("localhost", "USERNAME", "PASSWORD"); - The localhost value will almost always be the same but change the USERNAME and PASSWORD to your MySQL username and password, respectively.
mysql_select_db("DATABASE_NAME"); - Change the DATABASE_NAME to the database you made.
FULLTEXT KEY `title` (`title`) - This will allow us to search the column `title` for the method we are using.
Now for the actual search script, which should be very easy to implement on any site. Again, change the information for connecting to MySQL.
Code Breakdown:
Search - <input type="text" name="search" value="<?php if($_POST['search']) echo $_POST['search']; ?>" /><br /> - This will show the search in the search box after they have already submitted the search.
if(!get_magic_quotes_gpc()) { - Always make sure you take proper security measures, this will add slashes if magical quotes is not on, to prevent SQL injection.
$query = "SELECT *, MATCH (title) AGAINST ('".$search."' IN BOOLEAN MODE) AS score FROM `title` WHERE MATCH (title) AGAINST ('".$search."' IN BOOLEAN MODE) ORDER BY `score` DESC"; - Here is us actually doing the searching. This will "score" each result based on is relevance to the search and then select each result and order them by the score descending.
while($data = mysql_fetch_array($result)) { - This will loop through each result and then the line below it will output it.
It is that easy to make an effective search in PHP and MySQL. MySQL also has some built in features such as common words that won't search, which are called stopwords. Also, if you search "java" you will get both result but if you search "java -coffee" you will get the result about the programming language, because the minus means it will get result without the word following it. Alternatively, there is a plus which does the opposite, if you search "java +coffee" it would only return result with the word coffee in them.
Thats it, if you would like to know how to implement this on an existing database or how to search more than one column then I have explained it on the next page, otherwise, that concludes the tutorial.
One of the most important features, but often overlooked is the search functionality of a site.
I see many tutorials which just use LIKE="%search%" but the problem with that is it will look for the entire string. For example, if I typed in "php mysql tutorial", you will almost certainly never get a result using that because no tutorial would have "php mysql tutorial" in that exact order in the title.
To overcome that problem I have seen solutions which just make use of the PHP explode() function, which splits the the search into an array of keywords, and then putting each keyword in the query string, like this LIKE="%$keyword[0]%" OR LIKE="%$keyword[1]%"... and so on, although this will not work very well because it will just show the result in the order they are in the database, of the tutorials that are related to the search. So if I typed in "php login script", I would get every single tutorial with the words "php", "login", and "script". So if the tutorial about making a login script in PHP was say the 3000th tutorial then I would get every tutorial with "php" or "script" in it that comes before the 3000th tutorial.
With that being said, I feel the need to show a more effective method. So to get started, we need to make a database, to hold our data and two example results. First make a database, if you haven't already then run this script once and delete it.
Change the MySQL login data accordingly. If you don't know how, read below.
<?php
mysql_connect ("localhost", "USERNAME", "PASSWORD");
mysql_select_db("DATABASE_NAME");
mysql_query("CREATE TABLE IF NOT EXISTS `title` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(50) NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;");
mysql_query("INSERT INTO `title` (`id`, `title`) VALUES
(1, 'A hot cup of Java coffee'),
(2, 'An introduction to the Java programming language');");
echo 'Table made, default data inserted';
?>
Code Breakdown:
mysql_connect ("localhost", "USERNAME", "PASSWORD"); - The localhost value will almost always be the same but change the USERNAME and PASSWORD to your MySQL username and password, respectively.
mysql_select_db("DATABASE_NAME"); - Change the DATABASE_NAME to the database you made.
FULLTEXT KEY `title` (`title`) - This will allow us to search the column `title` for the method we are using.
Now for the actual search script, which should be very easy to implement on any site. Again, change the information for connecting to MySQL.
<?php
mysql_connect ("localhost", "USERNAME", "PASSWORD");
mysql_select_db("DATABASE_NAME");
?>
<form action="search.php" method="post">
Search - <input type="text" name="search" value="<?php if($_POST['search']) echo $_POST['search']; ?>" /><br />
<input type="submit" name="searchbtn" value="Search" />
</form>
<?php
if($_POST['searchbtn']) {
echo '<br /><br />';
if(!get_magic_quotes_gpc()) {
$search = addslashes($_POST['search']);
}else{
$search = $_POST['search'];
}
$query = "SELECT *, MATCH (title) AGAINST ('".$search."' IN BOOLEAN MODE) AS score FROM `title` WHERE MATCH (title) AGAINST ('".$search."' IN BOOLEAN MODE) ORDER BY `score` DESC";
$result = mysql_query($query);
while($data = mysql_fetch_array($result)) {
echo $data['title'].'<br />';
}
}
?>
Code Breakdown:
Search - <input type="text" name="search" value="<?php if($_POST['search']) echo $_POST['search']; ?>" /><br /> - This will show the search in the search box after they have already submitted the search.
if(!get_magic_quotes_gpc()) { - Always make sure you take proper security measures, this will add slashes if magical quotes is not on, to prevent SQL injection.
$query = "SELECT *, MATCH (title) AGAINST ('".$search."' IN BOOLEAN MODE) AS score FROM `title` WHERE MATCH (title) AGAINST ('".$search."' IN BOOLEAN MODE) ORDER BY `score` DESC"; - Here is us actually doing the searching. This will "score" each result based on is relevance to the search and then select each result and order them by the score descending.
while($data = mysql_fetch_array($result)) { - This will loop through each result and then the line below it will output it.
It is that easy to make an effective search in PHP and MySQL. MySQL also has some built in features such as common words that won't search, which are called stopwords. Also, if you search "java" you will get both result but if you search "java -coffee" you will get the result about the programming language, because the minus means it will get result without the word following it. Alternatively, there is a plus which does the opposite, if you search "java +coffee" it would only return result with the word coffee in them.
Thats it, if you would like to know how to implement this on an existing database or how to search more than one column then I have explained it on the next page, otherwise, that concludes the tutorial.
