Query depending on day of week?

Hello,

I’m after some help with a how to go about running a query. On my site I wish to output a list of headlines that were most popular from Monday to Saturday.
So today’s date for example is a Tuesday - ‘2010-12-07’ so my query would need to find the most popular story for:

Now these days would change depending on the day of the week. So for example when tomorrow comes the day is Wednesday and now be ‘2010-12-08’. So my database would now look for stories on:

However i’m struggling to figure out how to do this. My initial thoughts were to find the current date and then run a query based on that in a foreach loop, something like:


//1) Find todays date
$today = date("Y-m-d");

//2) Find the day of the week
$the_date = date("D");

//3) Loop through the array
$days = array ("Mon","Tue","Wed","Thu", "Fri", "Sat");
foreach ($days as $day) {
  $query = "Select * from db where published = 'how_do_i_force_a_specific_date?' and most_popular = 1";
}

Anyone help?

Thanks

I’m pretty sure you can do all of that in one query.
I remember Rudy helped me out on something similar a while ago and you might as well use MySQL’s native functions rather than construct something externally in php.

Will move you over to MySQL forum.

Also how do you quantify which headline is the most popular? Count the number of times viewed? Column in the table?

Thanks. Yep i’ve got something which sorts the most popular elsewhere, so I just have to in this instance simply add and most_popular = 1 here.

The trick here is that you always want to get the data of the last 7 days, regardless of what day today is.

So, for the publishdate you would something like


WHERE published > CURDATE() - INTERVAL 7 DAY

you’ll want to extract the last 7 days…

… WHERE published >= CURRENT_DATE - INTERVAL 7 DAY

and you’ll want to sort them from monday to saturday …

… ORDER BY WEEKDAY(published)

:slight_smile:

Thanks that’s great. Hit a stumbling block with this though. What I want to do is output:

  • One record per day (So would have six rows returned Mon - sat)
  • each with the highest ‘read count’ for that day
  • Where the current date < CURRENT_DATE
  • AND most_popular is set to = 1

Started it, but can’t quite get my head around it


SELECT * FROM table
WHERE published < CURRENT_DATE 
AND published >= CURRENT_DATE - INTERVAL 7 DAY 
AND most_popular = 1
ORDER BY published desc , read_count asc

Could you kindly help again please?

Thanks

SELECT t.foo
     , t.bar
     , t.published
     , t.read_count
  FROM ( SELECT published
              , MAX(read_count) AS max_read
           FROM daTable
          WHERE published >= CURRENT_DATE - INTERVAL 7 DAY 
            AND published  < CURRENT_DATE 
            AND most_popular = 1
         GROUP
             BY published ) AS m
INNER
  JOIN daTable AS t
    ON t.published = m.published
   AND t.read_count = m.max_read
ORDER 
    BY published DESC