I have 3 tables, maincat, subcat, images.
How can I show a random image for each maincat from corrosponding subcats:
table structures:
Code: Select all
CREATE TABLE gallery_maincat (
maincat_id int(20) NOT NULL auto_increment,
maincat_name varchar(50) NOT NULL default '',
PRIMARY KEY (maincat_id)
) TYPE=MyISAM;
CREATE TABLE gallery_subcat (
subcat_id int(20) NOT NULL auto_increment,
maincat_id int(20) NOT NULL,
subcat_name varchar(50) NOT NULL default '',
PRIMARY KEY (subcat_id)
) TYPE=MyISAM;
CREATE TABLE gallery_images (
photo_id int(20) NOT NULL auto_increment,
subcat_id int(20) NOT NULL,
photo_filename varchar(25) default '',
photo_caption text,
PRIMARY KEY (photo_id)
) TYPE=MyISAM;Code: Select all
$preview = mysql_query("SELECT i.photo_filename
FROM gallery_images i
LEFT JOIN gallery_subcat s
ON i.subcat_id = s.subcat_id
LEFT JOIN gallery_maincat m
ON m.maincat_id = s.maincat_id
WHERE m.maincat_id = s.maincat_id ORDER BY RAND()") or die(mysql_error());Cheers