Concatenating data in MySQL

Question

I have the following data in my database:

initial   |    word
---------------------------
   E      |    Example1
   E      |    Example2
   N      |    Nextexample1

The desired output:

E : Example1, Example2
N : Nextexample1

So for each initial, it should display all the words starting with that initial.

Output at this moment:

E : Example1
E : Example2
N : Nextexample1

My code at this moment:

<?php
    $pdo = new PDO('mysql:host=...');           
    $sql = "SELECT DISTINCT initial, word FROM `exampletable` ORDER by initial ASC";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();

    if($stmtrecords = $stmt->fetchall(PDO::FETCH_ASSOC))
    {

    foreach($stmtrecords as $result)
    {
?>

<?php echo $result['initial'];?> : <?php echo $result['word'];?>

The question:

I completely understand why I'm getting the wrong output but don't know how to solve it. I guess I need something like a foreach loop within a foreach loop to get the right output, but how to link the words to the correct initial?

AJReading · Accepted Answer · 2015-04-24 20:53:56Z

4

You can do this with a single SQL query.

SELECT `initial`, GROUP_CONCAT(`word` SEPARATOR ', ') as `output` 
    FROM `table` GROUP BY `initial` ORDER BY `initial` ASC

In this query you are asking MySQL to group the records by initial and you use the GROUP_CONCAT() function to aggregate each grouping into a single single row.

Using example data

initial | word
--------+--------------
E       | Example1
E       | Example2
Z       | Zebra
N       | NextExample
O       | OtherExample
O       | OtherExample2

Would return:

initial | output
--------+--------------
E       | Example1, Example2
N       | NextExample
O       | OtherExample, OtherExample2
Z       | Zebra

answered Apr 24, 2015 at 20:53

AJReading

1,22121 silver badges35 bronze badges

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

2 Comments

Stan Over a year ago

Thanks, that helps a lot! Just one more question: if I would decide to put each word matching the initial into a separate div, any idea how I can do that?

nietonfir Over a year ago

@Stan Obviously through a loop, but you might want to ask a second question. ;-)

Collectives™ on Stack Overflow

Concatenating data in MySQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related