Showing Featured Item From the Database

Question

I have database table as below.

id, bungalow_name, type, address, featured 

A bungalow can be featured in the home page. If a bungalow is featured, featured column has the value 1. I have 50 bungalows in the tables and 5-7 bungalows are featured at a given time.

Let's assume featured bungalow names are as below.

bungalow 1, bungalow 2, bungalow 3, .........., bungalow 6

What I'm trying to do is show a featured bungalow in the home page for each day. And I want to loop like below as below for each month. Given that I don't want to show a bungalow randomly for each page load. I want to show per day one bungalow basis.

today              -> bungalow 1
tomorrow           -> bungalow 2
day after tomorrow -> bungalow 3
...
After bungalow 6, bungalow 1 is shown on the next day.

How can I do it? Is it even possible with SQL/PHP?

Answer 1

You could use this MySQL query:

SELECT *
FROM Bungalows
WHERE id = (
  SELECT b1.id
  FROM
    Bungalows b1 LEFT JOIN Bungalows b2
    ON b1.id>b2.id AND b2.featured=1
  WHERE
    b1.featured=1
  GROUP BY
    b1.id
  HAVING
    COUNT(b2.id) = (SELECT
                      DATEDIFF(CURDATE(), '2013-05-06') MOD
                      (SELECT COUNT(*) FROM Bungalows WHERE Featured=1))
  )

Please see fiddle here. '2013-05-06' is the day when you want to start to show the first featured bungalow. They will be shown ordered by ID, strarting from '2013-05-06'.

EDIT

The following query will return the number of elapsed days since 2013-05-06:

SELECT DATEDIFF(CURDATE(), '2013-05-06')

the MOD function will return the integer remainder of the division of the number of elapsed day by the number of featured rows:

SELECT DATEDIFF(CURDATE(), '2013-05-06') MOD
                          (SELECT COUNT(*) FROM Bungalows WHERE Featured=1)

If there are 6 featured bungalows, it will return 0 the first day,1 the second,2,3,4,5, and then 0,1,2...again.

MySQL does not have a function to return a RANK (number of row), so you have to simulate it somehow. I simulated it this way:

SELECT b1.id, COUNT(b2.id)
FROM
  Bungalows b1 LEFT JOIN Bungalows b2
  ON b1.id>b2.id AND b2.featured=1
WHERE
  b1.featured=1
GROUP BY
  b1.id

I'm joining the Bungalows table with itself. The rank of bungalow ID is the count of bungalows that have an ID less than that (hence the join b1.id>b2.id).

I'm then selecting only the row that have the RANK returned by the function above:

HAVING COUNT(b2.id) = (SELECT DATEDIFF(CURDATE(), '2013-05-06') MOD (SELECT COUNT(*) FROM Bungalows WHERE Featured=1))

If you use MySQL, my initial query could be simplified as this:

SELECT b1.*
FROM
  Bungalows b1 LEFT JOIN Bungalows b2
  ON b1.id>b2.id AND b2.featured=1
WHERE
  b1.featured=1
GROUP BY
  b1.id
HAVING
  COUNT(b2.id) = (SELECT
                    DATEDIFF(CURDATE(), '2013-05-06') MOD
                    (SELECT COUNT(*) FROM Bungalows WHERE Featured=1))

Answer 2

$dbh = new PDO(....); // use your connection data
$statement = $dbh->query("SELECT count(*) as size FROM bungalows where features = 1");
$data = $statement->fetchALL(PDO::FETCH_CLASS,"stdClass");
$i = date('z') % $data[0]->size;

$statement = $dbh->query("SELECT * FROM bungalows where features = 1 order by id LIMIT $i,1");
$bungalow = reset($statement->fetchALL(PDO::FETCH_CLASS,"stdClass"));

EDIT

• Removed mysql_ calls
• added an order clause as fthiella suggested (thank you :) )