MySQL GROUP BY UNIX TIMESTAMP

Question

I'm trying to fetch a number of rows from a MySQL database and group them by the day they were posted.

End result I would like the following..

Monday -Article 1 -Article 2 -Article 3

Tuesday -Article 1 -Article 2

Wednesday -Article 1 -Article 2 -Article 3 -Article 4

And so on, I'm not sure if this can be done in MySQL alone without PHP doing extra work.

This is the query I have so far but doesn't seem to group by day.

SELECT 
cms_news.news_id,
cms_news.news_title,
cms_news.news_date,
cms_news.news_category,
cms_news.news_source,
cms_news.news_type,
cms_news.news_content
FROM 
cms_news cms_news,
GROUP BY 
DAYOFMONTH(FROM_UNIXTIME(cms_news.news_date)) 
ORDER BY 
cms_news.news_date DESC

Thanks.

Answer 1

Your above query does work, except that when you GROUP BY one column, you have to somehow aggregate all the others.

For example, GROUP BY Day returns one record for each day. If there are multiple records entered for each day and you're requesting their id,title,category,etc, how does MySQL know which of the multiple to show you, since it only gets to show you one row? (It usually shows the "first" row for each day as they appear in "SELECT * FROM mytable", but you shouldn't rely on this).

The solution is that you somehow aggregate all those individual properties like id into one string per group. You can use GROUP_CONCAT for this as @narcisradu suggests.

SELECT 
GROUP_CONCAT(cms_news.news_id),
GROUP_CONCAT(cms_news.news_title),
DAYOFMONTH(FROM_UNIXTIME(cms_news.news_date)) as Day,
GROUP_CONCAT(cms_news.news_category),
GROUP_CONCAT(cms_news.news_source),
GROUP_CONCAT(cms_news.news_type),
GROUP_CONCAT(cms_news.news_content)
FROM 
cms_news cms_news,
GROUP BY 
DAYOFMONTH(FROM_UNIXTIME(cms_news.news_date)) 
ORDER BY 
cms_news.news_date DESC

This will give you e.g.:

1,4     Story 1 Title, Story 2 Title       <Day1>       Funny,Sad    ....

I.e. the ids, titles, categories, sources, types, and contents will turn into one comma-separated string per Day.

However it seems like this doesn't quite suit your purposes (having one string with the contents of all the articles on that day seems nonsensical).

I think you should make your query:

SELECT 
cms_news.news_id,
cms_news.news_title,
DAYNAME(FROM_UNIXTIME(cms_news.news_date)) AS Day,
cms_news.news_category,
cms_news.news_source,
cms_news.news_type,
cms_news.news_content
FROM 
cms_news cms_news,
ORDER BY news_date DESC

(Notice I changed your DAYOFMONTH to DAYOFWEEK because it seems to match your original question better.)

The change is that there's no grouping - just ordering by the date.

Since your output is ordered by date, the Day names will also be in order.

You can do something like this in your php then:

$prevday='';
while($row = mysql_fetch_array($res)) {
   if ( $row['Day'] != $prevday ) {
       // day has changed!
       // make a new row. e.g:
       echo "<br/> \n" . $row['Day'];
   } 
   // now just list your article name
   echo " - " . $row['news_title'];
   $prevday = $row['Day'];
}

Answer 2

This query with GROUP_CONCAT()

SELECT 
  news_id
, news_date AS "date"
, GROUP_CONCAT(news_title) AS "articles"
FROM cms_news
GROUP BY news_date
ORDER BY news_date DESC
;

But not easy to use after and limited in size (1kB default).

This simple query

SELECT 
  news_id
, news_date AS "date"
, news_title AS "article"
FROM cms_news
ORDER BY news_date DESC
;

You could easily reindex with php.

// Day Index
$data = array();
while ($row = mysql_fetch_assoc($result)) {
  $data[$row['date']][] = $row;
}
// Display
foreach ($data as $date => $row) {
  echo ($date.' : '.implode(' - ', $row['article']));
}

Answer 3

Use a query such as this one to get the data in the right order:

SELECT
DAYNAME(FROM_UNIXTIME(cms_news.news_date)) AS DayName,
cms_news.*
FROM cms_news
ORDER BY DAYOFWEEK(FROM_UNIXTIME(cms_news.news_date)), cms_news.news_date

DAYOFWEEK returns a number between 1 and 7 (1 = Sunday) so your results will be sorted Sunday through Saturday. DAYNAME returns the literal name of the day (Sunday, Monday, ...). You can then group and display your data using PHP:

$DayName = "";
while($row = mysql_fetch_assoc($query)) {
    if ($DayName != $row['DayName']) {
        $DayName  = $row['DayName']; 
        echo "<br>\n" . $DayName . ": ";
    } 
    echo $row['news_title'] . ", ";
}