Mother of All SQL statements


SELECT ``. * , `regions`.`regionname` ,
MATCH (
``.name_first, ``.name_last, ``.address1, ``.address2
)
AGAINST (
' runner '
) AS relevance
FROM `regions`
INNER JOIN `` ON ( `regions`.`id` = ``.`region_id` )
WHERE (
(
``.businessflag = 0 AND ``.name_last
LIKE 'runner'
)
) OR (
(
``.businessflag = 1
) AND
MATCH (
``.name_first, ``.name_last, ``.address1, ``.address2
)
AGAINST (
'runner'
) > 0
)
HAVING relevance > 0
ORDER BY relevance DESC

OK, maybe not the mother of all SQL statements, but maybe the PHP will convince you otherwise:

< ?php
class myclass () {
	function fGetNumbersBySearch($search, $region_id, $type_id, $start = "", $amount = "") {
		require_once('funcs.mysql.boolean.php');
		$fulltext_key = "`".$this->table_numbers."`.name_first, `".$this->table_numbers."`.name_last, `".$this->table_numbers."`.address1, `".$this->table_numbers."`.address2"; // 
		$sql =  "SELECT `".$this->table_numbers."`.*, `".$this->table_regions."`.`regionname`, \n"
		.boolean_sql_select(
			boolean_inclusive_atoms($search),
			$fulltext_key)." as relevance \n"
		."FROM `".$this->table_regions."`"
		."INNER JOIN `".$this->table_numbers."` ON (`".$this->table_regions."`.`id` = "
		."`".$this->table_numbers."`.`region_id`)"
		."WHERE \n";
		if ($region_id != 1 && ($region_id != "" || !isset($region_id) ) ) {
			$sql .= "((`".$this->table_numbers."`.region_id = ".$region_id.") AND (";
		}
		if ($type_id == 2 || $type_id == "" || !isset($type_id)) {
			$a = explode (" ", $search);
			$sql .= "(";
			foreach ($a as $key=>$value) {
				if ($key >0) { $sql .= " OR "; }
				$sql .= "( `".$this->table_numbers."`.businessflag = 0 AND `".$this->table_numbers."`.name_last LIKE '".$value."' ) \n";
			}
			$sql .= ")\n";
		}
		if ($type_id == "" ) {
			$sql .= " OR \n";
		}
		if ($type_id == 1 || $type_id == "" || !isset($type_id)) {
			$sql .= "((`".$this->table_numbers."`.businessflag = 1) AND \n";
			$sql .= boolean_sql_where($search,$fulltext_key).") \n";
		}
		if ($region_id != 1 && ($region_id != "" || !isset($region_id) ) ) {
			$sql .= ") )";
		}
		$sql .= "HAVING relevance > 0 ORDER BY relevance DESC \n";
		if ($start != "") {
			$sql .= "LIMIT ".$start;
			if ($amount != "") {
				$sql .= ", ".$amount;
			}
		}
		echo "<font face='verdana'><small><small>".nl2br($sql)."<br /></small></small></font>";
		$result = $this->db->Execute($sql);
		if ($result === false) {
			die($this->db->ErrorMsg());
		} else {
			return $result;
		}
	}
?>

I’m using some functions included, available at http://davidaltherr.net/web/php_functions/boolean/article.mysql.boolean.htm .