Alyssa-Charlie Posted February 26, 2023 Share Posted 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> Quote Link to comment https://forums.phpfreaks.com/topic/315953-crud-read-an-overview-of-all-the-members/ Share on other sites More sharing options...
Solution Barand Posted February 26, 2023 Solution Share Posted 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. 1 Quote Link to comment https://forums.phpfreaks.com/topic/315953-crud-read-an-overview-of-all-the-members/#findComment-1606052 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.