Search Query to SQL Where Clause PHP Classes

Introduction

I wrote several php classes to handle query string processing, to turn a user-entered query string into an SQL where clause. By creating an object with one of two classes, the query string can be treated as either a keyword query string (stopwords will be ignored) or a boolean query string (parentheses and "and", "or", and "not" can be used to create a boolean query).

In both the keyword and boolean query strings, single and double quotes can be used to delimit phrases. The wildcard character is *. If the comparison operator used is "like", all *'s are converted to % and if the comparison operator is "rlike" or "regexp", all *'s are converted to .*. All other punctuation is treated literally.

To use either KeywordToSQL.php or BooleanToSQL.php in a script, include them along with Token.php. The most important method is getSQL(), which returns the SQL where clause.


getSQL() usage

getSQL($varnames, $join, $compop, $pre, $post)

varnames - array of column names in the table you want to search
join - string, how to join each of the varnames together
compop - string, mysql comparison operator
pre - string to preceed each token in the comparison clauses
post - string to follow each token in the comparison clauses

getSQL() returns the string "0" if it isn't able to process the query string. (It will fail if quotes don't match or the logic is wrong in the query string.)


Sample usage:

require "BooleanToSQL.php";

...

$querystring = "cats or dog* and (black not 'dark brown')";

$btosql = new BooleanToSQL($querystring);
$varnames = array("title", "notes");
$whereclause = $btosql->getSQL($varnames, "or", "regexp", "[[:<:]]", "[[:>:]]");

$query = "select * from table where $whereclause order by title";


Sample output

Resulting $query from sample above:

select * from table where (( title regexp '[[:<:]]cats[[:>:]]') or (( title regexp '[[:<:]]dog.*[[:>:]]') and (( title regexp '[[:<:]]black[[:>:]]') and not ( title regexp '[[:<:]]dark brown[[:>:]]') ) ) ) or (( notes regexp '[[:<:]]cats[[:>:]]') or (( notes regexp '[[:<:]]dog.*[[:>:]]') and (( notes regexp '[[:<:]]black[[:>:]]') and not ( notes regexp '[[:<:]]dark brown[[:>:]]') ) ) ) order by title


Other Class Methods

setQuery($querystring) - sets the query string
getQuery() - returns the query string
getCount() - returns the number of tokens found in the query string (so if this returns 0, all the words were stopwords)


Other Notes

In KeywordToSQL.php, a file of stopwords is read and the words included are ignored in the query string. The default file is currently stopwords.txt; edit the variable $stopfile in KeywordToSQL.php to change it.


Files

(save the .phps files as text with the extension .php)

BooleanToSQL.phps
KeywordToSQL.phps
Token.phps
Sample stopwords.txt file

php-queryclasses.tar.gz (all files)

Last updated: 08/29/2005 by Adriane Boyd