Consider the following code snippet:
$beat = date('B'); // 1 beat = 86.4 seconds, 1000 beats = 1 day
$total = 'SELECT COUNT(id) FROM ads WHERE featured = 1;'; // number of featured ads
$current = 'SELECT * FROM ads WHERE featured = 1 ORDER BY id ASC LIMIT 1 OFFSET ' . ($beat % $total) . ';'; // current featured ad
Basically this cycles trough all the featured ads giving each one of them a beat (86.4 seconds) window where they will be given special highlight, example:
$beat $total $current
0 3 0
1 3 1
2 3 2
3 3 0
4 3 1
5 3 2
6 3 0
7 3 1
This works just fine, however I was wondering if somehow it would be possible to eliminate the need for the $total query and accomplish the same using just one query.
I don't see anyway to do this without using sub-queries but still, I'd like to hear your opinion on this.
no, this is not possible. mysql requires an explicit constant value in LIMIT clauses. you can't put a calculation in a LIMIT clause.
$current = 'SELECT *, FOUND_ROWS(id) as num FROM ads WHERE featured = 1 ORDER BY id ASC LIMIT 1 OFFSET MOD(' . $beat . ', num)';
You can't optimize queries. But you can optimize algorithm. Probably, you do need to do this, but... ))
If $total >= 1000 you will never show some ads. At any case some ads are shown more times then others.
long timestamp = ... // standard timestamp in millis
long period = 86400; // millis
long total = ... // you total query
long offset = (timestamp / period) % total;
current = ... // you last query, but with my offset