Re: How to determine which column "matched"

On 12 Apr 2008, at 04:25, Rob Gould wrote:

I'm trying to figure out a way that SQL can pass a flag to PHP to say which column "matched" during a query. Let's say for instance that I want to search for the word "apple" in both column "producer", and column "designation". I was hoping I could do something like this: Select producer, flag=1 from wine where producer like '%apple%' UNION Select designation, flag=2 from wine where designation like '%apple%' and then in each row that comes back, I could determine which column did that match by doing a PHP analysis of the "flag" value. However, this doesn't appear to be the right way to go about this (mySQL doesn't like these flag=1, flag=2 things.

select producer as field1, 1 as flag from wine where producer like '%apple%' union select designation as field1, 2 as flag from wine where designation like '%apple%' But as other posters have pointed out a union is a lot more work for the DB engine to do when you could easily grab both fields in a single query. -Stut -- http://stut.net/