Hi all,
I've tried a few different ways of achieving this, once with a nested SELECT query in a while loop, but that didn't work. Now I'm trying to achieve it in one loop. It's for a photo site with more than one photographer (members) and is supposed to display their groups and a random image from each group alongside the group's name and description etc.
Table 1 is groups of images (like galleries Sport Nudes Portraits etc) with a description and some other detail. Table 2 is the images with member_id, group_id, dimensions, path etc
Table 1
CREATE TABLE bb_groups (
group_id int(3) NOT NULL auto_increment,
group_name varchar(70) NOT NULL default '',
group_description varchar(156) default NULL,
member_id int(2) NOT NULL default '0',
group_status char(1) NOT NULL default 'Y',
group_captions char(1) NOT NULL default 'Y',
group_locations char(1) NOT NULL default 'Y',
group_yeartaken char(1) NOT NULL default 'Y',
PRIMARY KEY (group_id)
) TYPE=MyISAM
Table 2
CREATE TABLE bb_images (
image_id int(3) unsigned NOT NULL auto_increment,
caption varchar(99) default '',
order int(3) NOT NULL default '0',
yeartaken year(4) default NULL,
location varchar(50) default '',
g_id int(3) default '0',
member_id int(2) NOT NULL default '0',
date_added datetime default NULL,
width int(3) default NULL,
height int(3) default NULL,
PRIMARY KEY (image_id)
) TYPE=MyISAM
What I want to do is to display all groups that a photographer has set up and then get one image, at random, from each group to display beside the group's title. Sounds easy huh? The LIMIT 4 is just for pagination reasons at the moment.
//start UL for a member's groups here
echo "<ul class=\"group_list\">";
$query = "SELECT * FROM bb_groups, bb_images WHERE bb_groups.member_id = '$m_id' AND group_status = 'Y' AND bb_groups.group_id = bb_images.g_id ORDER BY RAND() LIMIT 4";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
$group_id = $row[0];
$group_name = $row[1];
if (is_null($row[2])) {
$group_descriptor = "";
} else {
$group_descriptor = "/ "."$row[2]";
}
$i_id = $row[8];
/* echo "Member_id = $row[3]<br />";
echo "Status = $row[4]<br />";
echo "Captions = $row[5]<br />";
echo "Locations = $row[6]<br />";
echo "Yeartaken = $row[7]<br />";
echo "Caption = $row[9]<br />";
echo "Order = $row[10]<br />";
*/
echo "<li><p><a href=\"group.php?m=$m_id&g=$group_id\" title=\"$row[1]\" /><img src=\"./includes/thumb.php?i=$i_id\" class=\"thumb\" /><span class=\"group_name\">/ $group_name</span><br /></a><span class=\"descriptor\">$group_descriptor</span></p></li>";
} // end of while loop
echo "</ul>";
What the script is returning though, if I take away the LIMIT 4, is every image in my test database, so obviously my SELECT is wrong but I'm a bit baffled...
JR
