Tierra Innovation

Tierra Lab

«
»

MySQL Snippet: Pick a Daily Featured Item Automatically

Here is how to add a featured item on your website that changes automatically each day.  For this example let’s assume you have an e-commerce site with a MySQL table called “products” that has a column called stock_count which contains the count of each product in stock.  We want to feature products in stock, changing daily and also changing automatically if during the day the product goes out of stock.

First we need to pick a stock to feature, this query does that:

select date_format(now(),'%j') % count(*) from products
where stock_count > 0

The date_format() function returns the ordinal day of the year for the current day, a number in the range of 1 to 366.  We take that number and do a modulus by the number of total in stock products to get an index (not an id) of today’s in stock product.

Next we need to get the product picked from the first query.  Lets assume you stored the result of the previous query into a variable named $index.  Here is the query:

select * from products where stock_count > 0 order by id
limit $index, 1

You now have the product information to display on your page.

NOTE: If you are using PHP, you can optimize the first query so that MySQL will cache it by removing the now() call.   To do this first use the PHP date() function to get the current day of the year and then use it like this:

$i = date("z") + 1; // date returns 0..365
$sql = "select $i % count(*) from products where stock_count > 0";

Bookmark and Share

This entry was posted on Wednesday, June 24th, 2009 at 10:41 am . You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “MySQL Snippet: Pick a Daily Featured Item Automatically”

  1. shadowhand Says:
    June 24th, 2009 at 12:35 pm

    Wouldn’t this tend to give you same “random” product on the same day each year?

  2. Doug Says:
    June 24th, 2009 at 1:15 pm

    shadowhand: Yes. Its not random, it cycles through your available products restarting on January 1st.

Leave a Reply

Copyright © 2010 Tierra Innovation, Inc.