How to determine which column "matched"

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.

Can someone help steer me in the right direction?

Why so complicated


On Apr 12, 2008, at 5:25 AM, 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:


<?php
$slq = "select producer, designation from wine where designation
like '%apple%' and producer like '%apple%' ";
$rs = mysql_query($sql);

while( $row = mysql_fetch_row($rs) ){
echo $row[0] ; // producer
echo $row[1] ; // designation
}

?>

>
>
> 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.
>
> Can someone help steer me in the right direction?
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>

Igor Jocic
http://www.carster.us/

On Fri, 2008-04-11 at 20:25 -0700, 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%'

<?php

$query =
"SELECT "
." producer, "
." (producer like '%apple%') AS producerMatched "
."WHERE "
." producer like '%apple%' "
."UNION SELECT "
." designation, "
." (designation like '%apple%') AS designationMatched "
."WHERE "
." designation like '%apple%' ";

Cheers,
Rob.
--
http://www.interjinn.com
Application and Templating Framework for PHP

On Fri, 2008-04-11 at 23:42 -0400, Robert Cummings wrote:
> On Fri, 2008-04-11 at 20:25 -0700, 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%'
>
> <?php
>
> $query =
> "SELECT "
> ." producer, "
> ." (producer like '%apple%') AS producerMatched "
."FROM "
." wine "
> ."WHERE "
> ." producer like '%apple%' "
> ."UNION SELECT "
> ." designation, "
> ." (designation like '%apple%') AS designationMatched "
."FROM "
." wine "
> ."WHERE "
> ." designation like '%apple%' ";

Sorry, forgot the FROM clause. BTW, you shouldn't use a union, this can
be cleaned up to the following query:

<?php

$query =
"SELECT "
." producer, "
." (producer like '%apple%') AS producerMatched, "
." designation, "
." (designation like '%apple%') AS designationMatched "
."FROM "
." wine "
."WHERE "
." producer like '%apple%' "
." OR "
." designation like '%apple%' ";

?>

Cheers,
Rob.
--
http://www.interjinn.com
Application and Templating Framework for PHP

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/