Query question - Probably simple

[TileGuy]

Good afternoon everyone.

 

I'm using Server version: 5.0.90

 

I'm designing a website for Tile (go figure).

We sort our "series" of tiles into "collections".

 

I'm attempting to make a page, that shows a preview of all series within a collection.

 

This is where I'm at presently:

 

SELECT series.seriesID, series.seriesName, plogger_pictures.id

FROM series, plogger_pictures

WHERE series.collection = colname AND plogger_pictures.parent_album = series.installalbum

ORDER BY series.seriesName ASC

 

colname is the url variable that specifies what collection is to be loaded.

 

The query "works" but returns every photo i have, sorted by series, for the url provided collection.

 

I just need one row per series, with a random plogger_pictures.id from the same plogger.parent_album.

 

I would apreciate any help or suggestions.  Thanks for your patience with the newbee.

 

Thanks

 

TileGuy

[dabaR]

$series_ids = db_query("SELECT id FROM series");

foreach ($series_ids as $series_id) {
  $images_for_series = db_query("SELECT image_something FROM images WHERE series_id = " . db_quote($series_id));
  $random_image_from_series = array_rand($images_for_series);
  do_something($random_image_from_series);
}

 

Hope that helps!

[ignace]

I just need one row per series

 

 

SELECT series.seriesID, series.seriesName, plogger_pictures.id

FROM series, plogger_pictures

WHERE series.collection = colname AND plogger_pictures.parent_album = series.installalbum

GROUP BY series.collection

ORDER BY rand(), series.seriesName ASC

 

 

[TileGuy]

awesome, thank you both. 

 

Ignance is close but I would ideally get a list of all series in that collection each combined with a randome photo.

 

Dabar... your code is beyond me and I look forawrd to sitting down tonight and learning how it works.

 

Thanks for the quick responses.

 

TileGuy

[ignace]

Post both tables and their columns + indicate any foreign keys and to what they reference.

[TileGuy]

Table 1 = series

Fields:

seriesID  int(5) primary

seriesName varchar(25)

collection int(2)

installalbum int(7)

(there are more fields, but these are the relavent fields)

 

Table 2 = plogger_pictures

Fields:

path varchar(255)

parent_album int(11) <--- this field should join with series.installalbum

id int(11) Primary

 

Idealy, the sql would return All series within a collection (say collection # 1)

 

It would then match 1 picture from plogger_pictures (with the same album id) and join it with the relevant series.

 

The intention is to create a list of series within a collection, with a random photo from each series gallery displayed.

 

Is there help for me yet?

 

thanks everyone

 

TileGuy

 

[fenway]

Then get all of the series, and join to a random row of picture for each one.