Search Query to SQL Where Clause PHP Classes
IntroductionI 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() usagegetSQL($varnames, $join, $compop, $pre, $post)
varnames - array of column names in the table you want to search
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 outputResulting $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 MethodssetQuery($querystring) - sets the query stringgetQuery() - 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 NotesIn 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 |