new search function

[jpr]

I cant seem to be able to build a SMART search script, can anyone help please? I have one table, with two columns that I am searching: title, publication. The search word(s) are under varible $keywords. I made one but its not smart, it gets confused with more than one word. I would greatly appreciate your help.

Jason Rottman

[ednark]

punchline you need to use php to construct a giant complex sql statement.. something like my example below could be extended to do something like you want (i think)

1) parse search box into array of AND strings and an array of OR strings... this could be a complex regeular expression of some kind...

2) step through each array to construct this sql statement below or something similar...

SELECT
    *
FROM
    TABLE
WHERE
     (
        /* AND partial word match */
        column LIKE '%$word1%'
        AND
        column LIKE '%$word2%'
        AND 
        column LIKE '%$word3%'

 ) OR (

    /* OR partial word match */
    column LIKE '%$word4%'
    OR
    column LIKE '%$word5%'
    OR
    column LIKE '%$word6%'

 )  

[jpr]

Thanx! Your script does look like it works better. One question though, is it still better to explode the search phrase into seperate words and search each individual word, or search the whole phrase once by itself. What Ive been doing it using a loop, and running the search for each individual word. Thanx!

Jason Rottman

[ednark]

ok you solution works... so its not that wrong... however there is much room for a speed improvement if you don't use multiple queries...

if running a seperate search for each word is just a bit expensive and unnecessary... you can have your loop build the statement instead of running a sql statement each loop...

i think the standard is... "a multi-word phrase" should be interpretted as an item unto itself... and anything outside of quotes should be broken up word by word... is that what you were asking....

WHERE
description LIKE '%a multi-word phrase%'
OR
description LIKE '%more%'
OR
description LIKE '%stuff%'