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.

lol, you don't want to know.. was going to do a query by grouping the dates, and then another query to list by date.. but would rather 1 query. — user2639176
– user2639176, Commented Jan 28, 2014 at 7:54

Sundar · Accepted Answer · 2014-01-28 08:01:10Z

2

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/

answered Jan 28, 2014 at 8:01

Sundar

4,6606 gold badges40 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql query and output

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related