Create a Simple Search Script with Very Accurate Results in PHP
NOTE: - I don't guarantee this will work for you, back-up your data before doing anything to it.
On this page I will explain how you can use the search method explained on the previous page on an existing database. First, figure out what columns you want to search. Maybe you have a site with articles so you want to search the columns `title` and `text` which hold the title and full article of each article on your site. (you can search more columns if you want, or just one)
Here is an example of the script I would run to alter the existing table. Obviously, change your MySQL login info again.
<?php
mysql_connect ("localhost", "USERNAME", "PASSWORD");
mysql_select_db("DATABASE_NAME");
mysql_query("ALTER TABLE `articles` ADD FULLTEXT (`title`, `text`)");
echo 'Table altered';
?>
Code Breakdown:
mysql_query("ALTER TABLE `articles` ADD FULLTEXT (`title`, `text`)"); - This adds a FULLTEXT index to the specified columns, just follow the same format of `column`, `column2`, `column3` etc... to add more columns.
Finally, to search these columns using the same code I posted on the previous page just edit the columns in the MATCH(column1, column2, etc..) in the query. For example, if above I added the FULLTEXT index to the columns `title` and `text`, I would have this code.
<?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, text) AGAINST ('".$search."' IN BOOLEAN MODE) AS score FROM `articles` WHERE MATCH (title, text) AGAINST ('".$search."' IN BOOLEAN MODE) ORDER BY `score` DESC";
$result = mysql_query($query);
while($data = mysql_fetch_array($result)) {
echo $data['title'].'<br />';
}
}
?>
Now you can search your existing database of data, and all you have to do is integrate it and format the results however you like.
