Your Ad Here

advanced code snippet search

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'));

Expand | Embed | Plain Text 
  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.

You need to login to post a comment.