Getting all the rows within a day of a certain date

[thesavior]

I'm writing a calendar, and I want to find a number of rows that within that date. For example, I want to find how many posts occured on the 1st, how many topics occured on the 1st, how many posts occured on the 2nd, so on so forth.

Currently I'm looping through the month while i'm displaying and running the queries on each day.

These are just some of the queries that the page is using:

[1] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-02',`dateadded`) = 1
    [2] => SELECT count(*) FROM posts WHERE DATEDIFF('2009-04-02',`postdate`) = 1
    [3] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-03',`dateadded`) = 1
    [4] => SELECT count(*) FROM posts WHERE DATEDIFF('2009-04-03',`postdate`) = 1
    [5] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-04',`dateadded`) = 1
    [6] => SELECT count(*) FROM posts WHERE DATEDIFF('2009-04-04',`postdate`) = 1
    [7] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-05',`dateadded`) = 1
    [8] => SELECT count(*) FROM posts WHERE DATEDIFF('2009-04-05',`postdate`) = 1
    [9] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-06',`dateadded`) = 1
    [10] => SELECT count(*) FROM posts WHERE DATEDIFF('2009-04-06',`postdate`) = 1
    [11] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-07',`dateadded`) = 1
    [12] => SELECT count(*) FROM posts WHERE DATEDIFF('2009-04-07',`postdate`) = 1
    [13] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-08',`dateadded`) = 1
    [14] => SELECT count(*) FROM posts WHERE DATEDIFF('2009-04-08',`postdate`) = 1
    [15] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-09',`dateadded`) = 1
    [16] => SELECT count(*) FROM posts WHERE DATEDIFF('2009-04-09',`postdate`) = 1
    [17] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-10',`dateadded`) = 1
    [18] => SELECT count(*) FROM posts WHERE DATEDIFF('2009-04-10',`postdate`) = 1
    [19] => SELECT count(*) FROM topics WHERE DATEDIFF('2009-04-11',`dateadded`) = 1

Ideally what I would be able to do, is have two queries done before I start looping through the rows that I would be able to associate in an array.

If you have any idea how to help limit the queries necessary, please let me know.

[Old Pedant]

I'm lost. Why would you do

WHERE DATEDIFF('2009-04-02', dateadded) = 1

instead of just

WHERE dateadded = '2009-04-03'

????

Is it because the field dateadded has a time component that you want to ignore?

If so, it's much more efficient to simply do

WHERE DATE(dateadded) = '2009-04-03'

The MySQL DATE() function strips the time off of a date+time value.

So...

The best way to do this is to have a table of all possible dates for the next 10 years or so (one field in the table, one record per every date for 10 years). Build that table once and then use it until the computer gets recycled.

You can then just do

SELECT allDates.theDate, COUNT(dateadded)
FROM allDates LEFT JOIN othertable ON allDates.theDate = DATE(dateadded)
WHERE allDates.theDate BETWEEN '2009-04-02' AND '2009-04-11'
GROUP BY allDates.theDate
ORDER BY allDates.theDate

Works great. You get one record of output for your given WHERE range of dates.

But...

But if you aren't willing to do that, then just do:

SELECT DATE(dateadded) AS theDate , count(*) AS howMany
FROM yourtable
WHERE DATE(dateadded) BETWEEN '2009-04-02' AND '2009-04-11'
GROUP BY DATE(dateadded)
ORDER BY DATE(dateadded)

Now, you may have some "holes" in there. So as you loop through all the days in your external code (PHP, JSP, whatever), if the date from the set of records is AFTER the loop date, then you know the count for the loop date is zero and you so note it and then DO NOT advance to the next record.

So maybe your loop is going from 2009-4-2 to 2009-4-10:

loopdate: 2009-4-2
   date from db: 2009-4-2
   get count from db
   advance to next record
loopdate: 2009-4-3
   date from db: 2009-4-5
   count is zero (no advance)
loopdate: 2009-4-4
   date from db: 2009-4-5
   count is zero (no advance)
loopdate: 2009-4-5
   date from db: 2009-4-5
   get count from db
   advance to next record

In your case, you'd have two recrodsets and you'd just do the same logic on both of them, but you'd only need one loop.

No?

In the long run, creating the extra allDates table is worth the little bit of trouble. But up to you.

[Old Pedant]

NOTE: for decent performance, of course allDates.theDate should be the primary key. And then *both* dateadded and postdate in your table should be indexed fields.

[thesavior]

Thanks, this is exactly what I was looking for:

SELECT DATE(dateadded) AS theDate , count(*) AS howMany
FROM yourtable
WHERE DATE(dateadded) BETWEEN '2009-04-02' AND '2009-04-11'
GROUP BY DATE(dateadded)
ORDER BY DATE(dateadded)