Page 1 of 1

Join/Combine fields for a single search?

Posted: Tue May 13, 2003 5:29 pm
by NelsonN
Is it possible to combine fields to apply a search to all of them as though they were one whole field?

I want to search fields for the same keywords, the user has the ability to apply an AND or an OR.

Take for example the field named Name and the field named Description. I want to combine Name and Description before applying the MySQL query on them, is this possible? Or am I "barking up the wrong tree" here?

I want to avoid the following code and just be able to use one LIKE command on a combined field. It's crucial that Melissa is found in CelebName and that Profession is found in CelebProfession, or that it be found in the Description as well, if it's an AND search, or hit at least one keyword if it's an OR search. :?

SELECT * FROM celebrity WHERE (CelebName LIKE '%melissa%' OR CelebName LIKE '%actress%') AND (CelebProfession LIKE '%melissa%' OR CelebProfession LIKE '%actress%') AND (Description LIKE '%melissa%' OR Description LIKE '%actress%') AND RecordStatus = 'A' ORDER BY CelebName LIMIT 0, 10

Re: Join/Combine fields for a single search?

Posted: Wed May 14, 2003 5:52 am
by Gleeb

Code: Select all

SELECT * FROM celebrity WHERE (CelebName LIKE '%melissa%' OR CelebName LIKE '%actress%') AND (CelebProfession LIKE '%melissa%' OR CelebProfession LIKE '%actress%') AND (Description LIKE '%melissa%' OR Description LIKE '%actress%') AND RecordStatus = 'A' ORDER BY CelebName LIMIT 0, 10
I could be wrong, but try...

Code: Select all

SELECT * FROM celebrity WHERE `CelebName` + `CelebProfession` AS `CelebInfo` LIKE '%melissa%' OR `CelebInfo` LIKE '%actress%'
I could be very, very wrong.

Re: Join/Combine fields for a single search?

Posted: Wed May 14, 2003 11:22 am
by NelsonN
Thanks Gleeb for the help. Although your example didn't work I was pointed in the right direction by your post. This is what I found, I didn't know you could CONCAT fields together. :D

SELECT * FROM celebrity WHERE CONCAT(CelebName, Description) LIKE '%Melissa%'

This seems to be getting me where I want to go. Now all I need to do is use a case insentive search, so that %melissa% works on Melissa and melissa.

Re: Join/Combine fields for a single search?

Posted: Wed May 14, 2003 1:16 pm
by NelsonN
NelsonN wrote: This seems to be getting me where I want to go. Now all I need to do is use a case insentive search, so that %melissa% works on Melissa and melissa.
Found the problem, I had the Description field in the MySQL database as a blob. I changed that to text and LIKE now works as case insensitive. Blob is binary and text is not. LIKE on binary acts in case sensitive fashion.

My problem is solved, just thought I make this clear for the next person trying to figure something like this out.