mysql/php query

I have a question about (i think) joining.

If I have a table in a database that has this info:

key - name - favorite
1 - john - 2
2 - judy - 3
3 - joe - 1

the favorite icecream table is this:
key - flavors
1 - vanilla
2 - chocolate
3 - strawberry

how do i do a query to display that judy's favorite is strawberry.

obviously this is a simple example. i am doing working on something
that is much more complex than this, but if anyone can give a hint i
can apply to the thing i am working on.

Thanks in advance!

On 17-Nov-2003, redhatlinux@msn.com (aaron) wrote:

> I have a question about (i think) joining.
>
> If I have a table in a database that has this info:
>
> key - name - favorite
> 1 - john - 2
> 2 - judy - 3
> 3 - joe - 1
>
> the favorite icecream table is this:
> key - flavors
> 1 - vanilla
> 2 - chocolate
> 3 - strawberry
>
> how do i do a query to display that judy's favorite is strawberry.
>
> obviously this is a simple example. i am doing working on something
> that is much more complex than this, but if anyone can give a hint i
> can apply to the thing i am working on.

select flavors from nametable,icecreamtable where favorite=icecreamtable.key
and name='judy'

--
Tom Thackrey
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@willglen.net (it's reserved for spammers)

aaron, doing a poor impression of Xerex, said:
>
> I have a question about (i think) joining.

This is really more of an SQL question than a PHP question.


> If I have a table in a database that has this info:
> key - name - favorite
> the favorite icecream table is this:
> key - flavor

Here's TFM: http://www.mysql.com/doc/en/JOIN.html

Here's your simple answer, since I'm not a complete asshole (and also I
personally find it easier to learn from example):

$q = mysql_query("SELECT * FROM people
LEFT JOIN favorites
ON people.favorite = favorites.key
WHERE name = 'judy'");
if(!$q) die("query failed\n");
if(!mysql_num_rows($q)) die("no entry for judy\n");
$rec = mysql_fetch_assoc($q);
echo "judy's favorite is $rec[flavor]\n";

/joe
--
In git.talk.flame, Dr. Esque mentally reviles in Scott Hughes and a
processor, and then often links to the website of /home/pr0n and mcct and
hoovers, downloads, scans, and carefully emasculates Marilyn. The ninja
clan from icer will go to Irwin! In the Masquerade, Crai... [tape runs out]

redhatlinux@msn.com (aaron) schrieb:

> If I have a table in a database that has this info:
>
> key - name - favorite
> 1 - john - 2
> 2 - judy - 3
> 3 - joe - 1
>
> the favorite icecream table is this:
> key - flavors
> 1 - vanilla
> 2 - chocolate
> 3 - strawberry
>
> how do i do a query to display that judy's favorite is strawberry.

SELECT icecream.flavor
FROM person, icecream
WHERE person.name = 'judy'
AND person.favorite = icecream.key

Regards,
Matthias

*** aaron wrote/escribió (17 Nov 2003 08:49:53 -0800):
> I have a question about (i think) joining.
>
> If I have a table in a database that has this info:
>
> key - name - favorite
> 1 - john - 2
> 2 - judy - 3
> 3 - joe - 1
>
> the favorite icecream table is this:
> key - flavors
> 1 - vanilla
> 2 - chocolate
> 3 - strawberry
>
> how do i do a query to display that judy's favorite is strawberry.

Under MySQL you have two ways:

SELECT name, favourite
FROM people, flavours
WHERE favourite=flavours.key AND name='judy'

or

SELECT name, favourite
FROM people
INNER JOIN flavours ON favorite=flavours.key
WHERE name='judy'

Second one is more standard.

(Untested so typos expected)

--
--
-- Álvaro G. Vicario - Burgos, Spain
--

I noticed that Message-ID:
<dd6e9aa5.0311170849.25a137@posting.google.com> from aaron contained the
following:

>key - name - favorite
>1 - john - 2
>2 - judy - 3
>3 - joe - 1
>
>the favorite icecream table is this:
>key - flavors
>1 - vanilla
>2 - chocolate
>3 - strawberry
>
>how do i do a query to display that judy's favorite is strawberry.

SELECT flavors FROM people,favorite WHERE name ='judy' AND
name.favourite =favorite.key;

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

On Mon, 17 Nov 2003 17:37:41 +0000 (UTC), Disco Plumber
<scag@moralminority.org> wrote:

>aaron, doing a poor impression of Xerex, said:
>>
>> I have a question about (i think) joining.
>
>This is really more of an SQL question than a PHP question.
>
>> If I have a table in a database that has this info:
>> key - name - favorite
>> the favorite icecream table is this:
>> key - flavor
>
>Here's TFM: http://www.mysql.com/doc/en/JOIN.html
>
>Here's your simple answer, since I'm not a complete asshole (and also I
>personally find it easier to learn from example):
>
> $q = mysql_query("SELECT * FROM people
> LEFT JOIN favorites
> ON people.favorite = favorites.key
> WHERE name = 'judy'");
> if(!$q) die("query failed\n");
> if(!mysql_num_rows($q)) die("no entry for judy\n");
> $rec = mysql_fetch_assoc($q);
> echo "judy's favorite is $rec[flavor]\n";

I think you mean INNER JOIN, not LEFT JOIN; LEFT JOIN is the same as LEFT
OUTER JOIN, and so would only be applicable here if the database's referential
integrity was broken - i.e. judy's people.favourite field didn't match any of
the keys in the favourite icecream table, but you still wanted the people row.
Doing an outer join where an inner join is really wanted may have performance
implications as well.

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

Andy Hassall (79.740% quality rating):
>
> I think you mean INNER JOIN, not LEFT JOIN; LEFT JOIN is the same as LEFT
> OUTER JOIN, and so would only be applicable here if the database's
> referential integrity was broken - i.e. judy's people.favourite field
> didn't match any of the keys in the favourite icecream table, but you
> still wanted the people row.

I did mean the LEFT JOIN, but I usually opt for more information rather
than less and do extra error-checking in my PHP code.

> Doing an outer join where an inner join is really wanted may have performance
> implications as well.

Well, I'm no DBA, but I wouldn't expect a LEFT JOIN with an ON clause to
be that much worse than an INNER JOIN with a WHERE clause equating to
roughly the same thing. Is there an order of magnitude difference?

/joe
--
In El Myr, some bastard from IS kisses David Maynor, and then powers up a
preprocessor from Ryan Chaves. A huggable sorority house from Steve
Simonsen will go to Stevie Strickland.

Disco Plumber (74.510% quality rating):
>
> Well, I'm no DBA, but I wouldn't expect a LEFT JOIN with an ON clause to
> be that much worse than an INNER JOIN with a WHERE clause equating to
> roughly the same thing. Is there an order of magnitude difference?

The only information I found regarding this in MySQL's docs was:

http://www.mysql.com/doc/en/LEFT_JOIN_optimisation.html

which implies that LEFT JOINS have extra optimization done.

Regardless, the database implementation should not be my concern as a
PHP programmer. If I am doing valid SQL queries with fairly sound logic
(i.e., putting the processing in the right places, not making
unnecessary amounts of queries), the underlying implementation of one
JOIN versus another should be irrelevant to me. Of course, if I am
querying for information I'm not going to use (e.g., if I was going to
ignore those rows of the result where fields came back NULL), that is a
waste of processing.

But anyway I decided to write a script to do benchmarks for myself...

And the results are almost random (load dependent). There's no clear
winner. Of course, MySQL may be doing caching. But then, MySQL would be
doing caching for the actual service as well.

For reference:

$ mysql --version
mysql Ver 11.16 Distrib 3.23.49, for pc-linux-gnu (i686)

Check out a handful of the test results:

500 left joins (on): 1.480120 sec
500 inner joins: 0.505901 sec
500 left joins (using): 0.549247 sec

500 left joins (on): 1.678084 sec
500 inner joins: 0.723299 sec
500 left joins (using): 0.877342 sec

500 left joins (on): 0.488853 sec
500 inner joins: 0.696226 sec
500 left joins (using): 0.481876 sec

1000 left joins (on): 0.975070 sec
1000 inner joins: 1.654450 sec
1000 left joins (using): 0.969804 sec

1000 left joins (on): 0.993082 sec
1000 inner joins: 1.018203 sec
1000 left joins (using): 1.094612 sec

10000 left joins (on): 10.364384 sec
10000 inner joins: 11.173975 sec
10000 left joins (using): 13.425231 sec

10000 left joins (on): 11.104748 sec
10000 inner joins: 13.026637 sec
10000 left joins (using): 9.970058 sec

10000 left joins (on): 10.662058 sec
10000 inner joins: 10.493683 sec
10000 left joins (using): 16.690147 sec

Here's the script I used:

#!/usr/bin/php4 -q
<?php

include("common.php"); // database connection

define('ITERATIONS', 10000);

function diff($start, $end) {
list($stu, $sts) = explode(" ", $start);
$start = (float)$stu + (float)$sts;
list($etu, $ets) = explode(" ", $end);
$end = (float)$etu + (float)$ets;
return (float)($end - $start);
}

function bench($query, $desc) {
$start = microtime();
for($i=0;$i<ITERATIONS;$i++)
mysql_query($query);
$end = microtime();
$diff = diff($start, $end);
printf("%d %s: %f sec\n", ITERATIONS, $desc, $diff);
}

bench("SELECT * FROM users LEFT JOIN user_settings
ON users.uid = user_settings.uid
WHERE uname = 'phatjoe'",
"left joins (on)");

bench("SELECT * FROM users,user_settings
WHERE users.uid = user_settings.uid
AND uname = 'phatjoe'",
"inner joins");

bench("SELECT * FROM users LEFT JOIN user_settings
USING (uid)
WHERE uname = 'phatjoe'",
"left joins (using)");

?>

/joe
--
A dead relative's processor from the 118 will go to Myke.

On Mon, 17 Nov 2003 19:30:11 +0000 (UTC), Disco Plumber
<scag@moralminority.org> wrote:

>Andy Hassall (79.740% quality rating):
>>
>> I think you mean INNER JOIN, not LEFT JOIN; LEFT JOIN is the same as LEFT
>> OUTER JOIN, and so would only be applicable here if the database's
>> referential integrity was broken - i.e. judy's people.favourite field
>> didn't match any of the keys in the favourite icecream table, but you
>> still wanted the people row.
>
>I did mean the LEFT JOIN, but I usually opt for more information rather
>than less and do extra error-checking in my PHP code.

OK, but I'd argue that if you needed an outer join in this specific case, then
the database is broken; referential integrity checks really belong in the
database (although the real world sometimes gets in the way of that). Even
MySQL 3.x has foreign key constraints now.

>> Doing an outer join where an inner join is really wanted may have performance
>> implications as well.
>
>Well, I'm no DBA, but I wouldn't expect a LEFT JOIN with an ON clause to
>be that much worse than an INNER JOIN with a WHERE clause equating to
>roughly the same thing. Is there an order of magnitude difference?

Well, It Depends. But any time you fetch more data than you need, there's a
difference. And once you get past trivial queries, using outer joins where
they're not needed can certainly change for the worse and constrain the access
paths your database can use.

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)