PHP MYSQL搜索多个单词多列

I need to query a MYSQL database with words entered from a form. I need to search two columns of the database with all the words entered into the search form. Column one is a recordnumber and column two is a description. When searching on these two columns I need the recordnumber to be searched fuzzy (%t%) and the description searched with an exact match. I need the query to return all records that have all these words present in either column.

So if I search for C-101 the query will return any record with C-101 in one of the two columns. If I search for C-101 and Screw it will return all records with both of those present in either column.

My code right now that does not work properly:

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

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

$sql = "SELECT * FROM drawrecord WHERE (recordnumber LIKE $text) OR (description LIKE $text2)";

With this code if I search for C-101 and Screw it will return all records with C-101 or Screw in it. That is all records with C-101 in the first column, all records with Screw in the second column and all records with C-101 in the first column and Screw in the second column.

I need it to return all the rows with C-101 and Screw present together in the same row.

This needs to work if someone enters two three or four words. Typically they are entering only two.

Thanks for any help.

You have an error in your sql..

$sql = "SELECT * FROM drawrecord WHERE (recordnumber LIKE $text) OR (description LIKE $text2)";

Try this one..

$sql = "SELECT * FROM drawrecord WHERE (recordnumber LIKE ".$text.") OR (description LIKE ".$text2.")";

Hope this help.. :D

created an example for you, check if it works for you,

<?php

$formdata = "string1 string2 string3 string4 string5";

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

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

echo $sql = "SELECT * FROM drawrecord WHERE (recordnumber LIKE ".$text.") OR (description LIKE ".$text2.")";

?>

Using full text search you can achieve this

$formdata = "string1 string2 string3 string4 string5";

$search_string = "+".str_replace(" "," +",$formdata);
//string will be become "+string1 +string2 +string3 +string4 +string5"

$sql = mysql_query("SELECT * FROM drawrecord WHERE  
                     MATCH ( recordnumber, description ) 
                     AGAINST ('".$search_string ."' IN BOOLEAN MODE);");