Simple MySQL Search Function


 / Published in: PHP
 

A quick and simple way to search a MySQL database. Example: mysqlsearch('items', 'title tags', isset($GET['q'])?$GET['q']:'', Array('columns'=>'*', 'method'=>'OR', 'extrasql'=>'AND active = "true" ORDER BY id DESC'));

  1. if (!function_exists('mysql_search')) {
  2.  
  3. function mysql_search($table, $columns, $query = '', $options = Array()) {
  4.  
  5. if (empty($query)) { return Array(); }
  6.  
  7. $sql_query = Array();
  8.  
  9. $options['columns'] = isset($options['columns'])?$options['columns']:'*';
  10. $options['method'] = isset($options['method'])?$options['method']:'OR';
  11. $options['extra_sql'] = isset($options['extra_sql'])?$options['extra_sql']:'';
  12.  
  13. $query = ereg_replace('[[:<:]](and|or|the)[[:>:]]', '', $query);
  14. $query = ereg_replace(' +', ' ', trim(stripslashes($query)));
  15.  
  16. $pattern = '/([[:alpha:]:]+)([[:alpha:] ]+)[[:alpha:]]?+[ ]?/i';
  17.  
  18. $regs = Array();
  19.  
  20. preg_match_all($pattern, $query, $regs);
  21.  
  22. $query = $regs[0];
  23.  
  24. while (list($key, $value) = @each($query)) {
  25.  
  26. $column = $columns;
  27. $keywords = urldecode($value);
  28.  
  29. if (strpos($value, ':')) {
  30.  
  31. $column = substr($value, 0, strpos($value, ':'));
  32. $keywords = trim(substr($keywords, strpos($keywords, ':') + 1));
  33. $keywords = ereg_replace('\'', '', $keywords);
  34.  
  35. } else { $keywords = ereg_replace(' +', '|', $keywords); }
  36.  
  37. $column_list = explode(' ', $column);
  38.  
  39. $sql = Array();
  40.  
  41. for ($i = 0; $i < count($column_list); $i++) { $sql[] = '' . $column_list[$i] . ' REGEXP "' . $keywords . '"'; }
  42.  
  43. $query[$key] = Array('orignal'=>$value, 'sql'=>implode(' ' . $options['method'] . ' ', $sql));
  44.  
  45. $sql_query = array_merge($sql_query, $sql);
  46. $sql_query = implode(' ' . $options['method'] . ' ', $sql_query);
  47.  
  48. }
  49.  
  50. $results = mysql_fetch_results(mysql_query('SELECT ' . $options['columns'] . ' FROM ' . $table . ' WHERE ' . $sql_query . ' ' . $options['extra_sql']));
  51.  
  52. return $results;
  53.  
  54. }
  55.  
  56. }

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: golena on December 31, 2007
Posted By: optimal on June 26, 2008

Is this designed for FULLTEXT searching, or LIKE matching?

Also, do you have a version with comments?

Posted By: roark on April 9, 2010

Hey Neo Geek,

Thanks for the great little snipplet,

In order to search alpha & numeric values I changed the pattern accordingly, $pattern = '/([[:alnum:]:]+)([[:alnum:] ]+)[[:alnum:]]?+[ ]?/i'; It worked for normal searches like "apple" and "1234" howevr when I search for"1234-htry-3345" it splits the query at the "-" to an array of ("1234", "htry", "3345") and the search fails. My Regex knowledge leaves a lot to be desired, please would you explain where I'm goig wrong if I would like to search for the above term?

Thanks in advance, Roark

Posted By: ofadlaoui on October 15, 2010

You should add something like an example.. Some people don't understand this, you know.

Regards, Oussama.

Posted By: ofadlaoui on October 15, 2010

You should add something like an example.. Some people don't understand this, you know.

Regards, Oussama.

Posted By: JackPonting on November 30, 2018

It called Simple function but when it comes to code it is quite difficult. Thanks for sharing a quick and simple way to search a MySQL database.

Jack, http://www.qualitydissertation.co.uk/how-to-structure-a-dissertation

Posted By: academicconsultant05 on January 4, 2019

There are 2 most important part in students life one is assignments and another one is coursework so Some students are poor in such things and some don't get time for it so we are Uniresearchers provides you Assignment writing help and help with coursework. So We provide all kind of academic writing services at an affordable cost. And also provides 24*7 assistance, plagiarism is below than 10% and assured distinction.

You need to login to post a comment.