by Dragos, on October 14th, 2008  

I am currently developing a website that needs a good script to search in the database. The basic MySQL function LIKE doesn’t return some good results and because i didn’t want to go deeper with mysql with its regex i started to create my own script to search in a mysql database, actually it is a class, my first class ever written :) .
So here it is:

  1. class PHP_MySQL_search {
  2. /*
  3.                 Copyright 2008 Mocrii Dragos.
  4.                 To understand how this class works and what you need to do in order to use it, go to http://seoanalytic.com/blog/55/php-mysql-search-class-script/
  5. */
  6. function setDBdetails($host,$usr,$pwd) { //set database details
  7.         $this->HOST=$host;
  8.         $this->USR=$usr;
  9.         $this->PWD=$pwd;
  10. }
  11.  
  12. function DBselect($db) { //set database to work with
  13.         $this->db=$db;
  14. }
  15. function setTable($table) { //set table(s) to search in
  16.         $this->table=$table;
  17. }
  18. function setID($id) { //select the identifier of your rows. Usually it is id
  19.         $this->ID=$id;
  20. }
  21. function setColumns($cols) { //set the columns to search in
  22.         $this->columns=$cols;
  23. }
  24. function setOrderBy($orderby) { //this is how data will be retrieved from the database. This is not how results will be displayed.
  25.         $this->OrderBy=$orderby;
  26. }
  27. function setLimit($num) { //set the limit of results per page
  28.         $this->Limit=(int)$num;
  29. }
  30. function setBegin($num) { //set the start row. Starting with this row results will be retrieved until limit has been reached.
  31.         $this->BeginFrom=(int)$num;
  32. }
  33. function nin_array($val) { //filter arrays from common words. You may add your own.
  34. $filtered=array(‘that’,‘with’,‘they’,‘have’,‘this’,‘from’,’some’,‘what’,‘which’,‘each’,‘then’,‘when’,‘only’,‘because’);
  35. return (strlen($val)>3 && !in_array(strtolower($val),$filtered));
  36. }
  37. function find($kp) { //this will search and return the result array
  38. $kp=trim(strip_tags(urldecode($kp)));   
  39. $matches=array(‘\’‘,‘"’,‘/’,‘.’,‘,’,‘`’,‘!’,‘?’,‘@’,‘%’,‘_’,‘*’,‘[',']‘,‘{’,‘}’,‘$’); //filter from punctuation signs. only plain text is needed
  40. $replacements=”;
  41. $kp=str_replace($matches,$replacements,$kp); //This will filter unwanted chars
  42. $kp=preg_replace(‘/\s\s+/’, ‘ ‘, $kp); //This will filter excess whitespace
  43. $kpa=explode(‘ ‘,$kp);
  44. $kpa=array_filter($kpa,array($this,"nin_array"));
  45. if(!$this->BeginFrom || $this->BeginFrom==”) $this->BeginFrom=0;
  46. if(!$this->Limit || $this->Limit==”) $this->Limit=10;
  47. if(!$this->columns || $this->columns==”) {
  48.         $this->columns="*";
  49.         $build_query="SELECT ".$this->columns." FROM ".$this->table.($this->OrderBy!=”?" ORDER BY ".$this->OrderBy:"")." LIMIT ".$this->BeginFrom.",18446744073709551615";
  50. }
  51. else {
  52.         $build_query="SELECT ".$this->ID.",".$this->columns." FROM ".$this->table.($this->OrderBy!=”?" ORDER BY ".$this->OrderBy:"")." LIMIT ".$this->BeginFrom.",18446744073709551615"; //the big number is used to select all rows from limit to the end of the table. if you have more than 18446744073709551615 rows in your table, change this number to a bigger one :)
  53. }
  54. $con=mysql_connect($this->HOST,$this->USR,$this->PWD);
  55. if(!$con || !mysql_select_db($this->db)) return false;
  56. $my_query=mysql_query($build_query,$con);
  57. //echo mysql_num_rows($my_query);
  58. $results=array(); //echo $build_query;
  59. $kontor=0;
  60. $kontor2=0;
  61. while($row=mysql_fetch_array($my_query,MYSQL_ASSOC)) {
  62.         $kontor2++;
  63.         $row=array_map(’stripslashes’,$row);
  64.         $tmp=implode(‘ ‘,$row);
  65.         $matches=array(‘\’‘,‘"’,‘/’,‘.’,‘,’,‘`’,‘!’,‘?’,‘@’,‘%’,‘_’,‘*’,‘[',']‘,‘{’,‘}’,‘$’);
  66.         $replacements=”;
  67.         $tmp=trim(strip_tags($tmp));
  68.         $tmp=str_replace($matches,$replacements,$tmp); //This will filter unwanted chars
  69.         $tmp=preg_replace(‘/\s\s+/’, ‘ ‘, $tmp); //This will filter excess whitespace
  70.         $tmpa=explode(‘ ‘,$tmp);
  71.         $tmpa=array_filter($tmpa,array($this,"nin_array"));
  72.         $tmpa=array_map("strtolower",$tmpa);
  73.         for($i=0;$i<count($kpa);$i++) $found[$kpa[$i]]=0;
  74.         $found2=false;
  75.         foreach($tmpa as $kke) {
  76.        
  77.                 foreach($kpa as $kpp) {
  78.                         if(abs(strlen($kke)-strlen($kpp))<=2 && (substr($kke,0,strlen($kpp))==$kpp || substr($kpp,0,strlen($kke))==$kke))  { $found2=true; $found[$kpp]++; }
  79.                 }
  80.        
  81.         }
  82.        
  83.         if($found2===true) { $bld=array("kwds"=>$found,"res"=>$row); array_push($results,$bld); $kontor++;}
  84.         if($kontor>=$this->Limit) break; //if limit has been reached then break out of the while cycle
  85. }
  86. mysql_close($con);
  87. return $results;
  88. }
  89.  
  90. }
  91.  

To use it you need to set some details. Here is an example of its use:

  1.  
  2.         $msearch=new PHP_MySQL_search;
  3.         $msearch->setDBdetails("localhost","root","secret_password"); //
  4.         $msearch->DBselect("database_name");
  5.         $msearch->setTable("table_name(s)");
  6.         //$msearch->setOrderBy("id");
  7.         $msearch->setID("id");
  8.         $msearch->setBegin(0); // this will be used to form the <strong>SELECT</strong> query: <strong>limit 0,2</strong> , for example
  9.         $msearch->setLimit(15); //this is the number of the results per page
  10.         $msearch->setColumns("title,name,occupation"); //columns to search in. If you do not want to specify the columns then comment this line or set the value <strong>*</strong>
  11.         $result_array= $msearch->find(‘keyword phrase’)
  12.         print_r(  $result_array );
  13.  
  14.  

The dump of $result_array will have the form:
Array ( [0] => Array ( [kwds] => Array ( [keyword1] => 1, [keyword2]=> 5 ) [res] => Array ( [id] => 34 [title] => The title [content] => The content [summary] => This is about …) ) ) ,
where the main array contains all results, the second one contains the exploded words of the keyword phrase and how many times each one was found + [res] contains an array with the results from the columns you specified, or all columns if you set the * value. If it is hard for you to understand my explanation, i bet it is, just try it yourself and analyze the output for at least 3 different keyword phrases.
Any questions? Ask here.