Location: PHPKode > scripts > Relevant Search in MySQL > relevant-search-in-mysql/relevant_search.php
<?php

/**
 * Relevant Search
 * 
 */
ini_set('display_errors', 1);
class RelevantSearch {
	public $return=array();
	public $db;
	public $type;
	public $generalQ=array();
	public $restriction=array();
	public $searchParams=array();
	public $select=array();
	public $tables=array();
	public $links=array();
	public $addedTables=array();
	/**
	 * Making connection with DB, setting UTF-8 relation 
	 * and deafault General params relation type
	 *
	 * @param array $dbopt
	 */
	public function __construct($dbopt) {	
		$this->db = new PDO("mysql:host=".$dbopt['host'].";dbname=".$dbopt['dbname'], $dbopt['login'], $dbopt['pass']);
		$this->db->exec('SET NAMES UTF8');
		$this->type='or';
	}
	/**
	 * Adding table to the search query
	 *
	 * @param string $name - table name
	 * @param string $as - synonym of table
	 */
	public function addTable($name,$as) {
		$this->tables[]=array("name"=>$name,"as"=>$as);
		$this->addedTables[]=$as;
	}
	/**
	 * Add field wich will be selected for output
	 *
	 * @param string $base	- table name
	 * @param mixed $name	- column name
	 */
	public function addSelect($base,$name) {
		if(is_array($name)) {
			foreach($name as $el) {
				if(!array_key_exists($base.".".$el,$this->select))
				$this->select[$base.".".$el]=$base.".".$el." ".$base."_".$el;
			}
		} 
	}
	/**
	 * Add link between tables
	 *
	 * @param string $base1 - table1 where linking (where linking condition is placed)
	 * @param string $link1 - column in table1 wich linked
	 * @param string $base2	- table2 with what linking
	 * @param string $link2	- column in table2 wich linked
	 */
	public function addLink($base1,$link1,$base2,$link2) {
		$this->links[$base1]=$base1.".".$link1."=".$base2.".".$link2;
	}
	/**
	 * Adding Restriction to search
	 *
	 * @param string $table - table name
	 * @param string $name	- column name
	 * @param string $oper	- operator
	 * @param string $value	- value
	 * @param bool $date	- make date transformation
	 * @param bool $date_format	- date format
	 */
	public function addRestriction($table,$name,$oper,$value,$date=false,$date_format="") {
		switch($oper) {
			case ">":
				$this->restriction[$table][]="$table.$name>$value";
				break;
			case "<":
				$this->restriction[$table][]="$table.$name<$value";
				break;
			case "<=":
				$this->restriction[$table][]="$table.$name<=$value";
				break;
			case ">=":
				$this->restriction[$table][]="$table.$name>=$value";
				break;
			case "=":
				$this->restriction[$table][]="$table.$name='$value'";
				break;
			case "!=":
				$this->restriction[$table][]="$table.$name!='$value'";
				break;
			case "like":
				$this->restriction[$table][]="$table.$name like '".trim($value)."%'";
				break;
			case "between":
				if($date) {
					$this->restriction[$table][]="$table.$name between '".date($date_format,strtotime(trim($value[0])))."' and '".date($date_format,strtotime(trim($value[1])))."'";
				} else {
					$this->restriction[$table][]="$table.$name between '".trim($value[0])."' and '".trim($value[1])."'";
				}
				break;
		}
		
	}
	/**
	 * Adding General Search param
	 *
	 * @param string $table - table name
	 * @param string $name	- column name
	 * @param string $oper	- operator
	 * @param string $value	- value
	 * @param float $rate	- relevant rate
	 * @param bool $date	- make date transformation
	 * @param bool $date_format	- date format
	 */
	public function addGeneral($table,$name,$oper,$value,$rate,$date=false,$date_format="") {		
		$false=false;
		if(!is_array($value) && $date) $value=date($date_format,strtotime(trim($value)));
		switch($oper) {
			case ">":
				$this->generalQ[]="$table.$name>$value";
				break;
			case "<":
				$this->generalQ[]="$table.$name<$value";
				break;
			case "<=":
				$this->generalQ[]="$table.$name<=$value";
				break;
			case ">=":
				$this->generalQ[]="$table.$name>=$value";
				break;
			case "=":
				$this->generalQ[]="$table.$name='$value'";
				break;
			case "!=":
				$this->generalQ[]="$table.$name!='$value'";
				break;
			case "like":
				$this->generalQ[]="$table.$name like '".trim($value)."%'";
				break;
			case "between":
				if($date) {
					$this->generalQ[]="$table.$name between '".date($date_format,strtotime(trim($value[0])))."' and '".date($date_format,strtotime(trim($value[1])))."'";
				} else {
					$this->generalQ[]="$table.$name between '".trim($value[0])."' and '".trim($value[1])."'";
				}
				break;
			default:
				$false=true;
				break;		
		}
		if(!$false) {
			$this->addSelect($table,array($name));
			$this->searchParams[$table."_".$name][]=array($oper,$value,$rate,$date);
		}
	}
	/**
	 * Adding Bonus Search param
	 *
	 * @param string $table - table name
	 * @param string $name	- column name
	 * @param string $oper	- operator
	 * @param string $value	- value
	 */
	public function addSecondary($table,$name,$oper,$value,$rate,$date=false) {
		$this->addSelect($table,array($name));
		$this->searchParams[$table."_".$name][]=array($oper,$value,$rate,$date);
	}
	/**
	 * Setting type of relation between General Search params
	 *
	 * @param string $type - 'or' or 'and'
	 */
	public function setType($type) {
		$this->type=$type;
	}
	/**
	 * Make query and sort it by relevantion
	 *
	 * @return array - array of fetched query data sorted by relevantion
	 */
	public function make() {
	$first=array_shift($this->tables);
		$FROM=$first["name"]." ".$first["as"];
		$JOINS='';		
		foreach($this->tables as $table) {
			$RESTRICT=(count($this->restriction[$table['as']])>0)?" and (".implode(" and ",$this->restriction[$table['as']]).")":"";
			if(strlen($RESTRICT)>0) {$JOIN=" JOIN ";} else {$JOIN=" LEFT JOIN ";}
			$JOINS.=$JOIN.$table['name']." ".$table['as']." on (".$this->links[$table['as']].$RESTRICT.")";	
		}
		$sql=
		"		
		SELECT DISTINCT ".implode(",",$this->select)." from ".$FROM.$JOINS."
		WHERE
		( ".implode(" ".$this->type." ",$this->generalQ)." )
		";
		$query=$this->db->query($sql);
		$res=$query->fetchAll();
		$this->doRelevant($res);
		krsort($this->return);
		return $this->return;
	}
	/**
	 * Sort fetched query by relevantion
	 *
	 * @param array $data - fetched query data
	 */
	private function doRelevant($data) {
		foreach($data as $el) {
			$rel=0;
			foreach($this->searchParams as $k=>$v) {
				foreach($v as $val) {
					switch($val[0]) {
						case ">":
							if($el[$k]>$val[1]) $rel+=$val[2];
							break;
						case "<":
							if($el[$k]<$val[1]) $rel+=$val[2];
							break;
						case "<=":
							if($el[$k]<=$val[1]) $rel+=$val[2];
							break;
						case ">=":
							if($el[$k]>=$val[1]) $rel+=$val[2];
							break;
						case "=":
							if($el[$k]==$val[1]) $rel+=$val[2];
							break;
						case "!=":
							if($el[$k]!=$val[1]) $rel+=$val[2];
							break;
						case "like":
							if(mb_eregi("^".$val[1]."",trim($el[$k]))) $rel+=$val[2];
							break;
						case "between":
							if($val[3]) {
							if(strtotime($el[$k])>=strtotime(trim($val[1][0])) &&  strtotime($el[$k])<=strtotime(trim($val[1][1]))) $rel+=$val[2];
							} else {
								if($el[$k]>=trim($val[1][0]) &&  $el[$k]<=trim($val[1][1])) $rel+=$val[2];
							}
							break;
						
					}		
				}	
			}
			$this->return[(string)$rel][]=$el;
		}
	}
	
}
/**
 * Search
 * 
 */
class Search extends RelevantSearch {
	
	public $tableData=array();
	public $linked_to_table=array();
	/**
	 * Set DB connect params and tables info 
	 *
	 * @param array $dbopt - db params
	 * @param array $tables - array of tables information
	 */
	public function __construct($dbopt,$tables) {
		parent::__construct($dbopt);
		$this->tableData=$tables;
	}
	/**
	 * Join the table to the search
	 *
	 * @param string $table - table name with linking table like "table_linked_to.table"
	 */
	public function Join($table) {
		if(eregi("\.",$table)){
			$tabls=explode(".",$table);
			$this->addTable($this->tableData[$tabls[1]]['name'],$tabls[1]);
			if(strlen($tabls[0])>0) {	
				$this->linked_to_table[$tabls[1]]=$tabls[0];
			}
		} else {
			$this->addTable($this->tableData[$table]['name'],$table);
		}
		
	}
	/**
	 * Run search query
	 *
	 * @return array - sorted array of data sorted by relevant desc
	 */
	public function find() {
		foreach($this->addedTables as $table) {
			$this->addSelect($table,$this->tableData[$table]['out']);
			if(array_key_exists($table,$this->linked_to_table)) {
				$this->addLink(
				$table,
				$this->tableData[$table]["links"][$this->linked_to_table[$table]]['local'],
				$this->linked_to_table[$table],
				$this->tableData[$table]["links"][$this->linked_to_table[$table]]['foreign']
				);
			}
		}
		
		return $this->make();
	}
	
	
	
	
}
Return current item: Relevant Search in MySQL