Getting a random image for each category

[jordanwb]

I have two tables like so:

 

CREATE TABLE IF NOT EXISTS `categories` (
  `category_id` int(10) unsigned NOT NULL auto_increment,
  `category_name` varchar(64) NOT NULL,
  `category_parent` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `images` (
  `image_id` int(10) unsigned NOT NULL auto_increment,
  `image_category_id` int(10) unsigned NOT NULL,
  `image_name` varchar(64) NOT NULL,
  `image_path` varchar(128) NOT NULL,
  PRIMARY KEY  (`image_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

What I want to do is get a random image for each category row where category_parent is 0. image_category_id would be the category_id of each row.

[fenway]

That's two separate things -- find out which cats match, and then getting a random one for each.