Combining columns in the WHERE clause for searching

[downTime]

I'm writing a search engine for a site and have to check three different columns for the same search terms using AND/OR/NOT logic depending on the syntax of their search. I'm using LIKE for matching but I keep running into different logical traps. The easiest way to do this would be if I could combine the three columns into one and then search the combined column.

I know how to do this with an alias in the SELECT clause of the SQL statement. Is there a way to do it in the WHERE clause?

Something like:

SELECT * FROM TABLE_NAME WHERE (COLUMN1 + COLUMN2 + COLUMN3) LIKE '%search word%'

[GolferGuy]

If you would spell out completely how you want the search to work, this question would be much easier to answer. What I mean is, what are you giving to the user that wants to search, and what options are you supplying the user with, and how do you want the search term (or is it search terms?) used? How many search terms will you allow?

[downTime]

There are no limitations. There are two different searches, a regular open form search and an advanced search similar to google's advanced search. They need to check against three fields, A_TITLE, A_DESC and A_AUTHOR.

After another day of research, I don't believe there is any way to combine the columns in the SQL WHERE clause itself. I was trying to not have to either combine the three columns in a fourth column in the database and maintain that extra column every time any of the records were updated or have a very complex WHERE clause to check all the conditions.

I was already pretty close to having this done, but found a logical error with regards to multiple words using AND logic. The code I wrote to construct the SQL statement was checking each field for all the words, rather than searching all three fields for all the words. As I was falling asleep last night, I thought of a solution that I think will work for the AND portion of the WHERE clause:

Search for: word1 AND word2 AND word3

((A_TITLE LIKE '%word1%' OR A_DESC LIKE '%word1%' OR A_AUTHOR LIKE "%word1%') AND (A_TITLE LIKE '%word2%' OR A_DESC LIKE '%word2%' OR A_AUTHOR LIKE "%word2%') AND (A_TITLE LIKE '%word3%' OR A_DESC LIKE '%word3%' OR A_AUTHOR LIKE "%word3%'))

I still have to test it.

[downTime]

Test went poorly, it has a different logical problem than the first method I was trying that made me want to combine all the columns for search purposes.

I think that now what I'm looking at is something like this (no code, just the logic of the code):

check column1, 2 and 3 for all words OR

check column1 for word1, 2, OR 3 AND check column2 for word1, 2 or 3 AND check column3 for word1, 2 OR 3 OR

check column1 for word1, 2 OR 3 and check column2 for word1, 2 or 3 OR

check column2 for word1, 2 OR 3 and check column2 for word1, 2 or 3 OR

check colun1 for word1, 2 OR 3 and check column3 for word1, 2 or 3

Basically, check for any of the possible combinations that would make the result display when it should.

[downTime]

Oh and I shutter to think what will happen if in the future I have to add a fourth column.

[GolferGuy]

What was the logic problem? The code you posted looked to me like it should work. Rather than taking the time to construct the test also, it would be good to know what the logic problem was. thanks

[downTime]

The logic problem was that I want to find a match for all the search words if they appear in any combination of the three columns. The reason I posted initially was because my logic was pulling matching results only if all the words were in found in one of the columns:

word1 AND word2 AND word3 found in A_TITLE OR A_DESC OR A_AUTHOR.

This excluded results where word1 was in A_TITLE and word2 and 3 were in A_DESC as an example.

The next option I came up with (the actual code I posted above) would require that each column have at least one word, however a match should still occur if A_TITLE had none of the words, A_DESC had 2 of the words and A_AUTHOR had the other word.

I believe that without combining all three columns and searching the combined text for a match of all words, the only other method that will work would be to check for all the different possibilities of a positive match:

A_TITLE having all 3 words OR A_DESC having all 3 words OR A_AUTHOR having all 3 words OR A_TITLE having 1 word and A_DESC having another word and A_AUTHOR having the third word OR A_TITLE having 2 of the words and A_DESC having the third word and so on...

And now looking at the logic I had just written out with the multiple methods for checking, there is still a problem. It doesn't require different matches in each check, so the same word (say word_1) could trigger a match if it is in all three columns.

I'm about to give up on this as impossible.

[GolferGuy]

From the code that you posted, it really looks like it should work, as I understand the original request. In the posted code, you are making sure that all three words ARE found in any of the three columns. Because of the AND condition between the three words, all 3 must be present, but it does not have to be any any particular column. But, all 3 words must be found. What does your testing show that I missed?

[downTime]

The problem is that in order for a result to be returned, all three portions must have at least one condition be true. For example, the first condition is this:

(A_TITLE LIKE '%word1%' OR A_DESC LIKE '%word1%' OR A_AUTHOR LIKE "%word1%')

...hmmm, that looks solid. I can't find the logical problem either. I tested it and it didn't work with my sample data, though. Let me go back and look to see if my test matched that or not. Maybe I made some sort of error that didn't match that.

[GolferGuy]

That is exactly the response I had when I looked at the code you posted. I'm looking forward to seeing how the test goes.

[downTime]

In my original test I had tried to use existing code and simply change the AND to OR when building each condition, however I didn't notice that that code had each part of the condition as the same field, not the different fields. I guess in my haste I didn't notice the SQL statement wasn't correct. In the test I basically changed this (not going to spend time formating syntax properly, just logic):

A_TITLE LIKE word1 AND A_TITLE LIKE word2 AND A_TITLE LIKE word3

to this:

A_TITLE LIKE word1 OR A_TITLE LIKE word2 OR A_TITLE LIKE word3

Instead of this:

A_TITLE LIKE word1 OR A_DESC LIKE word1 OR A_AUTHOR LIKE word1

and so on...

The logic was sound, my test wasn't. I rewrote that area of the code to create the SQL statement in accordance with my falling asleep solution and it worked like a charm.

Thanks for being my sounding board and also for spotting that my solution should have worked or else I probably never would have gotten back to it and would be spending another week trying to make this work.

[GolferGuy]

You are welcome for the "sounding board." I know how valuable one is, and do enjoy being one too. Really glad it's working now, and thank you for sharing that it is working.

[downTime]

Thanks again. I always try to confirm a solution on forums like these because 2 years from now, if someone stumbles across the thread they at least know that it worked. I'd post a link to the site, but it's not ready for search engines to pick up just yet. I'll try to remember to do that once it goes live so people will have an example of what I was doing as well (even if the debugging will be off, so they won't get to see the actual SQL statement). It will at least provide an idea of the concept.

[Webenvelopment]

Two years later and that guy is me...
I can't get this to work.

SELECT * FROM beaches
WHERE title LIKE '%".$input."%' 
AND 
SELECT title FROM address WHERE LIKE '%".$input."%'

I want to return the TITLE column from a match in title or ADDRESS'S column.
Makes sense right? This way someone can search by town and keyword in the same search field. All across the internet this explanation is nonexistant but has to be possible.
Thanks guys!

[GolferGuy]

SELECT * FROM beaches
WHERE title LIKE '%".$input."%'
AND
SELECT title FROM address WHERE LIKE '%".$input."%'

It looks like you are trying to use separate SELECT statements, but what you need is separate parts within the WHERE clause.
This is his sample code that he ended up using. This code goes in the WHERE clause.
((A_TITLE LIKE '%word1%' OR A_DESC LIKE '%word1%' OR A_AUTHOR LIKE "%word1%') AND (A_TITLE LIKE '%word2%' OR A_DESC LIKE '%word2%' OR A_AUTHOR LIKE "%word2%') AND (A_TITLE LIKE '%word3%' OR A_DESC LIKE '%word3%' OR A_AUTHOR LIKE "%word3%'))