Boolean String To SQL

[scousethemoose]

Hi everyone,

Hopefully someone may be able to help me with this. I'm trying to write a function that will take the following boolean string and convert it to the SQL equivalent.

eg: "Indie OR Soul AND Metal AND Punk AND NOT HipHop"

I am using full text indexing and need to match across columns.

So my query would look like this:

SELECT id FROM table WHERE activated = 1 AND Contains((column1,column2),"Indie") OR Contains((column1,column2),"Soul") AND Contains((column1,column2),"Metal") AND Contains((column1,column2),"punk") AND NOT Contains(column1,column2,"HipHop")

Does anyone know of an example I could look at? I have a feeling I'm going to have to brush up on regular expressions.

Any help would be greatly appreciated, thanks.

 

[Sheco]

I believe the default operator precedence rules will cause the orginal statement to be evaluated as follows:[tt]
(((Indie OR Soul) AND Metal) AND Punk) AND (NOT HipHop)
[/tt]

So the only two possible results could be:[tt]
Indie, Metal, Punk[/tt]
or
[tt]Soul, Metal Punk[/tt]


Now I'm not sure exactly the goal to accomplish, but by the looks of the SQL query, there are only 2 columns...

... so working backwards from the idea that there are only 2 columns, my best guess is that the original logical statement needs to be restructured using Parenthesis to overcome the default rules of operator precedence.

 

[scousethemoose]

Thanks for the clarification concerning parenthesis and operator precedence. So essentially if the function is passed.

(((Indie OR Soul) AND Metal) AND Punk) AND (NOT HipHop)

It should return:

SELECT id FROM table WHERE activated = 1 AND (((Contains((column1,column2),"Indie") OR Contains((column1,column2),"Soul")) AND Contains((column1,column2),"Metal")) AND Contains((column1,column2),"punk")) AND (NOT Contains(column1,column2,"HipHop"))

Which will override the default operator precedence rules.

Although ideally I'd like the function to add parenthesis without the user having to.

With regards to a function to accomplish this, I somehow need to split the passed string into keywords and operators while maintaining the relationship between them so that I can then convert it to an sql statement.

This is why I originally thought that I will have to use regular expressions to extract the desired pattern.

I have found a php example that basically accomplishes what I want but I can't seem to get my head around the logic.

http://davidaltherr.net/web/php_functions/boolean/funcs.mysql.boolean.txt