MySQL Split Query into Multiple Html Tables

Question

I'm using a basic ORDER BY name query from MySQL and i'm attempting to split the data Alphabetically into individual html tables / or add a line break... so for example it currently outputs like so:

Name
adam
angel
Bert
Bob
Chloe
Courtney

But i would like the output to be....

Name
adam
angel

Bert
Bob

Chloe
Courtney

What's the best way to add a line break or blank cell to make this happen?

I'm simply echoing the data at the moment:

$sql = "SELECT name FROM test ORDER BY name ASC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo " <tr>";
        echo "<li>" . $row['name'] . "</li>";
        echo " </tr>";
    }
} else {
    echo "0 results";
}

Did you try to change <tr> by <ul> ?

Melvyn Marigny
– Melvyn Marigny

2020-04-20 14:38:11 +00:00
Commented Apr 20, 2020 at 14:38 — Melvyn Marigny
– Melvyn Marigny, Commented Apr 20, 2020 at 14:38

Josh · Accepted Answer · 2020-04-20 14:38:44Z

1

Something like this should work

$sql = "SELECT name FROM test ORDER BY name ASC";
$result = $conn->query($sql);    
$previousFirstChar = NULL;

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        if(!is_null($previousFirstChar) && substr($row['name'], 0, 1) != $previousFirstChar){
            echo "<tr></tr>";
        }
        echo " <tr>";
        echo "<li>" . $row['name'] . "</li>";
        echo " </tr>";
        $previousFirstChar = substr($row['name'], 0, 1);
    }
} else {
    echo "0 results";
}

What we're doing here is keeping track of the last first character used, and if the current first character is different, we add an empty <tr> tag to the output. The is_null check is to prevent there being an empty <tr> at the start of the query output.

As an unrelated aside, I suspect you either want <td> instead of <li>, or <ul> instead of <tr>. You're combining list and table tags in this snippet.

answered Apr 20, 2020 at 14:38

Josh

4322 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nigel Ren Over a year ago

A shorter method to get one character from a string would be to use it as an array $row['name'][0]

Josh Over a year ago

Absolutely, but I think it's a little less readable. I think it's a matter of preference - I doubt there's a performance difference in most practical use cases.

Realitätsverlust · Accepted Answer · 2020-04-20 14:39:00Z

First of all, your markup is wrong. A table has the following markup:

<table>
    <tr>
        <td>Row1</td>
    </tr>
    <tr>
        <td>Row2</td>
    </tr>
</table>

Second: The easiest approach would be to create a new table every time the character changes

$currentCharacter = 'a';

echo "<table>";

while($row = $result->fetch_assoc()) {
    echo " <tr>";
    echo "<td>" . $row['name'] . "</td>";
    echo " </tr>";

    if($currentCharacter != $row[0][0]) {
        $currentCharacter = $row[0][0];
        echo "</table></table>";
    }
}

echo "</table>";

In PHP, you can treat strings as arrays to get the element at the specific position. So the if condition is only executed if the first letter of the name is not what it was before. This approach only works if the content of the array is already sorted, which you are doing, so it's fine.

A much cleaner approach would be to sort the result of the query into an array with 26 elements from a to z, so you'd only need 2 nested foreach loops. That's a little bit more work tho.

Collectives™ on Stack Overflow

MySQL Split Query into Multiple Html Tables

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related