Display random image

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
bob_the _builder
Forum Contributor
Posts: 131
Joined: Sat Aug 28, 2004 12:25 am

Display random image

Post by bob_the _builder »

Hi,

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;
I have tried code such as:

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());
But thats displays images that arnt even related to the maincat etc.


Cheers
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

bob_the _builder,

try...
  • selecting ALL from the main category and subcategory
  • left joining on the picture's subactegory equal to the subcategory
  • where the subcategory's main category id equals the main category
  • grouping the pictures by subcategory id
  • ordering by random as u have
casue it sounds like u dont want a filter on what categories or sub categories shown
bob_the _builder
Forum Contributor
Posts: 131
Joined: Sat Aug 28, 2004 12:25 am

Post by bob_the _builder »

Hi,

I have tried a ton of different ways .. Im not good with joins :?

Code: Select all

$preview = mysql_query("SELECT m.maincat_id, s.subcat_id, i.photo_filename
		FROM gallery_maincat m, gallery_subcat s
		LEFT JOIN gallery_images i 
    	ON i.subcat_id = s.subcat_id
		WHERE m.maincat_id = s.maincat_id GROUP BY i.subcat_id ORDER BY RAND()") or die(mysql_error());
That still shows random subcat images that arnt related to the maincat.


How should it be?



Thanks
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

bob,
That still shows random subcat images that arnt related to the maincat.

Code: Select all

WHERE m.maincat_id = s.maincat_id AND m.maincar_id = '$theMainCatID'
Where $theMainCatID is the category u need to filter
bob_the _builder
Forum Contributor
Posts: 131
Joined: Sat Aug 28, 2004 12:25 am

Post by bob_the _builder »

Hi,

I beleive this maybe the answer, I forgot a I had called on the following prior to the $preview query:

Code: Select all

$sql = mysql_query("SELECT * FROM gallery_maincat ORDER BY maincat_id DESC LIMIT $from, $max_results");
Here is what seems to work:

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 = {$row['maincat_id']}
		ORDER BY RAND()") or die(mysql_error());
Does that seem plausable?

Should it be inner join?


Thanks
Post Reply