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. 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 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 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 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 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. 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
Archived
This topic is now archived and is closed to further replies.