DavidP123 Posted April 17, 2009 Share Posted April 17, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/154480-solved-select-statement-with-like-and-wildcards/ Share on other sites More sharing options...
kickstart Posted April 17, 2009 Share Posted April 17, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/154480-solved-select-statement-with-like-and-wildcards/#findComment-812245 Share on other sites More sharing options...
DavidP123 Posted April 17, 2009 Author Share Posted April 17, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/154480-solved-select-statement-with-like-and-wildcards/#findComment-812251 Share on other sites More sharing options...
kickstart Posted April 17, 2009 Share Posted April 17, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/154480-solved-select-statement-with-like-and-wildcards/#findComment-812259 Share on other sites More sharing options...
DavidP123 Posted April 17, 2009 Author Share Posted April 17, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/154480-solved-select-statement-with-like-and-wildcards/#findComment-812262 Share on other sites More sharing options...
DavidP123 Posted April 17, 2009 Author Share Posted April 17, 2009 Worked a treat. Thanks for your help Keith. David. Quote Link to comment https://forums.phpfreaks.com/topic/154480-solved-select-statement-with-like-and-wildcards/#findComment-812264 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.