CODEWITHSUNDEEP
phpmysqlforeach
Stan
Stan

Reputation: 963

Concatenating data in MySQL

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?

Upvotes: 1

Views: 45

Answers (1)

AJReading
AJReading

Reputation: 1213

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

Upvotes: 4

Related Questions