I was recently asked by a client to produce a report of content posted by day. My first instinct was to query the database for all rows of the particular content type and handle the counting in PHP.

First Idea #

1. Query the database for all rows of content type X
2. Create an array in PHP of days
3. Loop through all rows
4. Use PHP to figure out the day the row was created
5. Increment the array element for that day

This seemed pretty inefficient though and felt like something some SQL could handle far better.

SQL Implementation #

MySQL offers the WEEKDAY function that returns an integer showing the day of the week for the date it is passed. With “0” being Monday, “1” being Tuesday etc.

The next slight problem I had is my dates were stored as timestamps and weekday requires a date in the YYYY-MM-DD format, you can also optionally pass in hours, minutes and seconds.

MySQL functions to the rescue again. This time we need FROM_UNIXTIME function. This function takes a Unix timestamp and returns it in the correct date format for WEEKDAY.

I then did a loop from 0 to 6 and stored the results in an array for output.

The loop with query in ended up as follows. I am also using the MySQL COUNT function to just return the number of results as that is all I am interested in here.

$PDO is a PDO connection to the database.

We loop through and get the number of rows, using COUNT, for each day and content type X.

This stored in an array with the key equal to the MySQL day and the value being the count.

So Monday would look like

0 => 763

Tuesday would look like

1 => 542