mysql query and output

Question

I need to know how to write a query, or how this could be done.

I have a table events with structure say:

|  name   |     date    |            title           |
|  blue   |  2014-01-28 | some title 1               |
|  blue   |  2014-01-28 | some title 2               |
|  blue   |  2014-01-28 | some title 3               |
|  blue   |  2014-01-28 | some title 4               |
|  red    |  2014-01-29 | some title 1               |
|  red    |  2014-01-29 | some title 2               |
|  red    |  2014-01-29 | some title 3               |
|  red    |  2014-01-29 | some title 4               |

What I want to do, is select by each date (group?) and then list them after so in the php it would list like

<h1>2014-01-28</h1>
<ul>
   <li>some title 1</li>
   <li>some title 2</li>
   <li>some title 3</li>
   <li>some title 4</li>
</ul>

<h1>2014-01-29</h1>
<ul>
   <li>some title 1</li>
   <li>some title 2</li>
   <li>some title 3</li>
   <li>some title 4</li>
</ul>

something of that nature.. I can take it from there.

Answer 1

You can try this query

SELECT date, GROUP_CONCAT(title) As title
    FROM table GROUP BY date

This will give you output like below

date       | title

2014-01-28 | some title 1,some title 2,some title 3,some title 4

Then parse the result using PHP.

Note: GROUP_CONCAT() has the limitation of 1024 bytes. if your title length is more please consider before using the GROUP_CONCAT function

Ref

http://www.mysqlperformanceblog.com/2013/10/22/the-power-of-mysqls-group_concat/