Eli Fulkerson .com HomeProjectsBoolean-query
 

PHP (or ASP) Boolean to SQL Query Translator

Description:

This is a set of PHP functions that convert boolean syntax (such as "dogs" and "cats" not "mammoth") into SQL statements that can be run as a database query. I didn't do much in the way of sanitization here, any programmer using this is expected to sanitize his input before feeding it to this function. No excuses.

Update: This script has now been translated to work under ASP as well. Thanks to the user who goaded me into finally getting that done, I had intended to it years ago. The ASP version hasn't been tested as thoroughly as the PHP version, but appears to be outputting identical output for a given input. Source is at the bottom of the page.

Note: This code is very, very old at this point. Its from around 2002, possibly slightly earlier. Nowadays I would strongly recommend that if you are going to use this that you modify it to use PDO rather than the old game of 'sanitizing input' and building queries out of user input. I will be doing it myself if I ever need to use this code again, but for now it is what it is. As a related note: if you are using a single database user for both your read-only and read-write queries, I don't want to use your software.

Usage:

bq_simple ($return_fields, $tables, $check_fields, $query_text);

"$return_fields" is a space delineated list of the fields you would like the query to return

"$tables" is a space delineated list of the tables names to search

"$check_fields" is a space delineated list of the fields within those tables to search for the arguments in

"$query_text" is the boolean query itself (what the user typed in the search box)

The return value is a full SQL statement that can then be run against the database.

Example:

called as:

bq_simple("hat cat mat", "seuss", "hat cat", "sam +i +am or \"I do not like them in a house\"");

returns:

SELECT hat, cat, mat FROM seuss WHERE (hat LIKE '%sam%' OR cat LIKE '%sam%') AND (hat LIKE '%i%' OR cat LIKE '%i%') AND (hat LIKE '%am%' OR cat LIKE '%am%') OR (hat LIKE '%i do not like them in a house%' OR cat LIKE '%i do not like them in a house%');SELECT hat, cat, mat FROM seuss WHERE (hat LIKE '%sam%' OR cat LIKE '%sam%') AND (hat LIKE '%i%' OR cat LIKE '%i%') AND (hat LIKE '%am%' OR cat LIKE '%am%') OR (hat LIKE '%i do not like them in a house%' OR cat LIKE '%i do not like them in a house%');

Platform:

  • Any platform that will run PHP/SQL
  • Update: or ASP
  • License:

  • This code is free for non-commercial use. Anything else, please contact me.
  • Code:

    Source code without formatting

    Source code, ASP version.