MySQL/PHP Left Join Question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello, all.  I don't know if this is a php-mysql question or just a
mysql, but here goes:

I have a list of DVDs that my library loans out, and I'd like to allow
people to add comments to each item.  Since this list gets regenerated
periodically (it's extracted from another system, the library
catalog), there isn't a consistent ID in the dvd tables, so I'm using
the call number (which will look like DVD 2324) as the key.  Anyhow, I
join the tables like this to get all the DVDs and all the comments
associated with the DVDs:

SELECT distinct dvds.title, dvds.publisher, dvds.publication_date,
dvds.call_number,
comment.id, comment.parent_id, comment.comment, comment.name
FROM dvds
LEFT JOIN comment
ON dvds.call_number=comment.parent_id
WHERE dvds.title LIKE 'A%'
ORDER BY dvds.title

With this, I'll get results like

DVD 101    A.I.                           This movie rocked
DVD 101    A.I.                           This Movie stunk
DVD 102    Adaptation  . . .
DVD 103    After Hours . . .

When I loop in PHP through the records, of course, I want just the one
DVD with however many comments associated with it.  Is it possible to
do this (i.e., screen out DVD dupes) in MySQL, or do I have to do it
in PHP?

If this is a dumb question, my humblest apologies, and I'd be
interested if there was a better way to handle this . . . .

Andrew

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux