CODEWITHSUNDEEP
phpmysqlgroup-bytimestampaggregate-functions
Story Teller
Story Teller

Reputation: 427

MySQL GROUP BY UNIX TIMESTAMP

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.

Upvotes: 7

Views: 2477

Answers (5)

Salman Arshad
Salman Arshad

Reputation: 272106

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'] . ", ";
}

Upvotes: 1

Pierre de LESPINAY
Pierre de LESPINAY

Reputation: 46178

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']));
}

Upvotes: 2

mathematical.coffee
mathematical.coffee

Reputation: 56915

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'];
}

Upvotes: 4

YuriBro
YuriBro

Reputation: 902

Unfortunately MySQL do such things veeeery slow, because you can't create index for this ordering. I'd suggest you to use Postgres ;-) But in your case you should group results on PHP side, but avoid of using huge portions of data, PHP eats too much memory to store it.

Upvotes: 1

Narcis Radu
Narcis Radu

Reputation: 2547

I think you may try GROUP_CONCAT(cms_news.news_title SEPARATOR '-'). More information at: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Some untested query that actually solves the problem:

SELECT 
  cms_news.news_id,
  CONCAT(
    DAYNAME(FROM_UNIXTIME(cms_news.news_date)),
    ' - ',
    GROUP_CONCAT(cms_news.news_title SEPARATOR ' - ')
  ) as result,
cms_news.news_date
FROM 
cms_news cms_news
GROUP BY 
DAYOFMONTH(FROM_UNIXTIME(cms_news.news_date)) 
ORDER BY 
cms_news.news_date DESC

Upvotes: 1

Related Questions