logo-dw

Go Back   Dreamweaver Club Forums > Hand Coders Forum > PHP
Register FAQ Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools Display Modes
Old 09-28-2011, 10:31 AM   #1
tux
tux's Avatar
 
Join Date: Feb 2007
Location: Isle of Wight, that little island right at the bottom of the UK
Posts: 657
Default PHP Logic help

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....

PHP Code:
$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 Code:
<?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
tux is offline   Reply With Quote
Old 09-28-2011, 11:48 AM   #2
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,164
Default

LIMIT 1 in your select query should do the job
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas
edbr is offline   Reply With Quote
Old 09-28-2011, 12:05 PM   #3
tux
tux's Avatar
 
Join Date: Feb 2007
Location: Isle of Wight, that little island right at the bottom of the UK
Posts: 657
Default

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
tux is offline   Reply With Quote
Old 09-28-2011, 12:52 PM   #4
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,164
Default

hi paul on my phone so cant look properly but sounds doable maybe with some conditionables
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas
edbr is offline   Reply With Quote
Old 09-28-2011, 06:16 PM   #5
tux
tux's Avatar
 
Join Date: Feb 2007
Location: Isle of Wight, that little island right at the bottom of the UK
Posts: 657
Default

Okay thanks, I'll look forward to hearing about 'some conditionables' when you have time.

Regards, Paul
tux is offline   Reply With Quote
Old 09-29-2011, 01:24 AM   #6
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,164
Default

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?
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas

Last edited by edbr; 09-29-2011 at 01:51 AM..
edbr is offline   Reply With Quote
Old 09-29-2011, 06:02 AM   #7
tux
tux's Avatar
 
Join Date: Feb 2007
Location: Isle of Wight, that little island right at the bottom of the UK
Posts: 657
Default

I have exported the two tables into 2 seperate files and zipped them up. Hope this is what you need.

Regards, Paul

Last edited by tux; 07-16-2013 at 08:48 PM..
tux is offline   Reply With Quote
Old 09-30-2011, 01:21 AM   #8
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,164
Default

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
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas
edbr is offline   Reply With Quote
Old 09-30-2011, 11:50 AM   #9
tux
tux's Avatar
 
Join Date: Feb 2007
Location: Isle of Wight, that little island right at the bottom of the UK
Posts: 657
Default

I have managed to find a solution.

Here it is.....

PHP Code:
$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
tux is offline   Reply With Quote
Old 10-01-2011, 12:46 AM   #10
edbr
edbr's Avatar
 
Join Date: Aug 2005
Location: Bali
Posts: 11,164
Default

nice, thats one for the snippet bag. I hadnt seen that before
__________________
If you're happy and you know it shake your meds!
different style links examples

Flight / Hotel search
Free script download
Bali Villas
edbr is offline   Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 11:06 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Copyright 2006 DreamweaverClub.com