David David - 6 months ago 53
SQL Question

MySQL, PHP: split up output table in categories

Hello guys,
I've a problem and I hope anybody can help me.

My MySQL database looks like this:

database

I would like to output these data in a table this way:

output

My actual code looks like this:

<html>
<body>
<table>

<?php
$root = realpath($_SERVER["DOCUMENT_ROOT"]);
include "$root/config.php";

$stmt = $pdo->prepare('SELECT * FROM TestTable ORDER BY name');
$stmt->execute();


$results = $stmt->fetchAll();


if (empty($results)) {
echo 'Error!';
} else {
foreach( $results as $row ) {
echo
"
<tr>
<td class=\"left\">".$row['name']."</td>
<td class=\"right\">".$row['address']."</td>
</tr>
";
}
}

?>
</table>
</body>
</html>


Output looks like this actual:

actual output

What to do to have the name of the profession always on top, when the profession is not the same like in the row before?

To sove this issue I've this code:

<?php
$mysqli = new mysqli(host,username,password,db_name);

$sql = "SELECT * FROM TestTable ORDER BY profession, name";
$array = [];
$html = "";

if ($result = $mysqli->query($sql)) {
while($row = $result->fetch_assoc()) {
if (!isset($array[$row['profession']])) {
$array[$row['profession']] = array();
}
$array[$row['profession']][] = $row['name'];
}
}



foreach ($array as $profession => $name) {
$html .= '<p style="font-weight:bold">'.$profession.'</p>';
foreach ($name as $name) {
$html .= '<p class="name">'.$name.'</p>';
}
}
echo $html;

?>


output with second code

But I need the street in this table too. And I need this code with PDO not MySQLi. I tried to manage this, but without success.

Can anybody help me please?

Greetings,
David.

Answer

try something like this:

$stmt = $pdo->prepare('SELECT * FROM TestTable ORDER BY profession, name');
$stmt->execute();

$results = $stmt->fetchAll();

if (empty($results)) {
   echo 'Error!'; 
} else {
$profession = $row['profession'];
foreach( $results as $row ) {
  if ($row['profession'] <>$profession) {
       echo "<tr><td>". $row['profession']."</td></tr>";
     }
    echo "<tr>
    <td class=\"left\">".$row['name']."</td>
    <td class=\"right\">".$row['address']."</td>
    </tr>
    ";
   $profession = $row['profession'];
}
}