Creating search queries

[DoD]

I'm currently making a search section for a script i'm making... but my problem is getting a good code to process the user's query.

For example, i want to make it, so when the user searches, say "apples OR bananas" i can create the query from that which i can just slot into an SQL query - basically to turn the user query into:

WHERE comes before this...

(field LIKE "%apples%" OR field LIKE "%bananas%")

and so on, including converting "AND"

Also, possibly for it to retain the brackets the user sets to group the or / and queries.

I dont know how to go about this... and any help would be greatly appreciated!!! 😃

[paulnaj]

I don't know a definitive solution, but you might look at using
REGEXP instead of LIKE, at least for a set of OR's for a particular field.

For example, you could something like ...

WHERE field REGEXP '(apples|bananas|grapes)'

If all the choices were in a PHP array then you could use
the join() function ...

$fruit  = array('apples', 'bananas', 'grapes');
$sql = "
SELECT *
FROM table
WHERE field REGEXP '(".join('|', $fruit ).")'
";

[DoD]

Yes... this is much better!

My only problem now is how to do an AND in it, and a not?

[paulnaj]

I think you're in quite tricky territory ... a completely general SQL builder would need to be quite a sophisticated object.

Just look at the some of the possibilities for just three items:
A and B and C
(A and 😎 or C
A and (B or C)
(A and C) or B
B and (A or C)
... etc.

I had a flick round the forum for similar threads but couldn't find any real solutions.

Maybe have a look in the sample scripts!

[DoD]

Hmmm yes... i've decided to make it simpler... alot! And basically its now either search all words entered, or any of them. Seperate words by a space... and i'm using implode on an array to create the query...

seems to work though! 😉