PHP/SQL Cycle items every x minutes.

Question

Hi all! I have a friend who runs an online auction website. He currently has a featured items section on the homepage that he wants to have cycle an item every X amount of minute. The site runs off a MySQL database which I haven't actually seen yet.

The current code that he is using is a big, long messy Javascript code that is causing all kinds of errors.

The items would have to cycle in order and when they get to the end, go back and repeat again.

What would be the best approach to take to this using PHP?

EDIT: I mean from a backend SQL perspective. Not the UI.

Thanks Ben

Answer 1

Assuming you have a separate table for the featured items (probably has an item ID referencing the main items table and maybe other info)... In this table, add a last_featured column to represent the time the item was last shown. From there, you can manipulate your queries to get a rotating list of featured items.

It might look something like this (as a weird pseudocode mix between PHP & MYSQL):

// select the most recent item in the list, considered the current item
$item = SELECT * FROM featured_items ORDER BY last_featured DESC LIMIT 1;

if ($item['last_featured'] > X_minutes_ago) {
    // select the oldest item in the list, based on when it was last featured
    $item = SELECT * FROM featured_items ORDER BY last_featured ASC LIMIT 1;

    // update the selected item so it shows as the current item next request
    UPDATE featured_items SET last_featured=NOW() WHERE item_id = $item['item_id'];
}

Note that this requires 3 call to the database... There may be a more efficient way to accomplish this.