CODEWITHSUNDEEP
phpmysql
dan070
dan070

Reputation: 356

Sort data from mysql table in alphabetical order under different headings

I'm trying to sort a list of names with allocated links coming from mysql table.
Would like it to look something like this in html:

    <h3>A</h3>
    <ul>
       <li>Andrea</li>
       <li>Arron</li>
    </ul>

So far I was able to list letters and names in alphabetical order, but I don't know how to make a loop that would list all names starting with 'A' under heading 'A' etc.

    <?php

    $connection = mysqli_connect('localhost', 'root', '', 'database');

    $query = "SELECT * FROM users ORDER BY name";
    $result = $connection->query($query);

    $azRange = range('A', 'Z');

    foreach ($azRange as $letter){
        echo "<h3>" . $letter . "</h3>";
    }

    echo "<ul>";

    while($row = $result->fetch_array()){
        echo "<li><a href=\"" . $row['link'] . "\">" . $row['name'] . "</a></li>";
    }

    echo "</ul>";

    ?>

Thank you.

Upvotes: 0

Views: 1757

Answers (4)

Paul Spiegel
Paul Spiegel

Reputation: 31772

I would split the data first:

$query = "SELECT * FROM users ORDER BY name";
$result = $connection->query($query);

$data = array();
while($row = $result->fetch_array()){
    $firstLetter = strtoupper($row['name'][0]);
    $data[$firstLetter][] = $row;
}

foreach ($data as $letter => $letterData){
    echo "<h3>" . $letter . "</h3>";
    echo "<ul>";
    foreach ($letterData as $row) {
        echo '<li><a href="' . $row['link'] . '">' . $row['name'] . '</a></li>';
    }
    echo "</ul>";
}

There are two reasons, why i'd do so:

  • Data processing and output are separated
  • There is no condition used (if()). Thus the code is less complex.

Note: To be safe with multibyte strings you should use something like

$firstLetter = mb_strtoupper(mb_substr($row['name'], 0, 1,'UTF8'));

With PDO that would be a bit shorter (avoids the first loop):

$query = "SELECT UPPER(LEFT(name,1)) as first_letter, u.* FROM users u ORDER BY name";
$data = $connection->query($query)->fetchAll(PDO::FETCH_ASSOC|PDO::FETCH_GROUP);

Upvotes: 0

German Lashevich
German Lashevich

Reputation: 2433

Try this:

<?php

$connection = mysqli_connect('localhost', 'root', '', 'database');

$query = "SELECT * FROM users ORDER BY name";
$result = $connection->query($query);
$names = $result->fetch_all(MYSQLI_ASSOC);

$idx = 0;
$namesCount = count($names);

$azRange = range('A', 'Z');
foreach ($azRange as $letter) {
    echo "<h3>" . $letter . "</h3>";

    echo "<ul>";

    for (; $idx < $namesCount; ++$idx) {
        $row = $names[$idx];
        if (strtoupper($row['name'][0]) === $letter) {
            echo "<li><a href=\"" . $row['link'] . "\">" . $row['name'] . "</a></li>";
        } else {
            break;
        }
    }

    echo "</ul>";
}

Upvotes: 2

u_mulder
u_mulder

Reputation: 54831

You need to check first letter of every $row['name'] and control the change of this letter. So, if first letter was 'A' for several rows, and then it has changed to 'B' - this means that you need to echo this new letter (B). 

echo "<ul>";
// init first letter with an empty value
$first_letter = "";

while($row = $result->fetch_array()){
    // get first letter of current name
    $first_letter_of_name = substr($row['name'], 0, 1);
    if ($first_letter_of_name != $first_letter) {
        // first letters differ, this 
        // means that new letter is here

        // Echo this new letter
        echo '<li>Letter: <b>' . $first_letter . '</b></li>'
        // Change flag's value
        $first_letter = $first_letter_of_name;
    }
    echo "<li><a href=\"" . $row['link'] . "\">" . $row['name'] . "</a></li>";
}

echo "</ul>";

Upvotes: 1

SolE-pAt21
SolE-pAt21

Reputation: 38

use a switch statement; such that when the letter passed from the database matches A, run a certain code ; NOTE collect the first character from the data gotten from the database(you can use this php function strchr(string, charToFind);) and compare using the switch statement; 

$variable

switch($variable){
case 'A':
#output here 
break

case 'a':
#output here 
break
}

Upvotes: -1

Related Questions