Showing Featured Item From the Database

You could use this MySQL query:

SELECT *FROM BungalowsWHERE 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=1WHERE  b1.featured=1GROUP 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=1WHERE  b1.featured=1GROUP BY  b1.idHAVING  COUNT(b2.id) = (SELECT                    DATEDIFF(CURDATE(), '2013-05-06') MOD                    (SELECT COUNT(*) FROM Bungalows WHERE Featured=1))

$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 :) )

Try this query it will work in every case with increase in number of featured bungalows etcand daily will give a different one.

Here in the query I am assigning numbers to each featured bungalow from 0 to n and receiving then by dividing total number of featured bungalow to date diff I find the bungalow to be displayed.

Query 1:

select    a.* from    (select       @rn:=@rn+1 as rId,       b.cnt,        a.*    from       Bunglows a   join       (select @rn:=-1) tmp   join      (select          count(*) as cnt       from          Bunglows       where          featured=1)b   where       featured=1) awhere     datediff(CURDATE(), '2013-01-01')%a.cnt=a.rId

SQL FIDDLE:

| RID | CNT | ID | BUNGALOW_NAME | FEATURED |---------------------------------------------|   3 |   4 |  6 |    bungalow 4 |        1 |

EDIT

select count(*) as cnt from Bunglows where featured=1

This query finds the total featured bungalows

select @rn:=@rn+1 as rId, b.cnt, a.* from Bunglows a join (select @rn:=-1) tmp join     select count(*) as cnt from Bunglows where featured=1

This query adds the a rownumber to each featured bungalow starting from 0 to n

The main query first finds date diff from current date and a old date and find mod value by total featured bungalows which will give values from 0 to n-1 and I have added a where clause which checks for the divided value to be equal to the rowid which we have assigned..

Hope this helps...