Selecting 1 Record from multiple galleries in same table

images php coding issues or problems here.

Moderators: macek, egami, gesf

tux
New php-forum User
New php-forum User
Posts: 7
Joined: Wed Sep 28, 2011 11:24 am

Selecting 1 Record from multiple galleries in same table

Postby tux » Wed Sep 28, 2011 11:44 am

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

User avatar
Nullsig
php-forum Fan User
php-forum Fan User
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: Selecting 1 Record from multiple galleries in same table

Postby Nullsig » Wed Sep 28, 2011 12:01 pm

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
New php-forum User
New php-forum User
Posts: 7
Joined: Wed Sep 28, 2011 11:24 am

Re: Selecting 1 Record from multiple galleries in same table

Postby tux » Wed Sep 28, 2011 12:06 pm

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

Regards, Paul

tux
New php-forum User
New php-forum User
Posts: 7
Joined: Wed Sep 28, 2011 11:24 am

Re: Selecting 1 Record from multiple galleries in same table

Postby tux » Wed Sep 28, 2011 12:23 pm

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

User avatar
Nullsig
php-forum Fan User
php-forum Fan User
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: Selecting 1 Record from multiple galleries in same table

Postby Nullsig » Wed Sep 28, 2011 12:30 pm

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
New php-forum User
New php-forum User
Posts: 7
Joined: Wed Sep 28, 2011 11:24 am

Re: Selecting 1 Record from multiple galleries in same table

Postby tux » Wed Sep 28, 2011 2:00 pm

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

User avatar
Nullsig
php-forum Fan User
php-forum Fan User
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: Selecting 1 Record from multiple galleries in same table

Postby Nullsig » Thu Sep 29, 2011 4:18 am

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
New php-forum User
New php-forum User
Posts: 7
Joined: Wed Sep 28, 2011 11:24 am

Re: Selecting 1 Record from multiple galleries in same table

Postby tux » Thu Sep 29, 2011 6:01 am

Thanks Nullsig,

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

Regards, Paul

User avatar
egami
php-forum GURU
php-forum GURU
Posts: 2196
Joined: Wed Oct 06, 2010 11:19 am
Location: Happy Valley, UT

Re: Selecting 1 Record from multiple galleries in same table

Postby egami » Thu Sep 29, 2011 7:05 am

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

User avatar
Nullsig
php-forum Fan User
php-forum Fan User
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: Selecting 1 Record from multiple galleries in same table

Postby Nullsig » Thu Sep 29, 2011 8:50 am

Sorry that must have been my st-st-stutter

tux
New php-forum User
New php-forum User
Posts: 7
Joined: Wed Sep 28, 2011 11:24 am

Re: Selecting 1 Record from multiple galleries in same table

Postby tux » Fri Sep 30, 2011 1:16 am

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.

User avatar
Nullsig
php-forum Fan User
php-forum Fan User
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: Selecting 1 Record from multiple galleries in same table

Postby Nullsig » Fri Sep 30, 2011 7:23 am

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
New php-forum User
New php-forum User
Posts: 7
Joined: Wed Sep 28, 2011 11:24 am

Re: Selecting 1 Record from multiple galleries in same table

Postby tux » Fri Sep 30, 2011 9:20 am

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


Return to “PHP coding => Images”

Who is online

Users browsing this forum: No registered users and 2 guests

cron