Hello.

I just can not understand and find solutions how to make.

We have the news table (news_id int NOT NULL auto_increment, news_added datetime NOT NULL, news_title varchar (255), PRIMARY KEY (news_id), KEY idx_news_added (news_added))

The point is to display news, for example, a week by day. Those.

11/27/2012 News 4572 | News 4571 | News 4570

11/26/2012 News 4569

11/25/2012 News 4568 | News 4567

One request is it possible to do?

    3 answers 3

    first SELECT * FROM news ORDER BY news_added DESC

    then on php

     $news = array(); while ($item = ...->fetch_assoc()){ $news[$item['news_added']][] = $items['news_title ']; }
    • Yeah thanks. I already did the same. I think that there is nothing complicated in the end, then) - drop_off

    I improvised a little your request in my database

     -- Структура таблицы `tete` -- CREATE TABLE IF NOT EXISTS `tete` ( `news_id` int(11) NOT NULL, `news_added` datetime NOT NULL, `news_title` varchar(255) NOT NULL, PRIMARY KEY (`news_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Дамп дадзеных табліцы `tete` -- INSERT INTO `tete` (`news_id`, `news_added`, `news_title`) VALUES (1, '2012-11-01 05:17:24', 'new 4000'), (2, '2012-11-01 05:17:24', 'New 4001'), (3, '2012-11-01 05:17:24', 'new 40011'), (4, '2012-11-02 05:17:25', 'New 4002'), (5, '2012-11-02 05:17:25', 'New 4003');

    Request itself

     SELECT `news_added` , GROUP_CONCAT( `news_title` ) AS `news_title` FROM `tete` GROUP BY `news_added`

    Result

     news_added news_title 2012-11-01 05:17:24 new 4000,New 4001,new 40011 2012-11-02 05:17:25 New 4002,New 4003
    • If this week, add something after the FROM: WHERE ( news_added > '2012-11-01 00:00:00') AND ( news_added <'2012-11-07 00:00:00') - abibock_un
    • GROUP_CONCAT is not as I understand it. I need to display the tree type and get an array to transfer it to the template using smarty. This is like a site map output, when a conclusion is built up by months. In my case, not a month, but a week is the limit. - drop_off
    • probably either a subquery to do on the selection of records, depending on the current date, or you can somehow one. or recursively merge somehow into the desired array. I just never came across, for this I do not know how this is done correctly. - drop_off
    • SELECT displays a data table for you, and then you have to work with it. Select through the array from it the data and write to smarty Output as a tree, then, if I'm not mistaken here is not mysql, but css + html + php. Sorry if something is not understood. - abibock_un
    • hashcode.ru/users/10443/dropoff "I need to display the type of the tree and get an array." This was not discussed in your question. You have an example; an answer was made to you. - ЫЫЫ

    Googlanul for your question found GROUP_CONCAT time from the date, if it is recorded there. Well, WHERE sign.

     SELECT news_added, GROUP_CONCAT(news_title) FROM News GROUP BY news_added