SitePoint Sponsor

User Tag List

Results 1 to 25 of 25
  1. #1
    SitePoint Addict Iceman90's Avatar
    Join Date
    Mar 2006
    Location
    Calgary, Alberta, Canada
    Posts
    392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP and mySQL - comparing data from two tables

    Hello everyone. I am having a problem that I was hoping someone here would be able to assist me with.

    I am using PHP and mySQL to create a site that reviews DVDs and Music. What I'd love to do is to create a list of release dates. In this list, I'd like it to look something like this

    December 5, 2006
    DVD
    DVD
    Album
    Album

    November 29, 2006
    DVD
    DVD
    Album
    Album
    Album

    November 27, 2006
    Album



    I have 2 tables in my database. One to hold the information about my DVDs, and one to hold my information about my albums.

    What I can't seem to get, is for the data from both tables to display in this way.

    It's easy enough to do with the data from one table, as you just query the data, and display it, sorted by date.

    However, I guess what I am asking, is how do I compare the dates between the two tables, and then display the information (be it a DVD or an album) for all the selected date?

    At first I thought I could use a mySQL JOIN command, but then I don't get all the dates that don't have both a DVD and an album released, so I am assuming PHP.

    I am assuming I'd have to read both sets of dates out of the database, and into an array. Then, using a loop, loop through the dates and find any DVDs or albums that match, and display them. I am just not sure how I'd go about this, or if there is a better method.

    Any thoughts or advice?

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you need a UNION.
    Code:
    select title
         , price
      from cds
     union all
    select title
         , price
      from dvds

  3. #3
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Assuming you are using MySQL 4.1 or later, you can retrieve everything you need with just the database using subqueries, but I can't do that off the top of my head yet, and I am between classes so can't make a test database to figure it out right now.

  4. #4
    SitePoint Addict
    Join Date
    Nov 2005
    Location
    Moss, Norway.
    Posts
    283
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Iceman90 View Post
    At first I thought I could use a mySQL JOIN command, but then I don't get all the dates that don't have both a DVD and an album released, so I am assuming PHP.
    Have you tried left joins?

    Here is a great example from Kevin Yank's book "Build your own Database Driven WebSite" 3rd edition may 2005 page 200.

    SELECT author.name, COUNT(joke.id) AS numjokes FROM author LEFT JOIN joke ON authorid=author.id GROUP BY authorid;

    LEFT JOINS are made for situations in which there is a result in one table but not in the other.

    The tables are, as you may already have guessed, author and joke and numjokes is an alias for the number of joke.id's.
    Last edited by kgun; Dec 5, 2006 at 19:01. Reason: Give an example.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    ( select date_released
         , cd_title      as title
      from cds )
    union all
    ( select date_released
         , dvd_title   
      from dvds )
    order
        by date_released desc
    this will return results that look like this --

    2006-12-05 DVD
    2006-12-05 Album
    2006-12-05 Album
    2006-11-29 DVD
    2006-11-29 DVD
    2006-11-29 Album
    2006-11-29 Album
    2006-11-29 Album
    2006-11-27 Album

    then in your application language, you loop over the result set and detect the date changes in order to format it prettily
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Nov 2005
    Location
    Moss, Norway.
    Posts
    283
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you can do it in MySQL, do it. Using PHP code to solve a problem is a last resort. I think my LEFT JOIN will fix it for him.

    He has not given us the exact table structure (fields) with the id's he uses. No problem in sorting the results ascending or descending on one or more fields.

  7. #7
    malloc
    Join Date
    Nov 2006
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    longneck & r937 are right.

    Unions are the way to do it. They're designed for when you want the same data from different sources (or you're "pretending" it's the same).

    Joins (left or otherwise), however, are for relating records together. That is not the case here. You probably could do it with some perversion of joins and concats, but let's not go there.

  8. #8
    SitePoint Addict
    Join Date
    Nov 2005
    Location
    Moss, Norway.
    Posts
    283
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If both methods function, which is most efficient? I have an idea that a join is most efficient, since it creates a third table on the fly. Operating on one table should be most efficient in my view, but I have not tested it.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i think the more appropriate question is, "If both methods function, which is correct?"

    the only possible column to join those tables on is the date, but in that case, you will definitiely want to use a FULL OUTER JOIN, not a LEFT OUTER JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    malloc
    Join Date
    Nov 2006
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i think the more appropriate question is, "If both methods function, which is correct?"
    Absolutely.

    But the answer to both questions is surely "union"

  11. #11
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The answer is proper database design.

  12. #12
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Quote Originally Posted by stereofrog View Post
    The answer is proper database design.
    zing!

  13. #13
    SitePoint Addict
    Join Date
    Nov 2005
    Location
    Moss, Norway.
    Posts
    283
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i think the more appropriate question is, "If both methods function, which is correct?"

    the only possible column to join those tables on is the date, but in that case, you will definitiely want to use a FULL OUTER JOIN, not a LEFT OUTER JOIN
    Abou 10 years since I worked on the SyBase platform. May be you are correct.

    I can look it up in the book that is on my knee.

    Teach yourself Transact SQL in 21 days from Sams Publishing, but I leave it to the thread starter to come back and tell what functioned and what is most efficient, that is: "correct".

    P.S.
    I remember there was something called outer joins, but do not remember the syntax.

  14. #14
    SitePoint Addict
    Join Date
    Nov 2005
    Location
    Moss, Norway.
    Posts
    283
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    Assuming you are using MySQL 4.1 or later, you can retrieve everything you need with just the database using subqueries, but I can't do that off the top of my head yet, and I am between classes so can't make a test database to figure it out right now.
    Joins and subqueries are regarded as advanced topics in the above mentioned book, last chapter 21. I hope these hints are enough to solve the problem in the most efficient way. I leave that to the thread starter, since he has the data.

  15. #15
    SitePoint Addict Iceman90's Avatar
    Join Date
    Mar 2006
    Location
    Calgary, Alberta, Canada
    Posts
    392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all the replies everyone. I am going to try these out and see if I can get something functional.

    For those who wanted to see the database structure, here are the fields.

    dvd
    id,release_date,cover,title,studio,stars,price,rating

    album
    id,release_date,cover,title,artist,price,rating

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    they have more columns in common than different

    i would use a single table here

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    malloc
    Join Date
    Nov 2006
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would use something like this:

    release
    id PK,release_date,cover,title,price,rating

    dvd
    id PK FK release(id),sudio,stars

    album
    id PK FK release(id),artist

    Look up "class table inheritance" if you're interested.
    r937's suggestion is "single table inheritance" and would need an extra type discriminator field, but it's simpler & faster if you know your tables aren't going to grow. The other problem is you can't make the dvd/album specific fields "NOT NULL".

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    look up "subtype/supertype" too

    if i were going to have a common (supertype) table and separate (subtype) tables, i would at least not use surrogate ids as primary keys in the subtype tables, i would use the release_id as both PK/FK

    no sense compounding the surrogate key madness
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    malloc
    Join Date
    Nov 2006
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    look up "subtype/supertype" too
    As far as I can see this is the same thing? Google didn't return anything very useful tho.

    i would at least not use surrogate ids as primary keys in the subtype tables, i would use the release_id as both PK/FK
    I'm not. Perhaps my shorthand SQL was confusing:

    dvd
    id PK FK release(id)

    as in...

    Code:
    CREATE TABLE dvd (
      id ...
      ...
      PRIMARY KEY (id),
      FOREIGN KEY id REFERENCES release(id)
    )
    So the id column in the subtype tables is a foreign key - the release id.

    Agreed?

  20. #20
    Non-Member I87's Avatar
    Join Date
    Mar 2006
    Location
    UK
    Posts
    378
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I've found something new in this thread

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by JonSiddle View Post
    So the id column in the subtype tables is a foreign key - the release id.

    Agreed?
    yes, agreed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    At My Desk!!
    Posts
    1,642
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    have you guys stopped bickering yet??
    "Am I the only one doing ASP.NET in Delphi(Pascal)?"

  23. #23
    malloc
    Join Date
    Nov 2006
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    have you guys stopped bickering yet??
    Hey....this time we were saying the same thing!

    "Nothing exists but atoms and empty space;
    everything else is opinion. "
    I disagree! - had to be done :P

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i will also agree to disagree -- besides atoms and space, there is energy

    string theorists claim that atoms are made of energy anyway, so it should really be only energy and space...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    At My Desk!!
    Posts
    1,642
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    haha
    "Am I the only one doing ASP.NET in Delphi(Pascal)?"


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
  •