[SOLVED] Select statement with LIKE and wildcards

[DavidP123]

Hi All,

 

I've come across an issue with my sql statment. The following statement returns two records:

 

SELECT `user`.`id` FROM `user` 
INNER JOIN `contact_details` ON `user`.`contact_details_id` = `contact_details`.`id` 
WHERE ((`user`.`username` LIKE '%dav%')
OR (`contact_details`.`business_name` LIKE '%dav%')
OR ((`contact_details`.`firstnames` + ' ' + `contact_details`.`lastname`) LIKE '%dav%')
OR (`contact_details`.`address` LIKE '%dav%') 
OR (`contact_details`.`email` LIKE '%dav%') 
OR (`contact_details`.`postcode` LIKE '%dav%'))
ORDER BY `contact_details`.`lastname` LIMIT 0, 10

 

 

if however I change this section:

WHERE ((`user`.`username` LIKE '%dav%')

    to

WHERE ((`user`.`username` LIKE '%david%')

 

the statement only returns 1 record, even though my second record fits some of the OR clauses.

 

If I move any of the other OR sections to be directly after the WHERE clause the same problem happens with those also.

 

Would someone be kind enough to explain what I am doing wrong??, and how I might go about altering it so that it shows the two records.

 

The mysql server version is: 4.1.22

 

Regards,

 

David.

[kickstart]

Hi

 

I can see nothing obviously wrong (although unless you wildcard can contain spaces it seems pointless to concatenate 2 fields together with a space in the middle and then check the contents).

 

Can you post the 2 records that are returned at first, and which is not returned afterwards.

 

All the best

 

Keith

[DavidP123]

Hi,

 

I've opened the query up a little to bring back the username as well (to help with the identification of which record is actually being pulled back. The results of the original query (wildcard search being: dav) are:

 

username    id

dave2        42

dave          41

 

after altering to show 'david':

 

username    id

dave          41

[kickstart]

Hi

 

What are the values of business_name, firstnames, lastname, address, email and postcode? Which of those should it have matched on?

 

All the best

 

Keith

[DavidP123]

it strikes me that it should have matched on the firstnames + ' ' + lastname section.

 

I have expanded the query further to give me the values as you suggest, the concatination returns the value 0 rather than the name,

 

I used CONCAT( `contact_details`.`firstnames` , ' ', `contact_details`.`lastname` ) instead and it seems to be working, I'll give it a proper test over the next few minutes and then post back with the results

[DavidP123]

Worked a treat.

 

Thanks for your help Keith.

 

David.