Search Object
Item 8 of 9
1 <? 2 3 /*==================================================================================================================== 4 5 Title: class_SearchObject.inc.php 6 Description: A class for handling database searches from a free text query. Can search multiple fields on multiple 7 tables (if desired). Queries are simple and structured like natural language, i.e.: 8 one AND two OR three AND for IN general forum 9 10 Publisher: act e-learning (www.actelearning.com) 11 Creator: Dan Pupius (www.pupius.co.uk) 12 Date: 2003-09-11 13 Rights: Copyright (c)2003 Act E-learning 14 15 16 Example use: — — — — — — $query = "\"flying sheep\" OR \"magic books\" frogs IN main forum BY dan ORDERBY date"; 17 $search_sql = new SearchQuery($query); 18 $db->query($search_sql->sql); 19 .... rest of your sql code .... 20 21 22 the returned sql is: 23 SELECT f.title,m.author,m.posttype,m.id,m.title,m.body,m.locked,m.status,m.created 24 FROM messages as m, forum as f 25 WHERE message.forum = forum.id AND 26 f.title LIKE 'main forum' AND 27 m.author LIKE 'dan' AND ( 28 ( 29 ( 30 m.title LIKE '%flying sheep%' OR m.body LIKE '%flying sheep%' 31 ) 32 ) OR ( 33 ( 34 m.title LIKE '%magic books%' OR m.body LIKE '%magic books%' 35 ) AND ( 36 m.title LIKE '%frogs%' OR m.body LIKE '%frogs%' 37 ) 38 ) 39 ) 40 ORDER BY date 41 42 43 As you can see the AND operator is default while the OR operator takes prescidence. This means that queries are 44 more akin to natural language. Brackets are not supported so "(a OR b) AND c" would need to be written as 45 "a AND c OR b AND c", for programmers this is less intuative, but it's easier for normal people... I promise ;) 46 47 48 49 NOTE: you must configure the $config array in the SearchQuery object to work with the fields and tables in your 50 database. 51 52 The main search occurs in the so called "normalfields" while the "specialfields" lock down the search, with 53 the above example "IN main forum" means the search will only return rows where the field corresponding to IN 54 equals "main forum", likewise "BY dan" means the rows must have dan in the field corresponding to "BY" in the 55 specialfields array. 56 57 Obviously if you want to use the SearchObject to perform a number of different type of searches you can rewrite 58 the config array on the fly instead of hard coding in this file. 59 60 Special mention about the ORDERBY specialfield which has it's behaviour hard coded and sets the "ORDER BY" field 61 in the SQL. 62 63 64 ======================================================================================================================*/ 65 66 67 class SearchQuery { 68 69 //SearchQuery config 70 var $config = array( "normalfields" => array("name","url","description","keyword"), //fields to do the normal text search in 71 "specialfields"=> array("IN" => "project", //optional fields that must be satisfied (i.e. 'IN xxx' would mean add an AND project='xxx') 72 "BY" => "who", 73 "IS" => "type", 74 "HASID" => "id"), //ORDERBY is not a specialfield as such as its behaviour is hard coded 75 "extrafields"=> array("size","length","screen","uploaded","parent"), //extra fields that are returned by the query but not searched in 76 77 "fromdatabase"=> "asset", //the FROM clause in the SQL, can refer to multiple tables (but if you do, then fix the field names above) 78 "extrawhere"=> "" //prepended to WHERE clause 79 ); 80 81 82 var $rawquery; 83 var $fixedquery; 84 var $processedquery; 85 86 //all the specialterms MUST be satisified AND at least one of the normalterms (if there are any) 87 //for a normal term to be satisified, all their ->values must be satisified. 88 var $normalterms = array(); 89 var $specialterms = array(); 90 91 var $sql; 92 93 94 function searchQuery($query) { 95 96 $this->rawquery = $query; 97 98 //strip out illegal characters from the search 99 $this->fixedquery = preg_replace("/([^\w\s^\"\*]+)/","",$query); 100 101 102 //remove any "AND OR", "AND AND", "OR AND" or "OR OR"s and leave the first occurance instead 103 $this->fixedquery = preg_replace("/(OR|AND)\s+(OR|AND)/","\\1",$this->fixedquery); 104 105 //process the query into this object, ready for processing 106 //=================================================================== 107 108 //remove special search terms from the end of our query and store them in a seperate array 109 $this->processedquery = preg_replace("/(".implode_keys("|",$this->config["specialfields"])."|ORDERBY)\s+(.*)/e","\$this->add_special_term(\"\\1\",\"\\2\")",$this->fixedquery); 110 $this->processedquery = trim($this->processedquery); 111 112 $this->processedquery = preg_replace("/\"([\w\*]*)(\s*)([\w\*]*)(\s*)([\w\*]*)(\s*)([\w\*]*)(\s*)([\w\*]*)\"/","\\1___\\3___\\5___\\6___\\7___\\9",$this->processedquery); 113 114 115 //convert AND and OR into & and | (also remove spaces) 116 $this->processedquery = preg_replace("/\s+AND\s+/","&",$this->processedquery); 117 $this->processedquery = preg_replace("/\s+OR\s+/","|",$this->processedquery); 118 119 //make & the default operator 120 $this->processedquery = preg_replace("/([\w\*]*)\s+([\w\*]*)/","\\1&\\2",$this->processedquery); 121 122 $this->processedquery = trim($this->processedquery); 123 124 if($this->processedquery!="") { 125 //explode the search string and parse into a collection of objects. All terms in an object must 126 //be satisified, and one object must be satisified for the query to match against a string 127 $normalterms = explode("|",$this->processedquery); 128 for($i=0;$i<count($normalterms);$i++) { 129 $obj = new __SearchQuery_NormalTerm; 130 $parts = explode("&",$normalterms[$i]); 131 foreach($parts as $part) $obj->add_term(trim(str_replace("___"," ",$part))); 132 $this->add_normal_term($obj); 133 } 134 } 135 136 $this->sql = $this->generate_sql(); 137 } 138 139 140 //recursivly add special search terms to the array 141 function add_special_term($name,$value) { 142 $fixedvalue = preg_replace("/(".implode_keys("|",$this->config["specialfields"])."|ORDERBY)\s+(.*)/e","\$this->add_special_term(\"\\1\",\"\\2\")",$value); 143 array_push($this->specialterms,new __SearchQuery_SpecialTerm($name,$fixedvalue)); 144 } 145 146 function add_normal_term($term) { 147 array_push($this->normalterms,$term); 148 } 149 150 151 function get_special_term_value($name) { 152 foreach($this->specialterms as $term) if($name==$term->name) return $term->value; 153 return false; 154 } 155 156 157 function generate_sql() { 158 159 $sql = "\nSELECT " . implode(",",array_merge($this->config["specialfields"],$this->config["normalfields"],$this->config["extrafields"])) . " \n" . 160 "FROM " . $this->config["fromdatabase"] . " \n". 161 "WHERE " . $this->config["extrawhere"]; 162 163 $whereparts = array(); 164 165 //create parts from special fields that are required! 166 foreach($this->config["specialfields"] as $term => $dbfield) { 167 if($this->get_special_term_value($term)) { 168 array_push($whereparts, "\n\t$dbfield LIKE '".trim($this->get_special_term_value($term))."'"); 169 } 170 } 171 172 173 //now set up the normal terms OR and ANDs 174 $normalparts = array(); 175 foreach($this->normalterms as $term) { 176 $thispart = array(); 177 foreach($term->values as $val) { 178 $micropart = array(); 179 foreach($this->config["normalfields"] as $field) { 180 array_push($micropart, "$field LIKE '%$val%'"); 181 } 182 183 array_push($thispart,"(\n\t\t\t\t".implode(" OR ",$micropart)."\n\t\t\t)"); 184 } 185 186 array_push($normalparts,"(\n\t\t\t".implode(" AND ",$thispart)."\n\t\t)"); 187 } 188 189 if(count($normalparts)>0) array_push($whereparts,"(\n\t\t".implode(" OR ",$normalparts)."\n\t)"); 190 191 if(count($whereparts)>0) $sql .= implode(" AND ",$whereparts); 192 193 194 if($this->get_special_term_value("ORDERBY")) $sql .= "\nORDER BY " . $this->get_special_term_value("ORDERBY"); 195 196 $sql = str_replace("*","%",$sql); 197 198 return $sql; 199 } 200 201 } 202 203 204 205 206 207 class __SearchQuery_SpecialTerm { 208 var $name; 209 var $value; 210 211 function __SearchQuery_SpecialTerm($name,$value) { 212 $this->name = $name; 213 $this->value = $value; 214 } 215 } 216 217 class __SearchQuery_NormalTerm { 218 var $values = array(); 219 220 function __SearchQuery_NormalTerm() { 221 } 222 223 function add_term($value) { 224 array_push($this->values,$value); 225 } 226 } 227 228 229 230 function implode_keys($glue, $array) { 231 $output = array(); 232 foreach($array as $key => $item) $output[] = $key; 233 234 return implode($glue, $output); 235 } 236 237 ?> 238
