[RESOLVED] Need a solution to display random images depending of the hour

lpa

Need a solution to display random images depending of the hour.

My database contains 2 columns:
start_time
end_time
with hour ranges from each image (only hours, not minutes):

15 | 18
9 | 13
21 | 7

I tried it in this way:

$timenow = date("H");

$result = mysql_query("SELECT id FROM images_rotate 
where start_time < $timenow and end_time > $timenow 
ORDER BY last_showed ASC limit 0, 1") or die(mysql_error());

It works but there is a problem when start time number is biger than end time number after midnight. For example, if I want to display an image from 21:00 in the evening till 06:00 in the morning, the start time will be 21 and time 6, so the image will be not displayed using this query.

Any ideas how to solve this problem?

Thanks

CoderDan

Are you set on your approach?
For rotating images based on the hour I think there may be a much more elegant solution, if you tell me exactly what you are trying to achieve, I might be able to suggest something.

i.e. if you want to rotate through your images so a new image is shown every X hours, out of a table of images that has a fixed size, then there's an easy solution, however if it's more specific than that, it might need something different.

in your OP it sounds like you want specific images to show during specific timeframes, however, and that's something else again, and not random.

lpa

CoderDan, thank you for your reply.

I know, image rotation can be done even without MySQL, but this is not the case.
Yes, you are right I want want specific images to show during specific timeframes, not only 1 different image every hour.

There will be many images with the same timeframe. The timeframes will be very different, like:
12-13
12-18
00-24
6-8 etc, etc

For each user will be displayed different images depending of their city in the profile.

CoderDan

I rewrote your query into pseudocode type, assuming these are the values you are using:
SELECT id FROM images_rotate
where start_hour < now_hour
and end_hour > now_hour
ORDER BY last_showed ASC limit 0, 1

if this is true, then I am thinking you want something like this:
SELECT id FROM images_rotate
where
( start_hour < now_hour
and IF( end_hour < start_hour, end_hour + 24, end_hour ) > now_hour )
OR
( IF( end_hour < start_hour, start_hour - 24, start_hour ) < now_hour
and end_hour > now_hour )
ORDER BY last_showed ASC limit 0, 1

haven't put it through all it's paces, and it can probably be simplified if it works...

lpa

Thank you very much, CoderDan!
It works. That's what I needed.