RE: [PHP-DB] MySQL/PHP Left Join Question

I approach this by assigning a value to a variable and then comparing to see if i need to write out a new title $oldDVD = ""; while ($rows=mysql_fetch_array($result)) { //decide whether to show the DVD title if ($oldDVD != $rows['title']) {

    echo "<tr><td>".$rows['title']."</td></tr>";
    $oldDVD = $rows['title'];

} //echo out the rest of the data echo "<tr><td>".$rows['comment']."</td></tr>"; } hth Bastien

From: "Andrew Darby" <adarby@gmail.com> To: php-db@lists.php.net Subject: [PHP-DB] MySQL/PHP Left Join Question Date: Thu, 25 May 2006 11:28:56 -0400 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