Search functions are both essential and significant for websites. You need to give visitors abilities to search for products or articles by typing keywords. Visitors enter one or multiple keywords in the search box, and you need to develop an SQL query to handle the searches. The ways of constructing the SQL query in MySQL will affect the keyword searches. To better perform keyword search functions, there are various SQL query techniques involved.
Exact Search
For exact keyword searches, you need to create queries using simple where conditions: Select * from sales Where MATCH(title, body) AGAINST (PHP)
You also need to remove the blank space from left and right of the search string via Itrim and rtrim functions:
$search_text=ltrim($search_text);
$search_text=rtrim($search_text);
Any Where Match
For any where match, you need to read the search term and break it into array of keywords using split command. Then search through each term. The PHP code for this function is as follows:
$keyword=split(" ",$search_text);
while(list($key,$val)=each($keyword))
if($val<>" " and strlen($val) > 0)$q .= " name like '%$val%' or ";
Advanced Boolean Searching
Boolean Search enables users to narrow their results via using Boolean operators, such as AND, OR, XOR and other operators. To use Boolean mode, you should add IN BOOLEAN MODE to the end of the SQL query: Select * from sales Where MATCH(title, body) AGAINST (PHP IN BOOLEAN MODE)
You can even build more advanced search regarding keyword PHP via plus or minus sign:
Select * from sales Where MATCH(title, body) AGAINST (+PHP-MySQL IN BOOLEAN MODE)
Here the plus sign shows that this word must be present in every returned text, whereas the minus sign indicates that this word shouldnt appear in the returned text.
Query Expansion Searches
A query expansion search is a modification of a natural language search. The search string is used to perform a natural language search. The words returned by the search are added to the search string and the search is conducted again. The query returns the results from the second search. To create query expansion search, you need to add WITH QUERY EXPANSION or IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION to the search statements:
Select * from sales Where MATCH(title, body) AGAINST (PHP WITH QUERY EXPANSION)
