Correct use of foreach loop within foreach loop

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 that initial and all the words starting with that initial. Important for me: each word should be displayed in a separate div so no GROUP_CONCAT(wordSEPARATOR ', ') suggestions please.

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)
    {
?>

<div>
    <?php echo $result['initial'];?>
</div>
<br>
<div>
    <?php echo $result['word'];?>
</div>
<br><br>

The question:

Obviously I need an extra loop to solve this problem but I don't know how to do that exactly?

Answer 1

You don't actually need an extra loop, you just need a little extra logic in your loop. Since the query sorts the data by initial, you just need to check when $result['initial'] changes in the loop and only output the header at that time.

$initial = null;

foreach ($stmtrecords as $result) {
    if ($result['initial'] !== $initial) {
        $initial = $result['initial'];
        // output header div
    }
    // output word
}

Just a word of caution: In your code above you are embedding data from the database directly into HTML. That data should be escaped before inserting in HTML. See phptherightway.com and the Zend Escaper for more information on how to properly escape data.

Answer 2

I would use group_concat as

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

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

    foreach($stmtrecords as $result)
    {
       $words = $result['words'];
       $words_array = array();
       if(strlen($words) > 1) {
         $words_array = explode(',',$words);
       }
?>

<div>
    <?php echo $result['initial'];?>
</div>
<br>
<div>
    <?php
     if (count($words_array) > 0 ){
       foreach($words_array as $val){
         echo $val.'<br />';
       }
     }
    ?>
    <?php echo $result['word'];?>
</div>