Geek Answers Handbook

MySQL GROUP BY UNIX TIMESTAMP

I am trying to extract several rows from a MySQL database and group them by the time they were sent.

The 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 that this can only be done in MySQL without PHP doing the extra work.

This is a query that I still have, but that does not seem to be grouped 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.

+7
source share
5 answers

Use this query to get the data in the correct 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 from 1 to 7 (1 = Sunday), so your results will be sorted from Sunday to Saturday. DAYNAME returns the literal name of the day (Sunday, Monday, ...). Then you can 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'] . ", "; }
+1
source

Your above query works, except that when you GROUP BY single column, you need to somehow merge all the others.

For example, GROUP BY Day returns one record for each day. If several entries are entered during each day, and you request their id , title , category , etc., How does MySQL know which of the several displays you, since it only shows you one row? (Usually it shows the "first" row for each day, as they appear in "SELECT * FROM mytable" , but you should not rely on it).

The solution is that you somehow combine all of these individual properties, such as id , into one line for each 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, for example:

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

those. IDs, headings, categories, sources, types, and contents are converted to a single line, separated by commas, per day.

However, it seems that this does not quite meet your goals (having one line with the contents of all articles on this day seems pointless).

I think you should make your request:

 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

(Note that I changed your DAYOFMONTH to DAYOFWEEK , because it seems to be better suited to your original question.)

The change is that there is no grouping - it is simply sorted by date.

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

Then you can do something like this in your php:

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

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 it is not easy to use after and a limited size (default 1kB).

This simple request

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

You can 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'])); }
+2
source

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

Invalid request 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
+1
source

Unfortunately, MySQL does things slower because you cannot create an index for this order. I suggest you use Postgres ;-) But in your case you should group the results on the PHP side, but avoid using huge pieces of data, PHP eats too much memory to store it.

+1
source

More articles:


All Articles