m3tsys m3tsys - 1 month ago 7
MySQL Question

PHP MySQL arrange html output three by three, order by first letter of group

This is the code i have by now:

<?php

$sql = DB::query("SELECT name FROM stores ORDER BY name");
foreach ($sql as $row){ $name = $row["name"]; }

// don't know how to get the current letter
$current_letter = '?';

// Set the start at 1
$i = 1;
foreach ($sql as $row){
// If the loop hits 1, add <div>
if($i == 1)

echo '<div class="store-wrapper">'.PHP_EOL;

echo '<div class="store-letter">Store Letter - '. $current_letter .'</div>'.PHP_EOL;
echo '<ul>'.PHP_EOL;

echo $id = "\t<li>".$name.'</li>'.PHP_EOL;
// If the loop hits 3, add </div>
if($i == 3) {
echo '</ul>'.PHP_EOL;
echo '</div>'.PHP_EOL; // end .store-wrapper
// Reset the counter
$i = 0;
}
// Increment up
$i++;
}

?>


Is there something wrong with my code? This is the code output that i am trying to achieve:

<div class="store-wrapper">
<div class="store-letter">Store Letter - A</div>
<ul>
<li>ajhgjgj</li>
<li>abgjkhjh</li>
<li>avbnvnmnm</li>
</ul>
</div>

<div class="store-wrapper">
<div class="store-letter">Store Letter - B</div>
<ul>
<li>bgfghfj</li>
<li>bhgjhgj</li>
<li>bvkjhgkj</li>
</ul>
</div>

<div class="store-wrapper">
<div class="store-letter">Store Letter - C</div>
<ul>
<li>cgfghfj</li>
<li>chgjhgj</li>
<li>cvkjhgkj</li>
</ul>
</div>


Another thing is that i don't really know how to get the first letter of each group.

The problem with the code is that the output does not display as the upper example and the groups don't jumb to the next available letter when maximum 3
<li>
are reached.

What i am doing wrong?

Answer

You don't need the first foreach loop. It just repeatedly assigns the same variable $name. You can do this in the loop that echoes the DIVs.

You can get the first letter of a string with $name[0].

Rather than testing for $i == 3 to know when to close the UL and DIV, test for $i > 3 to skip printing the names. Otherwise, if a letter has only 1 or 2 names in its group, you'll never close the group.

$current_letter = null;
foreach ($sql as $row) {
    $name = $row['name'];
    $first_letter = $name[0];
    if ($first_letter != $current_letter) {
        $i = 1;
        if ($current_letter) { // close the previous DIV before starting new one
            echo '</ul>'.PHP_EOL;
            echo '</div>'.PHP_EOL;
        }
        $current_letter = $first_letter;
        echo '<div class="store-wrapper">'.PHP_EOL;
        echo '<div class="store-letter">Store Letter - '. $current_letter .'</div>'.PHP_EOL;
        echo '<ul>'.PHP_EOL;
    }
    if ($i > 3) {
        continue;
    }
    echo "\t<li>".$name.'</li>'.PHP_EOL;
    $i++;
}
if ($current_letter) { // close the last DIV
    echo '</ul>'.PHP_EOL;
    echo '</div>'.PHP_EOL;
}

DEMO
output