Help with MySQL query

[someone]

Hi,

I need a search that allows multiple words. I got started on the following by code others posted here before.

$search = 'S 1';

$searcha = Array();
$searcha = explode(" ", $search);
$query = "select * from Story where 1=1 ";
reset($searcha);
while (list($key, $val) = each($searcha)) {$query.= "and Description LIKE '%$val%'";
}

Description is a field in my table that contains the description of the story and the story number. ("This story is about garden planting, S 18")

Basically, the code works fine. The thing is when I search for "S 1" (story number 1), because of the "LIKE '%$val%'" clause, the query will basically return everything that starts with a "S" and everything that starts with an "1". So even though I search for "S 1", "S 18" will come up as well.

Anyway to work around this? Thanks.

[ironik]

MySQL supports regular expressions... I think your type of query might make a good candidate:

$search = 'S 1';
$query = 'SELECT something FROM myTable WHERE description REGEXP \'\b' . $search . '\b\'';

Read more:
[dev.mysql.com...]

This is only a way to search using your existing database structure, it would be much better/faster to add extra fields that hold the S and 1 values.

[someone]

I got the following error when from the following code. Can someone tell me what's wrong?

Parse error: parse error, unexpected '[', expecting T_STRING or T_VARIABLE or T_NUM_STRING in /nfs/disk/data/www/bus/class/testing.php on line 77

$search = 'S 1';  
$searcha = Array();  
$searcha = explode(" ", $search);  
$query = "select * from Story where 1=1";  
reset($searcha);  
while (list($key, $val) = each($searcha))  
{ 
 $query.= " and Description REGEXP '[[:<:]]$val[[:>:]]'"; 
}  
$query_result = mysql_query($query); 
$num_results = mysql_num_rows($query_result); 
print $num_results; 

[someone]

Just found out that I have to separate the query like this in order for it to work.

And thanks ironik for your suggestion, the search results are so much better now.

while (list($key, $val) = each($searcha))
{
$query.= " and Description REGEXP '[[:<:]]";
$query.= "$val";
$query.= "[[:>:]]'";
}