Professional Web Applications Themes

mysql/php query - PHP Development

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

  1. #1

    Default 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!
    aaron Guest

  2. #2

    Default Re: mysql/php query


    On 17-Nov-2003, [email]redhatlinuxmsn.com[/email] (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
    [url]www.creative-light.com[/url]
    tom (at) creative (dash) light (dot) com
    do NOT send email to [email]jamesbutlerwillglen.net[/email] (it's reserved for spammers)
    Tom Thackrey Guest

  3. #3

    Default Re: mysql/php query

    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: [url]http://www.mysql.com/doc/en/JOIN.html[/url]

    Here's your simple answer, since I'm not a complete (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]
    Disco Plumber Guest

  4. #4

    Default Re: mysql/php query

    [email]redhatlinuxmsn.com[/email] (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
    Matthias Esken Guest

  5. #5

    Default Re: mysql/php query

    *** 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
    --
    Alvaro G Vicario Guest

  6. #6

    Default Re: mysql/php query

    I noticed that Message-ID:
    <dd6e9aa5.0311170849.25a137posting.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 [url]http://www.ckdog.co.uk/rfdmaker/[/url]
    Geoff Berrow Guest

  7. #7

    Default Re: mysql/php query

    On Mon, 17 Nov 2003 17:37:41 +0000 (UTC), Disco Plumber
    <scagmoralminority.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: [url]http://www.mysql.com/doc/en/JOIN.html[/url]
    >
    >Here's your simple answer, since I'm not a complete (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 (andyandyh.co.uk) icq(5747695) ([url]http://www.andyh.co.uk[/url])
    Space: disk usage ysis tool ([url]http://www.andyhsoftware.co.uk/space[/url])
    Andy Hassall Guest

  8. #8

    Default Re: mysql/php query

    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 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 Guest

  9. #9

    Default Re: mysql/php query

    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:

    [url]http://www.mysql.com/doc/en/LEFT_JOIN_optimisation.html[/url]

    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.
    Disco Plumber Guest

  10. #10

    Default Re: mysql/php query

    On Mon, 17 Nov 2003 19:30:11 +0000 (UTC), Disco Plumber
    <scagmoralminority.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 (andyandyh.co.uk) icq(5747695) ([url]http://www.andyh.co.uk[/url])
    Space: disk usage ysis tool ([url]http://www.andyhsoftware.co.uk/space[/url])
    Andy Hassall Guest

  11. #11

    Default Re: mysql/php query

    Andy Hassall (4.980% quality rating):
    >
    > 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).
    I'm not sure what you're saying here about referential integrity checks
    belonging in the database. In the simple example given, if someone is
    added to the people table, but no corresponding entry for them is ever
    added to the favorites table, how does the database know whether or not
    that is a problem (and what should it do if it is?).
    > 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
    I frequently use them in more complex queries, even joining more than
    two tables. However, in most cases where I want a LEFT JOIN, it's
    because there may be no corresponding entries in the second (and
    potentially third) table(s).

    But still, there are cases where there should be no good reason for the
    second table to be missing the corresponding entry, but I don't want to
    omit output data if the entry is somehow missing. Like in the sample
    code where I was checking "users LEFT JOIN user_settings". A user might
    accidentally not have had his user_settings record created... but that
    doesn't mean the user doesn't exist, and so if I am trying to show a
    list of users with some piece of information from their user settings, I
    will do a left join to make sure I get all the users. The alternative
    (if I am not willing to trust the user_settings table to have all of the
    users' entries) would seem to be to do a select on the users table and
    then N selects on the user_settings table, but that's clearly less
    efficient (N+1 queries vs. 1 query).
    > and constrain the access paths your database can use.
    I don't know what this means at all.

    /joe
    --
    The choad is wholesale. The 3LA is slimy and educational.
    Disco Plumber Guest

  12. #12

    Default Re: mysql/php query

    On Mon, 17 Nov 2003 21:27:35 +0000 (UTC), Disco Plumber
    <scagmoralminority.org> wrote:
    >Andy Hassall (4.980% quality rating):
    >>
    >> 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).
    >
    >I'm not sure what you're saying here about referential integrity checks
    >belonging in the database. In the simple example given, if someone is
    >added to the people table, but no corresponding entry for them is ever
    >added to the favorites table, how does the database know whether or not
    >that is a problem (and what should it do if it is?).
    But that can't happen in the data model given; favourite was part of the first
    table.

    The data given was:

    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

    Since 'favorite' was part of the 'person' row, then the only situations where
    an outer join would apply would be:

    (a) favorite was null (this could be a valid case for an outer join)
    (b) favorite was set to a value that does not appear in the favorite table
    (this was the case I was referring to as being broken, since I was assuming
    'favorite' was declared as not null in the first table).

    Case (b) should be caught by a foreign key constraint, and so on the
    assumption that favorite is not null, there's no valid situation for an outer
    join.
    >> 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
    >
    >I frequently use them in more complex queries, even joining more than
    >two tables. However, in most cases where I want a LEFT JOIN, it's
    >because there may be no corresponding entries in the second (and
    >potentially third) table(s).
    Yes, that's the purpose of an outer join.
    >But still, there are cases where there should be no good reason for the
    >second table to be missing the corresponding entry, but I don't want to
    >omit output data if the entry is somehow missing. Like in the sample
    >code where I was checking "users LEFT JOIN user_settings". A user might
    >accidentally not have had his user_settings record created... but that
    >doesn't mean the user doesn't exist, and so if I am trying to show a
    >list of users with some piece of information from their user settings, I
    >will do a left join to make sure I get all the users. The alternative
    >(if I am not willing to trust the user_settings table to have all of the
    >users' entries) would seem to be to do a select on the users table and
    >then N selects on the user_settings table, but that's clearly less
    >efficient (N+1 queries vs. 1 query).
    Sure, if it makes sense (or is convenient) to bring rows back where there is
    no relation. On the other hand, if you're querying for a user's settings, it
    doesn't necessarily make sense to bring back a row if there are no settings in
    the table. That doesn't imply the user doesn't exist, just that that user has
    no settings.

    I see the point you're making (and even agree!), but what I'm trying (badly)
    to get across was that the original question never called for an outer join,
    just an ordinary join. There's no advantage to having it return one row with
    the flavour field NULL, versus no rows indicating no favourites matched.

    As you said on the other reply:
    >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.
    Exactly! :-)

    --
    Andy Hassall (andyandyh.co.uk) icq(5747695) ([url]http://www.andyh.co.uk[/url])
    Space: disk usage ysis tool ([url]http://www.andyhsoftware.co.uk/space[/url])
    Andy Hassall Guest

  13. #13

    Default Re: mysql/php query

    While thinking about how cool Tipper Gore was, Andy Hassall blurted:
    >
    > But that can't happen in the data model given; favourite was part of
    > the first table.
    good point, I should have checked the initial example again before
    spouting.
    > Case (b) should be caught by a foreign key constraint, and so on the
    > assumption that favorite is not null, there's no valid situation for an outer
    > join.
    ah, so in a foreign key constraint I can tell the db "don't allow me to
    add this person record if there is no matching flavor record for his
    favorite"?
    > doesn't necessarily make sense to bring back a row if there are no settings in
    > the table. That doesn't imply the user doesn't exist, just that that user has
    > no settings.
    yes, the example I cited was actually a combined "get all users" and
    "get their settings" simultaneously, so it made sense to do the left
    join.

    /joe
    --
    In the emo garage, the chair from Cuddles the Cat will go to Psi U. Cuddles
    the Cat's delightful computer from Hojohoro Bekahamu will go to Sarah H..
    Mike Doyle ignores the monitor from Faff.
    Disco Plumber Guest

  14. #14

    Default Re: mysql/php query

    On Mon, 17 Nov 2003 22:59:36 +0000 (UTC), Disco Plumber
    <scagmoralminority.org> wrote:
    >> Case (b) should be caught by a foreign key constraint, and so on the
    >> assumption that favorite is not null, there's no valid situation for an outer
    >> join.
    >
    >ah, so in a foreign key constraint I can tell the db "don't allow me to
    >add this person record if there is no matching flavor record for his
    >favorite"?
    Yes - it'll raise an error that there is no parent record.

    Additionally it won't let you delete the flavour record leaving 'dangling'
    rows in the people table that were referencing it; it'll complain there are
    child records present. Typically you can set the constraint to either raise an
    error, 'on delete cascade' (deleting the child rows too), or 'on delete set
    null'.

    --
    Andy Hassall (andyandyh.co.uk) icq(5747695) ([url]http://www.andyh.co.uk[/url])
    Space: disk usage ysis tool ([url]http://www.andyhsoftware.co.uk/space[/url])
    Andy Hassall Guest

Similar Threads

  1. Is this a MySQL Query bug?
    By Carl Anatorian in forum MySQL
    Replies: 4
    Last Post: June 13th, 07:29 AM
  2. Replies: 0
    Last Post: November 4th, 05:38 PM
  3. MySql query help
    By luckyrye in forum PHP Development
    Replies: 2
    Last Post: May 1st, 05:25 AM
  4. PHP - MySQL Query...
    By Steven Kallstrom in forum PHP Development
    Replies: 2
    Last Post: August 14th, 02:22 PM
  5. [PHP] PHP - MySQL Query...
    By Jay Blanchard in forum PHP Development
    Replies: 3
    Last Post: August 14th, 11:43 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139