spaces in select query causing issues

[admoore]

While using a like comparison in a select query I am unable to locate an existing exact match when the search string contains a space. I am initiating the query via a web based php script. The field being searched is collated latin1_general_ci and has a fulltext index as well.

More specifically the field contains a "full name", i.e. John Smith which would be successfully located with the search string smith or SMITH or ohn, etc., just not with John Smith, etc...

I thought the fulltext index would solve this; but, did not.

TIA,

-Allen M.

 

[r937]

the syntax for using a fulltext index is not the same as a LIKE query

can you show the query you tried? also, how many rows are in the table? fulltext indexes only start working properly when there are several hundred rows

r937.com | rudy.ca

 

[admoore]

The query used is:

$query = "select * FROM campaign_sr1 WHERE full_name LIKE CONVERT( _utf8 '%$search%' USING latin1 ) COLLATE latin1_general_ci ORDER by mail_dt DESC LIMIT 100";

There are around 20,000 records in the table...

Thanks,
-A

 

[admoore]

Following your lead I changed the query to:

$query = "select 8 FROM campaign_sr1 WHERE MATCH ($search_type) AGAINST('%$search%') ORDER by mail_dt DESC LIMIT 100";

The problem now is that if I search for "John Smith", I get all the matches for both "John" -or- Smith... Is there a way to limit the search to exact string matches, only resulting in records containing "John Smith"???

Again, thanks,

-A

 

[r937]

huh?

More specifically the field contains a "full name", i.e. John Smith which would be successfully located with the search string smith or SMITH or ohn, etc., just not with John Smith, etc...

WHERE fullname LIKE '%John Smith%'


i'm not sure if you need fulltext after all


r937.com | rudy.ca