MySQL/PHP Left Join Question

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=3Dcomment.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

Re: MySQL/PHP Left Join Question

One thing I've done in situations like this is just load your returned DB data into an array. Something like this would do:


$dvdarr[$result['call_number']]['Title'] = $result['title'];
$dvdarr[$result['call_number']]['Publisher'] = $result['publisher'];
$dvdarr[$result['call_number']]['Comments'][] = $result['comment'];


Put that in a loop of your results. Notice the "[]" on the comments. That'll collect all your comments under the umbrella of 'Comments'.

Then when you go to do your output, you'd do something like this:

forach ($dvdarr as $callnumber => $dvddata) {
$title = $dvddata['Title'];
$publisher = $dvddata['Publisher'];
$comments = $dvddata['Comments'];
foreach ($comments as $comment) {
// do whatever
}
}


There are other ways to handle this.. this might be one of the easier ones (if I'm understanding your problem correctly).

-TG

= = = Original message = = =

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


___________________________________________________________
Sent by ePrompter, the premier email notification software.
Free download at http://www.ePrompter.com.

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

RE: 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 "".$rows['title']."";
$oldDVD = $rows['title'];
}

//echo out the rest of the data
echo "".$rows['comment']."";

}


hth

Bastien



>From: "Andrew Darby"
>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
>

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

Re: MySQL/PHP Left Join Question

Some good examples of how to deal with this in PHP have already been
given, especially the associative array solution.

The question I have is with something like this, how do you weight out
the pros/cons of code development versus speed?
i.e. for code development breaking the logic up into 2 queries would
yield a much more readable result, but speed wise what is better
multiple queries with smaller more precise record sets, or having PHP
parse through the result set to properly weed out duplicate titles based
on the outer join applied?

This is assuming that there is no SQL construct to solve this problem,
which personally I know of none.

-Brad

Andrew Darby wrote:

> 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

Re: MySQL/PHP Left Join Question

Thanks to Bastien and TG for their suggestions. I'd been looking at
it Bastien's way, but TG's is probably better suited to what I want to
do. I'd forgotten about that possibility, cramming it all into an
array . . . .

Again, thanks for the help,

Andrew

p.s. I'd be curious about the relative speeds of these methods, too.

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

Re: MySQL/PHP Left Join Question

test both and let us know

bastien


>From: "Andrew Darby"
>To: php-db@lists.php.net
>Subject: Re: [PHP-DB] MySQL/PHP Left Join Question
>Date: Thu, 25 May 2006 12:33:15 -0400
>
>Thanks to Bastien and TG for their suggestions. I'd been looking at
>it Bastien's way, but TG's is probably better suited to what I want to
>do. I'd forgotten about that possibility, cramming it all into an
>array . . . .
>
>Again, thanks for the help,
>
>Andrew
>
>p.s. I'd be curious about the relative speeds of these methods, too.
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php
>

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