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

So this is can be possible at either the PHP or SQL level, but I'll address doing this with SQL.

You'll want to use the modulus operator to rotate through the bungalows. You might need to tweak the following slightly due to the specific needs of your data, but it should be fairly close.

I'm assuming the ID field is numeric and am combining that with the mod operator and the current day of the month to cycle through the bungalows. The "gotcha" here is that row numbers start at 1 and mod functions can return 0 to "row numbers" - 1.

Here's an example:

with bungalow_row_numbers as (

select

id,

row_number() over (order by id) featured_bungalow_row_number

from bungalows

where featured = 1

)

select b.id

from

bungalows b,

bungalow_row_numbers rn

where b.featured = 1

and b.id = rn.id

and mod(EXTRACT (DAY from CURRENT_DATE), rn.featured_bungalow_row_number) = rn.featured_bungalow_row_number - 1