Well, after banging my head against the wall for a while, and thinking
this will require a regular expression or a few (which are mindbending
to me) I thought I'd see if someone here might be able to whip
something up for this.

We have a search form and we would like to allow people to enter
boolean terms to help them narrow their search.  So, for example the
code I'm looking for would take something like this:

$_POST[thefieldtosearch]
  would have "Running OR Walking AND Tall AND Happy NOT short"

The code needs to watch for " OR ", " AND ", " NOT " and my example
here should end up as SQL such as this:

$sql = "SELECT * from mydb where thefieldtosearch LIKE '%Running%' OR
thefieldtosearch LIKE '%Walking%' AND thefieldtosearch LIKE '%Tall%'
AND thefieldtosearch LIKE '%Happy%' NOT thefieldtosearch LIKE
'%short%'";

I'm sure that's not exactly correct syntax, and the query would likely
need to be built piece by piece as the original string is being broken
down..but that's an example of the final query as seen by the db.

---

Clarification of Question by trancecan-ga on 02 Jun 2006 13:55 PDT

P.S.  The answer should also be "SQL Injection" SAFE

THANKS!

trancecan-ga,

I have written an include file so you can easily add this
functionality to your code:

www.maxlin.ca/boolean-query.zip

To use, simply copy the file into the directory of the script you want
to use it, and add at the beginning of your file:

<? include ("boolean-query.php"); ?>

To use the function, call:

boolstring2sql (<return fields>, <search tables>, <search fields>, <search string>)

For example:
echo boolstring2sql ("*","mydb","thefieldtosearch", "Running OR
Walking AND Tall AND Happy NOT short");

Returns:
SELECT * FROM mydb WHERE (thefieldtosearch LIKE '%running%') OR
(thefieldtosearch LIKE '%walking%') AND (thefieldtosearch LIKE
'%tall%') AND (thefieldtosearch LIKE '%happy%') AND (NOT
thefieldtosearch LIKE '%short%');

If you only want the WHERE portion, use:

boolstring2sql_query (<search fields>, <search string>)

For example:

boolstring2sql_query ("thefieldtosearch", "Running OR Walking AND Tall
AND Happy NOT short")

Returns:

(thefieldtosearch LIKE '%running%') OR (thefieldtosearch LIKE
'%walking%') AND (thefieldtosearch LIKE '%tall%') AND
(thefieldtosearch LIKE '%happy%') AND (NOT thefieldtosearch LIKE
'%short%')

To specify multiple fields, use space delimiting. For example:

boolstring2sql ("*","mydb","firstfield secondfield thirdfield",
"Running OR Walking AND Tall AND Happy NOT short");

Returns:

SELECT * FROM mydb WHERE (firstfield LIKE '%running%' OR secondfield
LIKE '%running%' OR thirdfield LIKE '%running%') OR (firstfield LIKE
'%walking%' OR secondfield LIKE '%walking%' OR thirdfield LIKE
'%walking%') AND (firstfield LIKE '%tall%' OR secondfield LIKE
'%tall%' OR thirdfield LIKE '%tall%') AND (firstfield LIKE '%happy%'
OR secondfield LIKE '%happy%' OR thirdfield LIKE '%happy%') AND (NOT
firstfield LIKE '%short%' OR secondfield LIKE '%short%' OR thirdfield
LIKE '%short%');

It should be noted that the search string is guarded against SQL injection.


Enjoy,

tox-ga

---

Request for Answer Clarification by trancecan-ga on 05 Jun 2006 11:38 PDT

Hi again!

We stumbled into a "bug", maybe not so much a bug as something I might
not have clarified.  When doing a search such as:

training AND (cat OR dog)

We end up with this sql:

...WHERE (objective LIKE '%training%') AND (objective LIKE '%(cat%')
OR (objective LIKE '%dog)%')

Which is ALMOST correct, but it's not stripping out the original () in
the query.  '%(cat%')  should be '%cat%'

I am not SQL wiz, so I'm not sure what the exact correct sql is for
that, I've been running some queries and both of these seem to give me
the same results:

..WHERE (objective LIKE '%training%') AND ((objective LIKE '%cat%') OR
(objective LIKE '%dog%')) ...
..WHERE (objective LIKE '%training%') AND (objective LIKE '%cat%') OR
(objective LIKE '%dog%') ...

That could just be a fluke because of precedence.  Is suspect you will
know which way is truly correct.  Can you make this change?  If it's a
huge issue to rewrite for the () around the whole OR, if we can just
get the ( and ) removed from the actual query terms that would be
great.  It is safe to assume that any ( ) in the search terms is meant
to dictate the precedence.  There is no need for anyone to actually be
searching for a ( or ) in their terms.

Is this quickly/easily do-able?  Am I asking too much of you?

Very good answer, I had to make a minor tweak to get it working for
"NOT" queries.  Line 41 of boolean-query.php I had to change $front =
"(NOT ";  to $front = "NOT (";
Otherwise seems to work great!!  THANKS!!