Join/Combine fields for a single search?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
NelsonN
Forum Newbie
Posts: 3
Joined: Tue May 13, 2003 5:29 pm

Join/Combine fields for a single search?

Post 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
Gleeb
Forum Commoner
Posts: 87
Joined: Tue May 13, 2003 7:01 am
Location: UK
Contact:

Re: Join/Combine fields for a single search?

Post 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.
NelsonN
Forum Newbie
Posts: 3
Joined: Tue May 13, 2003 5:29 pm

Re: Join/Combine fields for a single search?

Post 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.
NelsonN
Forum Newbie
Posts: 3
Joined: Tue May 13, 2003 5:29 pm

Re: Join/Combine fields for a single search?

Post 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.
Post Reply