CODEWITHSUNDEEP
phpmysql
eablokker
eablokker

Reputation: 103

How can I get MySQL records from past 7 days, but only days that have a record, and return each day as a record?

Say I've got a simple mysql table with columns id, title, date. Each day can have several rows, but not every day has a row. For example there might be 5 rows with June 15th as the date, but zero rows with June 14th as the date. How can I get all results from the past 7 days, excluding June 14th because it has no rows. So I'm not just subtracting 7 days from the current date. I want to get only the past 7 days which have any rows at all.

Then I want to return each day as a single result, like a group by date, but by the year/month/day part of the timestamp, so that what I get back is only 7 results, and each result is like an array of individual rows.

I hope that makes sense. Any ideas?

Edit: I realized I can do it something like this:

WHERE Date BETWEEN DATE_SUB( NOW(), INTERVAL DATEDIFF( NOW(), (SELECT Date FROM test GROUP BY Date LIMIT 7,1 ) ) DAY ) and NOW()

But this gives an SQL syntax error. What I'm trying to do is a subquery and group by date to get one result for each day, and return one result starting at offset 7, then do a DATEDIFF on that to get the number of days that DATE_SUB should put in the INTERVAL x DAYS

Upvotes: 0

Views: 3831

Answers (2)

eablokker
eablokker

Reputation: 103

Figured it out: It works!

WHERE Date BETWEEN DATE_SUB(NOW(), INTERVAL (DATEDIFF( NOW(), (SELECT Date FROM test GROUP BY Date ORDER BY Date DESC LIMIT 8,1 ) ) ) DAY) and NOW()

I was missing a parentheses, and I had to add ORDER BY and DESC to the subquery.

Upvotes: 0

Mathew
Mathew

Reputation: 8279

You won't be able to get the same day results back as an array, but you can group it by date, with the titles comma separated:

SELECT GROUP_CONCAT(title) AS titles, date 
FROM test 
WHERE date > DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY date;

Then in PHP, do something like this:

foreach ($results as $row)
{
    echo $row['date'];
    foreach ($row['titles'] as $title)
    {
        echo $title;
    }
}

Upvotes: 2

Related Questions