I am looking to get help with this query or see if it should be a php script to get the results I am look for. Here is the table I am using, which can be altered at this point.
CREATE TABLE slideshows (
geo_id int(7) unsigned NOT NULL default '0',
src varchar(40) NOT NULL default ''
);
Field geo_id comes from another table which tells me which image belongs to with what town. Each town has can have several images. Here is an example data for the 10 towns.
INSERT INTO slideshows VALUES (1, '1.png');
INSERT INTO slideshows VALUES (1, '2.png');
INSERT INTO slideshows VALUES (1, '3.png');
INSERT INTO slideshows VALUES (2, '1.png');
INSERT INTO slideshows VALUES (2, '2.png');
INSERT INTO slideshows VALUES (2, '3.png');
INSERT INTO slideshows VALUES (3, '1.png');
INSERT INTO slideshows VALUES (3, '2.png');
INSERT INTO slideshows VALUES (3, '3.png');
INSERT INTO slideshows VALUES (4, '1.png');
INSERT INTO slideshows VALUES (4, '2.png');
INSERT INTO slideshows VALUES (4, '3.png');
INSERT INTO slideshows VALUES (5, '1.png');
INSERT INTO slideshows VALUES (5, '2.png');
INSERT INTO slideshows VALUES (5, '3.png');
INSERT INTO slideshows VALUES (6, '1.png');
INSERT INTO slideshows VALUES (6, '2.png');
INSERT INTO slideshows VALUES (6, '3.png');
INSERT INTO slideshows VALUES (7, '1.png');
INSERT INTO slideshows VALUES (7, '2.png');
INSERT INTO slideshows VALUES (7, '3.png');
INSERT INTO slideshows VALUES (8, '1.png');
INSERT INTO slideshows VALUES (8, '2.png');
INSERT INTO slideshows VALUES (8, '3.png');
INSERT INTO slideshows VALUES (9, '1.png');
INSERT INTO slideshows VALUES (9, '2.png');
INSERT INTO slideshows VALUES (9, '3.png');
INSERT INTO slideshows VALUES (10, '1.png');
INSERT INTO slideshows VALUES (10, '2.png');
INSERT INTO slideshows VALUES (10, '3.png');
Each time the page loads I would like to display only ten images, one image from each town randomly placing the towns in different order as well as randomly select what image to use. The current query I am using is the following.
SELECT geo_id, img FROM slideshows_img GROUP BY geo_id ORDER BY RAND() LIMIT 10;
The reuslts randomly display the towns in a different order each time, but always selects the first image for each town but nevers ransomly selects the others.
geo_id => 1, src => 1.png
geo_id => 3, src => 1.png
geo_id => 6, src => 1.png
geo_id => 7, src => 1.png
geo_id => 2, src => 1.png etc ...
When it should be producing,
geo_id => 1, src => 2.png
geo_id => 3, src => 1.png
geo_id => 6, src => 3.png
geo_id => 7, src => 1.png
geo_id => 2, src => 2.png etc ...
Can I get some one to help me with this, thanks.
