CRUD read. An overview of all the members

Alyssa-Charlie · February 26, 2023

I have an overview of all members here. The only problem I have is that there are also members with multiple phone numbers and multiple email addresses.

Actually I should run the query one by one but I can't do this. And how can I order them by Lidnummer?

<!DOCTYPE html>
<html>
<head>
<title>View Page</title>
<link rel="stylesheet"
href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<h2>Leden</h2>
<table class="table">
<thead>
<tr>
<th>Lidnummer</th>
<th>Naam</th>
<th>Voornaam</th>
<th>Adres</th>
<th>Huisnummer</th>
<th>Postcode</th>
<th>Woonplaats</th>
<th>Telefoonnummer</th>
<th>Emailadres</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php

require_once 'login.php';
$conn = new mysqli ($hn, $un, $pw, $db);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql= "SELECT lid.Lidnummer, lid.Naam, lid.Voornaam, lid.Huisnummer, lid.Postcode, postcode.Adres, postcode.Woonplaats,
telefoonnummers.Telefoonnummer, email.Emailadres
FROM lid, postcode, telefoonnummers, email
WHERE lid.Postcode = postcode.Postcode
AND lid.Lidnummer = email.Lidnummer";

$result = $conn->query($sql);
if (!$result) die("Fatal Error");

$rows = $result->num_rows;

for ($j = 0 ; $j < $rows ; ++$j)
{

if($result->num_rows>0)
{
while($row=$result->fetch_assoc())
?>

<tr>
<td><?php echo $row['Lidnummer']?></td>
<td><?php echo $row['Naam']?></td>
<td><?php echo $row['Voornaam']?></td>
<td><?php echo $row['Adres']?></td>
<td><?php echo $row['Huisnummer']?></td>
<td><?php echo $row['Postcode']?></td>
<td><?php echo $row['Woonplaats']?></td>
<td><?php echo $row['Emailadres']?></td>
<td><?php echo $row['Telefoonnummers']?></td>

<td><a class="btn btn-info" href="update.php?id=<?php echo $row['Lidnummer']?>
">Edit</a> <a class="btn btn-danger" href="verwijderleden.php?id=<?php echo $row['Lidnummer'];?>
">Delete</a></td><td>
</tr>
<?php
}
}
?>

</tbody>
</table>
</div>
</body>
</html>

Barand · February 26, 2023

If you are happy to display the multiple numbers and emails as, say, comma-separated lists, then you can use GROUP_CONCAT on those columns and GROUP BY Lidnummer.

SELECT lid.Lidnummer
     , lid.Naam
     , lid.Voornaam
     , lid.Huisnummer
     , lid.Postcode
     , postcode.Adres
     , postcode.Woonplaats
     , GROUP_CONCAT(telefoonnummers.Telefoonnummer SEPARATOR ', ') as Telefoonnummer
     , GROUP_CONCAT(email.Emailadres SEPARATOR ', ') as Emailadres 
FROM lid
     INNER JOIN
       postcode ON lid.Postcode = postcode.Postcode
     INNER JOIN 
       telefoonnummers lid.Lidnummer = telefoonnummers.Lidnummer
     INNER JOIN
       email ON lid.Lidnummer = email.Lidnummer
GROUP BY lid.Lidnummer;

Use explicit joins and not "FROM A, B, C WHERE..."

When posting code in the forum, use the <> button to create a code block and specify the code type.

Sign In

CRUD read. An overview of all the members

Recommended Posts

Alyssa-Charlie

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information