Selecting 1 Record from multiple galleries in same table

[tux]

Hello everyone, new here.

I'm finding this hard to explain.

Scenario.

I have 2 tables, one for galleries and one for images. The gallery table holds the info for the gallery owner. The images table holds the image info linked by the galleryID.

I want to query the image table so that I select just the first image from each gallery. Images are uploaded at random by users so the galleryID is random in the images table depending on who uploaded and when.

Eg. The table may look like this....

ID |GalleryID|filename ( letters used to keep it simple )
1 | 1 | xyz
2 | 4 | abc
3 | 1 | xxx
4 | 2 | yyy
5 | 4 | zzz
6 | 3 | aaa
7 | 2 | bbb
8 | 3 | ccc

So, I need to just get the filename for the first instance of each gallery.
1- xyz 2- yyy 3- aaa 4- abc

So far I have this query......

Code: Select all

$query    = sprintf("SELECT * FROM wp_ngg_gallery INNER JOIN wp_ngg_pictures ON wp_ngg_gallery.gid=wp_ngg_pictures.galleryid "); 
$result   = mysql_query($query); 
$gallery  = mysql_fetch_array($result);

.... and to display this.....

Code: Select all

<?php do{ ?>

<div>
<a href="http://www.mysite/<?php echo $gallery['name']; ?>"><img src="http://www.mysite/<?php echo $gallery['path'] . "/thumbs/thumbs_" . $gallery['filename']; ?>" title="<?php echo $gallery['title']; ?>" alt="<?php echo $gallery['title']; ?>" width="100" height="75" /></a>
</div>

<?php } while ($gallery  = @mysql_fetch_array($result));  ?>

This of course displays all images.

Does anyone know how I can code this to just select the first image of each gallery.

Hope this makes some sense.

Thanks everyone.

Regards, Paul

[Nullsig]

Just use the following query:

Code: Select all

SELECT *
FROM wp_ngg_gallery g INNER JOIN
        (SELECT GalleryID, MIN(ID) as 'minimum'
         FROM wp_ngg_gallery
         GROUP BY GalleryID) x
                ON g.GalleryID = x.GalleryID AND g.ID = x.minimum
ORDER BY g.GalleryID


This will return the file with the lowest ID for each gallery.
1- xyz 2- yyy 3- aaa 4- abc

It allows you to accommodate any number of Galleries. Essentially it will show the oldest picture in each gallery (assuming ID is auto incrementing). So if you wanted to show the newest image in each gallery you would just change MIN to MAX.

The added benefit of this method is that you don't have to iterate through all of the images with PHP to determine which picture you need. SQL does all the thinking for you.

[tux]

Thanks for your speedy reply. I will give it a bash and see how I get on.

Regards, Paul

[tux]

Cant seem to get this to work.

Here is my actual table and field names.

The gallery table is.... 'wp_ngg_gallery' and the ID field is 'gid' (used for getting user info and gallery info)

The images table is.... 'wp_ngg_pictures' and its ID field is 'galleryid' (used for getting image info for each gallery)

Can you show me with my actual tables/ids please.

Cheers, Paul

[Nullsig]

can you list the fields in both tables and how they relate to each other. The code I gave you works specifically for the example you gave me. I need to see the structure of the actual tables to modify it to your needs.

[tux]

wp_ngg_gallery
gid|name|slug|path|title|

wp_ngg_pictures
pid|image_slug|postid|galleryid|filename|description


They are related by the 'gid' and the 'galleryid'

Thanks

[Nullsig]

Code: Select all

SELECT *
FROM wp_ngg_pictures p INNER JOIN
        (SELECT galleryid, MIN(pid) as 'minimum'
         FROM wp_ngg_pictures
         GROUP BY galleryid) x
                ON p.galleryid= x.galleryid AND AND p.pid = x.minimum INNER JOIN
         wp_ngg_gallery g
                ON p.galleryid = g.gid
ORDER BY p.galleryid

This will give you every field from both _gallery and _pictures for the first picture each gallery

[tux]

Thanks Nullsig,

I will give it a try when I get home and let you know how I get on.

Regards, Paul

[egami]

Nullsig, I think you have a double AND in there; which may cause a problem.

[Nullsig]

Sorry that must have been my st-st-stutter

[tux]

Thanks Nullsig,

It worked perfectly. I changed it so that MIN is MAX so I get the latest images and removed the stuttering AND hehe!

Nullsig, could I ask you to go through the logic of this code with me. I get INNER JOINS but have not used nested selects and group by before and don't get how you have included the extra letters like p, x & g.

Could you explain how/what each piece of code is doing. Just want to learn.

Thanks. Paul.

[Nullsig]

Yeah here ya go....

First off the letters after each table or sub query are called "aliases" it allows you to refer to the table or subquery by that letter or word instead of typing out the entire table name over and over throughout the query. It isn't required but it makes the code look cleaner and easier to read.

The sub-query that joins back to the table it queries on is due to the fact that Group By statements require every field in the select statement to be either an aggregate function or in the Group By clause. Since you needed all of the fields from the picture table and needed to choose only the (in your case) Last inserted picture for each gallery, the sub query acts as the filter for the main picture table.

This structure is actually one I use quite a bit in one of the sites I run as I always need to find the most recent/least recent/min/max value related to a 1-to-many join.

[tux]

Thanks for that Nullsig,

Its always good to learn something new everyday. You have been my tutor for today, thank you.

Next time I have something I don't understand I'll be sure to come back.

Take care.

Paul