MySQL query doing my head in

PMWD · 5 Mar 2009 at 20:21

Can some kind person help me with this one please

Code:

"SELECT fd_gallery.gallery_id, fd_gallery.gallery_title, fd_gallery_images.image_thumb " .
"FROM fd_gallery " .
"INNER JOIN fd_gallery_images ON fd_gallery.gallery_id=fd_gallery_images.gallery_id " .
"WHERE fd_gallery_images.image_active='1' ".
"GROUP BY fd_gallery.group_id " .
"ORDER BY fd_gallery.gallery_id DESC";

The problem I've got is it seems to be ignoring the ORDER BY element.

At the moment we have 6 galleries and 4 groups. In a gallery id/group id structure the fd_gallery goes like this:

gallery_id/group_id
1/1
2/2
3/3
4/4
5/2
6/4

It's working fine in terms of the GROUP BY as it's only showing 4 galleries, but it's not showing the latest versions of the groups which it would do if it took notice of the ORDER BY. So it's showing gallery_id 4 instead of 6 and 2 instead of 5.

HELP!

SimonCHere · 5 Mar 2009 at 22:29

The group by is happening before the order by so the order by can only "see" the records it's left with.

"SELECT max(fd_gallery.gallery_id), fd_gallery.gallery_title, fd_gallery_images.image_thumb " .
"FROM fd_gallery " .
"INNER JOIN fd_gallery_images ON fd_gallery.gallery_id=fd_gallery_images.gallery_id " .
"WHERE fd_gallery_images.image_active='1' ".
"GROUP BY fd_gallery.group_id, fd_gallery.gallery_title, fd_gallery_images.image_thumb " .
"ORDER BY fd_gallery.gallery_id DESC";

(disclamer not tested or anything!)

PMWD · 6 Mar 2009 at 02:03

Thanks for trying Simon, but that just pulled out all the images!

I've fixed it using 2 queries and an array, which isn't as clean as I would have liked it but I'm fed up with it now...

SimonCHere · 6 Mar 2009 at 09:39

Now I'm a little more awake how about:

Code:

select g.gallery_id, g.group_id, g.gallery_title, i.image_Thumb
From fd_gallery g
inner join fd_gallery_images i
  on g.gallery_id = i.gallery_id
where i.image_active = '1'
and g.gallery_id in (select max(gallery_id) from fd_gallery group by group_id)

fez · 6 Mar 2009 at 10:01

Looks about right. Make sure you are aliasing the table names like simon has done. I find it makes code a lot easier to read and debug. Less clutter.

PMWD · 6 Mar 2009 at 13:57

Hehe nope now it's pulling 279 entries! Thanks for the heads up on the aliases though.

Here's the code I've used to get it to work, you might be able to see the structure a little easier and work out what I'm doing wrong. The 2nd query shouldn't be neccessary but for some bizarre reason I simply could not get it to collect the thumbnail with an INNER JOIN without pulling out all the records for the thumbs on the first query.

Code:

<?php
$group_id = array(); // New Array
$group_id[] = 0; // Give Array Opening Value That Won't Be Added Later

$query = "SELECT g.gallery_id, g.gallery_title, g.group_id " .
"FROM fd_gallery g " .
"ORDER BY g.gallery_id DESC";
$result = mysql_query($query) or die(mysql_error());
$nor = mysql_num_rows($result);

if($nor > 0)
{
while($row = mysql_fetch_assoc($result))
{
$gi = $row["gallery_id"];
$gt = $row["gallery_title"];
$gri = $row["group_id"];
$gts = super_safe($gt);

if(in_array($gri,$group_id))
{
// Group already in array
}// if(in_array($gri,$group_id))
else
{
$group_id[] = $gri;

$query2 = "SELECT i.image_thumb " .
"FROM fd_gallery_images i " .
"WHERE i.image_active='1' " .
"AND i.gallery_id='$gi' " .
"ORDER BY i.image_id DESC " .
"LIMIT 1";
$result2 = mysql_query($query2) or die(mysql_error());
$nor2 = mysql_num_rows($result2);

if($nor2 > 0)
{
$it = mysql_result($result2,0,0);
}// if($nor2 > 0)
else
{
$it = "holding.gif";
}// else to if($nor2 > 0)						
?>
<div id="latest_top">
<a href="/galleries/<?php echo $gts; ?>/<?php echo $gi; ?>/" title="Latest Gallery"><img alt="Latest Gallery" src="/galleries/<?php echo $it; ?>" /></a>
</div>
<div id="latest_base">
<p><a href="/galleries/<?php echo $gts; ?>/<?php echo $gi; ?>/" title="Latest Gallery"><img alt="Latest Gallery" src="/_images/latest_gallery.gif" /></a></p>
<p>&nbsp;</p>
<p><?php echo htmlentities($gt,ENT_QUOTES); ?></p>
<p>-----</p>
<p>&nbsp;</p>
</div>
<?php
}// else to if(in_array($gri,$group_id))

}// while($row = mysql_fetch_assoc($result))

}// if($nor > 0)
else
{
echo "&nbsp;";
}// else to if($nor > 0)
?>

SimonCHere · 6 Mar 2009 at 16:10

Argg PHP

More importantly, what's the data in your 2 tables?
The select I posted should have retrieved 4 rows (from your op) with 1 image per row with table data as follows:

Code:

fd_gallery
gallery_id,group_id,gallery_title
1,1,one
2,2,two
3,3,three
4,4,four
5,2,five
6,4,six

fd_gallery_images
gallery_id,image_thumb, image_active
1,image1,1
2,image2,1
3,image3,1
4,image4,1
5,image5,1
6,image6,1
2,image2old,0

returns:
gallery_id  group_id    gallery_title   image_Thumb                                        
1             1             one              image1
3             3             three            image3
5             2             five              image5
6             4             six               image6

Unless of course there is more than 1 image with image_active = 1 for a gallery_id. Then you would get 1 row per image. And by that LIMIT 1 in you select I guess you do.

PMWD · 6 Mar 2009 at 16:47

Yeah that's the issue I think

fd_event_groups.group_id

Is a foreign key in

fd_gallery

So that has

fd_gallery.gallery_id
fd_gallery.gallery_title
fd_gallery.group_id

The images table

fd_gallery_images

Has the fd_gallery.gallery_id in there as a foreign key

fd_gallery_images.image_id
fd_gallery_images.image_thumb
fd_gallery_images.image_active
fd_gallery_images.gallery_id

So there's multiple instances of the group_id in the fd_gallery table
And there's multiple instances of the gallery_id in the fd_gallery_images table where the image_active == 1.

What I need to do is pull out the latest gallery for each group, in addition to the last thumbnail added to the images table specifically for that gallery.

SimonCHere · 6 Mar 2009 at 17:10

Code:

select g.gallery_id, g.group_id, g.gallery_title, i.image_Thumb
From fd_gallery g
inner join fd_gallery_images i
  on g.gallery_id = i.gallery_id
where i.image_active = '1'
and i.image_id = (select max(image_id) from fd_gallery_images where gallery_id = i.gallery_id group by gallery_id)
and g.gallery_id in (select max(gallery_id) from fd_gallery group by group_id)

It's not great but it should work