CODEWITHSUNDEEP
phpmysql
RCNeil
RCNeil

Reputation: 8759

PHP Loop Detect First Instance of each Letter of MySQL results

If I query a database with PHP using MySQLi, how can I detect the first instance of each letter of the alphabet?

If I have a table like -

ID | name
1    Allen
2    John
3    Sally
4    William

and I query 

SELECT * FROM table ORDER BY name ASC

Can I have something in my loop that says "if this is the first time you've seen the string in name start with the letter A", echo <a id="a"></a> to create an anchor tag? Then it will proceed to do the same for B,C,D,E,F,G, etc.. Then I can create an alphabetical legend.

Here is my query and loop:

$query = "SELECT * FROM table ORDER BY name ASC";
$result = $db->query($query);
$num = $result->num_rows;
for($i=0; $i < $num; $i++){
    $row = $result->fetch_object();
      //IF THIS IS THE FIRST TIME SEEING $row->name START 
      //WITH A DIFFERENT LETTER OF THE ALPHABET ECHO SOMETHING...
    echo $row->name;
 }

Upvotes: 0

Views: 202

Answers (2)

FuzzyTree
FuzzyTree

Reputation: 32392

Since your results are ordered by name, if the first letter of the current row doesn't match the first letter of the previous row, then you know it's the first time you've seen it.

$previousLetter = '';

for($i=0; $i < $num; $i++){
    $row = $result->fetch_object();        
    if($row->name[0] != $previousLetter) {
        echo "<a id='" . $row->name[0] . "'></a>";
        $previousLetter = $row->name[0];
    }    
    echo $row->name;
 }

Upvotes: 1

Barmar
Barmar

Reputation: 781068

Create an associative array that records which letters you've seen.

$letters_seen = array();

while ($row = $result->fetch_object()) {
    $letter = substr($row->name, 0, 1);
    if (!isset($letters_seen[$letter])) {
        // This is the first time seeing this initial letter
        $letters_seen[$letter] = true;
        echo "<a id='$letter'>$letter</a>";
    }
    echo $row->name;
}

Upvotes: 2

Related Questions