[RESOLVED] MySQL random photo, find next / last photos?

Yari

Hi,

I have come across an interesting problem... I have a photo gallery that shows photos in a 'seeded' random order each week.

I.e. Photos are ordered randomly, but stay that way for 1 week

I now want to go to an individual photo, and see the last 2 and next 2 photos in the random order described above.

My sql is:

	$limit = 5;
	$type = "1"; // gallery 1
	$rand_seed = date("W"); // order by Week of year (e.g. 42nd week of 52)

$sql = "SELECT photo_id FROM yallery_photos JOIN yallery ON photo_yallery_id=yallery_id WHERE yallery_album='$type' ORDER BY RAND(".$rand_seed.") LIMIT $limit";
if (!($result=mysql_db_query(DB, "$sql"))) $this->mysql_msg($sql,mysql_error(),mysql_errno());

This does what its supposed to, get each Photo id randomly each week...

Now, how can I sort this list based on the Photo I am viewing?

E.g. I am on photo 22, and would like to get the last and next 5 photos in this gallery

how to do this when I am randomizing the results?

Thanks

bogu

Make another select that extracts your next and prev. photos, form your random selected photo ...

Yari

Thank you Bogu, but how?

This is the problem, how do I find the next, last photos when its ordered randomly?

bye

Yari

bogu

Actually two selects, one for the prev photo and one for your next photo:

SELECT photo_id as prev_id FROM yallery_photos WHERE photo_id < $rand_photo_id ORDER BY photo_id DESC LIMIT 1

SELECT photo_id as next_id FROM yallery_photos WHERE photo_id > $rand_photo_id ORDER BY photo_id ASC LIMIT 1

Yari

At first I thought this would work... but it can't as the photos are not ordered by id, so using the statement:

'WHERE photo_id < $rand_photo_id'

actually now orders the photos by id!

Perhaps there is a way to give each photo an order, and then sort using that order?
Using PHP I could put all the photo_id's into an array and then get the last / next 5 from the array, starting at the photo id...

Will let you know how I get on.

Thanks for all your help so far!

Yari

Yep, that worked!