Greetings.

After searching through some old posts, I came across a script that would allow for multiple word searches in a MySQL database (i.e. $word1 blah blah blah blah $word2 blah $word3) but I'm having trouble modifying it so that it will search more than one field within a table. The code in question, which was originally posted by Mega can be seen in it's original context here: http://www.phpbuilder.com/board/showthread.php?s=&threadid=10208147

The code itself:

$text = '';
$the_array = explode(' ', $formdata);
$i = 0;

foreach( $the_array AS $t ){
if( $i == 0 ){
$text .= " '%$t%' ";
$i = 1;
}else{
$text .= " OR field LIKE '%$t%' ";
}
}

mysql_query("SELECT * FROM table WHERE field LIKE $text");

How could that be modified to search within three fields while still executing the multiple word search within each field?

I thought of this:

$text = '';
$the_array = explode(' ', $search);
$i = 0;

foreach( $the_array AS $t ){
if( $i == 0 ){
$text .= " '%$t%' ";
$i = 1;
}else{
$text .= " OR field LIKE '%$t%' ";
}
}

$result = mysql_query("SELECT * FROM tapes 
WHERE title LIKE $text || date LIKE $text 
|| ml LIKE $text ORDER BY 'tape_id' 
LIMIT $start,$limit");

But I think it's obvious that will result in an SQL syntax error.

Any suggestions?

Thanks a bunch in advance.

--Tony

    I personally don't think what you are trying to do is possible.

    Psudo coded your are basicall asking for:

    (assume 3 words passed from form)

    select * from table where field1 like word1 or field1 like word2 or field1 like word3 and/or field2 like word1 or field2 like word2 or field2 like word3 and/or field3 like word1 or field3 like word2 or filed3 like word3

    Even if a query like that can execute, the results would be horribly ambiguous.

    You need to re-think the way your database is structured.

      Greetings.

      I've managed to fix it, although I've come to terms with the fact that I'll only be able to search a single field. My actual purpose- a multiple word search, has been realized.

      At the same time, I've stumbled onto a way to add boolean capability without the use of a FULLTEXT index. A sacrifice made is knowledge gained, I suppose.

      None the less, thank you for your help!

      --Tony

        a month later

        I found a way of searching for multiple keywords in multiple fields in a table by amending your script as follows:

        $text = '';
        $the_array = explode(' ', $formdata);
        $i = 0;

        foreach( $the_array AS $t ){
        if( $i == 0 ){
        $text .= " '%$t%' ";
        $text2 .= " '%$t%' ";
        $i = 1;
        }else{
        $text .= " OR field1 LIKE '%$t%' ";
        $text2 .= " OR field2 LIKE '%$t%' ";
        }
        }

        $sql="SELECT * FROM Table WHERE (field1 LIKE $text) OR (field2 LIKE $text2)";

        Hope this helps

        Mayan

          Write a Reply...