PDA

View Full Version : PHP Logic help


tux
09-28-2011, 11:31 AM
Hello Guys its been a long time.

Got something I cant figure out, not done any PHP for ages and have got well rusty.

I have two MYSQL tables one containing gallery info and one containing image info.

I have this....

$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 am displaying the thumbnails with this....

<?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)); ?>

Therefore, I am pulling out the galleries and the images for each gallery.

My problem is that this displays all images but I only want to display one image from each gallery. The images are random in the table as they are uploaded by the photographers as and when they want.

So, in summary I want to pull just the first image from each gallery as the query loops through the table.

Thanks Guys, Paul

edbr
09-28-2011, 12:48 PM
LIMIT 1 in your select query should do the job

tux
09-28-2011, 01:05 PM
Hello Edbr,

Its nice to be back on DWC.

I tried that but it will only return the first record in the table.

I need to return the first record of each gallery ID. So as the query loops through the table it wil come acrosssay gallery id 01 and take the first image then look for gallery ID 02 and take that ones first image. etc. I currently have 10 galleries and growing. Each gallery could have any number of images.

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

ID|GalleryID|filename
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

Is that clearer. Iknow i'm rubbish at explaining things hehe.

Paul

edbr
09-28-2011, 01:52 PM
hi paul on my phone so cant look properly but sounds doable maybe with some conditionables

tux
09-28-2011, 07:16 PM
Okay thanks, I'll look forward to hearing about 'some conditionables' when you have time.

Regards, Paul

edbr
09-29-2011, 02:24 AM
right, that looks interesting. ill try to duplicate the database and have a crack using a foreach loop later

actually i could use the sql to create the tables as you have them , would make it clearer can you do that?

tux
09-29-2011, 07:02 AM
I have exported the two tables into 2 seperate files and zipped them up. Hope this is what you need.

Regards, Paul

edbr
09-30-2011, 02:21 AM
didnt get enough time to try , im kinda swamped this month my thinking though was to make a string and increment in a loop

then select form table _ _ _ _ _where galleryid= $m LIMIT 1. Its just a brain 'fast' but might work

tux
09-30-2011, 12:50 PM
I have managed to find a solution.

Here it is.....


$query = sprintf("SELECT * FROM wp_ngg_pictures p INNER JOIN (SELECT galleryid, MAX(pid) as 'maximum'
FROM wp_ngg_pictures GROUP BY galleryid) x ON p.galleryid= x.galleryid AND p.pid = x.maximum INNER JOIN wp_ngg_gallery g ON p.galleryid = g.gid ORDER BY p.galleryid ");
$result = mysql_query($query);
$gallery = mysql_fetch_array($result);


This returns only the first record in the table wp-ngg-pictures for each gallery. Using MAX(pid) as 'maximum' gives the latest record change to MIN(pid) as 'minimum' if you require the oldest.

Hope this helps someone else.

Regards, Paul

edbr
10-01-2011, 01:46 AM
nice, thats one for the snippet bag. I hadnt seen that before