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?

Interesting! Well, just keep a count of all featured bungalows in table. Display different featured bungalow each day using system time(). Each time the date increments increment a variable till the number of featured bungalow. — Vivek Sadh, May 04 '13 at 06:29
I'd almost think about doing a featured module, similar to newsflash but modify the query to use only featured. — Elin, May 04 '13 at 09:35
Interesting question we are trying similar where we have time range let me give you my data and you can build your own query over this. okay here we used http://stackoverflow.com/questions/9604391/sql-combine-data-from-two-different-date-ranges-into-one-result-set and it works with a smile :) — Kevin_cl, May 04 '13 at 06:42
Dasun are you using a separate table to manage ordering the way content does? — Elin, May 09 '13 at 16:04
wouldn't it be better if the `featured` column would actually contain `null` when the bungalow is not featured at all, OR an actual date on which it is featured? when you click on your 'lets feature this' button a script could calculate the next available date to feature it on, and your could update the db according to that. after that, you just have to `"SELECT .. WHERE featured == '".date('Y m d')."'; "` (hungarian date order :) ) — ppseprus, May 10 '13 at 10:02

score 8 · Accepted Answer · edited Dec 23 '13 at 15:02

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

can you please add little more details explaining how it works? — Techie, May 08 '13 at 15:19
@Dasun added an explanation, pls let me know if something is not clear — fthiella, May 08 '13 at 16:25

Wicked · Answer 2 · 2013-05-07T17:05:06.747

3

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

edited May 07 '13 at 17:05

answered May 06 '13 at 14:32

Wicked

165
1
6

1

nice idea :) using just a MySQL query it's not that easy, but using MySQL+PHP makes things easier... +1... i would suggest to add an order by `SELECT * FROM bungalows WHERE featured=1 ORDER BY id ...` because SQL tables are not ordered by default – fthiella May 06 '13 at 18:46

score 3 · Answer 3 · edited Jun 20 '20 at 09:12

Try this query it will work in every case with increase in number of featured bungalows etc and 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) a
where  
   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...

can you please add little more details explaining how it works? — Techie, May 08 '13 at 15:13
That's really not how you would do it in Joomla. First you would use jdatabasequery. — Elin, May 08 '13 at 20:39

score 2 · Answer 4 · edited May 23 '17 at 11:49

The basic concept of my answer is to;

1/ Create a list of all featured bungalows. This is achieved in the sub-query, where each bungalow is given a unique sequence number. The code for the seq_num field is based on the answer here

2/ Pick a single bungalow from that listed based on where we are in the month. To do this I look at the day of the month for today, using the code day(curdate()) and I find the mod of that number to the total number of featured bungalows.

select  sq.bungalow_name
from    (
        select  bungalow_name
                ,@curRow := @curRow + 1 AS seq_num
        from    table1, (SELECT @curRow := 0) r
        where   featured = 1
        order by
                bungalow_name desc
        ) sq
where   sq.seq_num = mod(day(curdate()),(select count(*) from table1 where featured = 1))

Example at this SQL Fiddle

score 0 · Answer 5 · answered May 06 '13 at 12:17

You have to track the last date of display for each featured record (last_viewed). For new records, set this date to a day in the past, eg. 2000-01-01. If there is a record with the current date, use that. In not, use the record with the earliest date.

SELECT *, IF(DATE(last_viewed)=CURDATE(), 1, 0) AS current
   FROM #__bungalows
   WHERE featured=1
   ORDER BY current DESC, last_viewed ASC
   LIMIT 0,1

Like the hit counter in com_content, you should add a hit method to your bungalow model, which sets the last_viewed column of the selected bungalow to now().

beiller · Answer 6 · 2013-05-07T18:28:07.243

0

select ....

where featured = 1
limit DAYOFYEAR(NOW()) % (select count(*) from ... where featured = 1), 1

I'm not sure if subselect is allowed in limit. You may have to perform that query separately. This will rotate every day. Easy peasy lemon squeezy.

edit: to perform in 2 queries

$query = "SELECT COUNT(*) FROM ... WHERE FEATURED = 1";
$count = intval(array_pop(mysql_fetch_assoc(mysql_query($query))));

$query = "
select ....

  where featured = 1
  limit DAYOFYEAR(NOW()) % {$count}, 1
";

DONE!

edited May 07 '13 at 18:28

answered May 06 '13 at 19:26

beiller

3,105
1
11
19

you can't nest a query in the limit clause (to achieve something similar you need a prepared statement) – Wicked May 07 '13 at 17:01
ok there is another way: order by rand(dayofyear(now)) limit 1 -- this will pick a random one every day. Maybe there is a better way because there is a chance it will pick the same twice. – beiller May 07 '13 at 17:49
"Given that I don't want to show a bungalow randomly for each page load. I want to show per day one bungalow basis." – Wicked May 07 '13 at 17:54
Yeah well its not completely random. It is random once per day which I think fits the description. Its not "random every page load". I think that's the main point being communicated, but who knows? :P – beiller May 07 '13 at 18:24

score 0 · Answer 7 · answered May 11 '13 at 23:50

The modulo-operator % does the trick:

First, add a column "counter int".

Next, number the featured columns like 1,2,3... if you are lazy, you can use this:

set @cc=0;
update bungalows set counter=(select @cc:=@cc+1) where featured=1;

Now, everything is prepared and you simply can do a first select:

select * from bungalows where featured=1 and counter%(select count(*) from bungalows where featured=1)=0;

And everytime before you need the next featured bungalows, do a:

update bungalows set counter=counter+1 where featured=1;

Then again:

select * from bungalows where featured=1 and counter%(select count(*) from bungalows where featured=1)=0;

...

update bungalows set counter=counter+1 where featured=1;

and so on...

Deval Shah · Answer 8 · 2013-05-18T11:57:40.010

Check here with SQLFiddle

SELECT *
FROM bungalows b
  JOIN (SELECT
          ( DAYOFMONTH(CURDATE() ) % COUNT(b2.id) ) AS slab,
          COUNT(b2.id) AS total_count
        FROM bungalows b2
        WHERE b2.featured = 1) AS b3
WHERE IF(b3.slab = 0, b3.total_count, b3.slab) = (SELECT
                                                    COUNT(id)
                                                  FROM bungalows b1
                                                  WHERE b.id >= b1.id
                                                      AND b1.featured = 1)
    AND b.featured = 1

Showing Featured Item From the Database

8 Answers8

EDIT