How to query with a space ' ' in the LIKE clause

[henryrhenryr]

Perhaps this should be in the PHP forum but I'll try here first...

I'm trying to search some fields for complete words. The word can be in the middle of a string of text. My solution (perhaps there is a better one) is to use the following query:

[CODE=sql]
SELECT id, string FROM string_table WHERE string LIKE ('%This is my very long test string%') OR string LIKE ('% This %') OR string LIKE ('% very %') OR long LIKE ('% long %') OR test LIKE ('% test %') OR string LIKE ('% string %');
[/CODE]

This query actually works when I use MySQL Query Browser (or command line). You can see that each LIKE has a single word with spaces around. I don't want to match 'test' to attested' for example.

My problem arises when I send this query using PHP. I use something like this:

[CODE=php]
$output=array() ;
$query= "SELECT id, string FROM string_table WHERE string LIKE ('%This is my very long test string%') OR string LIKE ('% This %') OR string LIKE ('% very %') OR long LIKE ('% long %') OR test LIKE ('% test %') OR string LIKE ('% string %')";
$result= mysql_result($q uery,$link);
if ($result && mysql_num_rows( $result)>0) {
while ($row=mysql_fet ch_array($resul t)) {
$output=$row;
}
}
var_dump($outpu t);
[/CODE]
$output is an empty array. No results are returned so mysql_num_rows( )=0.

I think it's something to do with character encoding but I'm not really familiar with how to get PHP to send the query in the correct format so that the spaces are retained.

Can anyone help me?

Thanks!

[mwasif]

What does mysql_num_rows( ) return? Use var_dump($row) inside while() to see the output.

[pbmods]

Heya, Henry.

In your PHP, you use $output ->=<- $row instead of $output ->.=<- $row. Is this intentional?

You may want to use MySQL's fulltext searching instead (what about ' test.' or '-test ' or '-test.'?).

[henryrhenryr]

Hi!

Thanks for your replies - sorry it's taken so long to respond! Trying to juggle too many things!

The $output=$row is a typo. Should read $output[$row['id']]=$row; - I record each row as a 'sub-array' with the primary key as the array key.

I tried outputing the results as the while loop looped. Hasn't revealed much. The results are output row by row but they are just the results that were displayed by my script anyway.

The problem is rooted in the fact that searching on something like MySQL query browser gives me the correct result, while using my script and the same query gives me different results?

[mwasif]

The problem is rooted in the fact that searching on something like MySQL query browser gives me the correct result, while using my script and the same query gives me different results?

This is not possible. Make sure you are connecting to correct database and table. Echo the the resultant query in PHP and execute it in MySQL to compare the results.