mysql group by timestamp,MySQL GROUP BY UNIX TIMESTAMP

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 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 "
\n" . $row['Day'];

}

// now just list your article name

echo " - " . $row['news_title'];

$prevday = $row['Day'];

}