The MySQL Search Class available from 2404.co.uk is a useful quick way to implement a database search in PHP. However there is an error when doing table joins on approx line 110 of the code the following line:
$sql .= "{$join_type} JOIN {$join_table} ".
should read:
$sql .= "{$join_type} JOIN {$destination_table} ".
Full Code reproduced from 2404.co.uk:
<?
/*MysqlSearch.class.php
Usage:
1. Instansiate class with arguments:
$search_args = array(
"table"=>"tblArticles",
"return_fields"=>"ArticleId, Title, Subject, Content",
"search_fields"=>"ArticleId, Title, Subject, Content",
"joins=>array(
array("type"=>"INNER",
"source_table"=>"tblArticles",
"destination_table"=>"tblCategories",
"source_field"=>"CategoryId",
"destination_field"=>"CategoryId"
)
),
"sort_by"=>"Title"
);
$search = new MysqlSearch($search_args);2. Use the find method – this will return a result set of the specified fields:
$search_results = $search->find("a");3. Loop the results and output, checking for errors:
if($search_results)
{
foreach($search_results as $result)
{
print $result["Title"];
print "<br/>";
}
}
elseif($search->errors)
{
foreach($search->errors as $error)
{
print "$error <br/>";
}
}
else
{
print "No results";
}Notes:
o For relevancy calculations to be correct, search_fields must be the
same as return_fields
*/
class MysqlSearch
{
var $args;
var $errors;
function MysqlSearch($args)
{
// remove illegal characters
$args = $this->clean_var($args);$this->args = $args;
$this->errors = array();
}
function clean_var($var)
{
if(is_array($var))
{
foreach($var as $key=>$value)
{
$var[$key] = $this->clean_var($value);
}
}
else
{
$var = preg_replace("/;|'|\\\/","",$var);
}
return $var;
}
function find($search_term)
{
global $p_db, $p_site;
// get args
$table = $this->args["table"];
$joins = $this->args["joins"];
$return_fields = $this->args["return_fields"];
$search_fields = $this->args["search_fields"];
$sql = "SELECT {$return_fields} ".
"FROM {$table} ";
if($joins)
{
foreach($joins as $join)
{
$destination_table = $join["destination_table"];
$source_table = $join["source_table"];
$source_field = $join["source_field"];
$destination_field = $join["destination_field"];
$join_type = $join["type"];
if(!$join_type)
{
$join_type = "INNER";
}
$sql .= "{$join_type} JOIN {$join_table} ".
"ON {$destination_table}.{$destination_field} = {$source_table}.{$source_field} ";
}
}
if($search_term)
{
$search_term = $this->clean_var($search_term);
$search_term = explode(" ",$search_term);
$search_fields = explode(",",$search_fields);
$sql .= "WHERE ";
for($ti=0;$ti<sizeof($search_term);$ti++)
{
$keyword = $search_term[$ti];
$last_keyword = $search_term[$ti-1];
$next_keyword = $search_term[$ti+1];
if(preg_match("/^[A-Za-z0-9]+$/",$keyword))
{
// was the last keyword an operator
if($this->is_operator($last_keyword))
{
$sql .= strtoupper($last_keyword)." ";
}
// continue if this keyword is an operator
if($this->is_operator($keyword))
{
continue;
}
else
{
$sql .= "(";
for($fi=0;$fi<sizeof($search_fields);$fi++)
{
$field = $search_fields[$fi];
$sql .= "$field LIKE '%{$keyword}%' ";
if($fi != sizeof($search_fields)-1)
{
$sql .= "OR ";
}
}
$sql .= ") ";
}
if(!$this->is_operator($keyword) && !$this->is_operator($next_keyword) && $next_keyword)
{
$sql .= "AND ";
}
}
}
if($this->args["constraints"])
{
foreach($this->args["constraints"] as $field=>$constraint)
{
$sql .= "AND $field = '$constraint' ";
}
}// carry out the query
$res = mysql_query($sql);
if($res)
{
$result_set = array();
while($row = mysql_fetch_assoc($res))
{
// get keyword score
$keyword_score = 0;
foreach($search_term as $keyword)
{
if($keyword)
{
if(!$this->is_operator($keyword))
{
foreach($row as $key=>$value)
{
$keyword_score = preg_match_all("/$keyword/i", $value, $null);
}
}
}
}$row["relevancy"] = $keyword_score;
array_push($result_set, $row);
}
usort($result_set, array($this, "order_result_set"));return $result_set;
}
else
{
// error
array_push($this->errors, mysql_error());
}
}
}
function is_operator($var)
{
$var = strtoupper($var);
switch($var)
{
case "AND":
$return = 1;
break;
case "OR":
$return = 1;
break;
default:
$return = 0;
break;
}
return $return;
}
function order_result_set($a, $b)
{
if(!$this->args["sort_by"])
{
$sort_by = "relevancy";
}
else
{
$sort_by = $this->args["sort_by"];
}
if($a[$sort_by] && $b[$sort_by])
{
$ax = $a[$sort_by];
$bx = $b[$sort_by];
}if ($ax == $bx){ return 0; }
return ($ax > $bx) ? -1 : 1;
}
}
?>