I am trying to pupulate 1 table with results from 2 select querys but from the same table. It is not working, one pupulates the other one shows only one result.. Is it posible to do this in one select as i think the problem is with the use of two fetch assoc.
Question: How do i get the result of both of these querys presented in the table i created?
<table align="left" style="width: auto; min-width: 700px; margin-bottom:20px;" class="deftable"> <thead><tr> <th colspan="4">Top 10 users</th> </tr> </thead> <thead><tr> <th colspan="2">Top 10 Richest users</th> <th colspan="2">Top 10 Bananaslappers</th> </tr> </thead> <thead> <tr><th>Name</th> <th>Bananas</th> <th>Name</th> <th>Total slapped</th> </tr></thead><tbody> <?php $stmt88 = $mysqli->prepare("SELECT naam,geld FROM `gebruikers` ORDER BY geld DESC LIMIT 10 "); $stmt88->execute(); $result = $stmt88->get_result(); //only works when nd_mysli is set on the server! $stmt88->close(); while ($rowrich = $result->fetch_assoc()) { $stmt89 = $mysqli->prepare("SELECT naam,user_amountslapped FROM `gebruikers` ORDER BY user_amountslapped DESC LIMIT 10 "); $stmt89->execute(); $result2 = $stmt89->get_result(); //only works when nd_mysli is set on the server! $stmt89->close(); while ($rowrich2 = $result2->fetch_assoc()) { ?> <tr><td > <a class="online" href="profile.php?username=<?= htmlspecialchars($rowrich['naam']) ?>"><b><?= htmlspecialchars($rowrich['naam']) ?></b></a></td> <td>🍌<?= htmlspecialchars(number_format($rowrich['geld'],0,",",".")) ?></td> <td > <a class="online" href="profile.php?username=<?= htmlspecialchars($rowrich2['naam']) ?>"><b><?= htmlspecialchars($rowrich2['naam']) ?></b></a></td> <td>🍌<?= htmlspecialchars(number_format($rowrich2['user_amountslapped'],0,",",".")) ?></td> </tr> </tbody> <? } }?></table><br>
Advertisement
Answer
You shouldn’t have nested loops, that’s creating a cross product between the two results, not processing them in parallel.
You should have a single loop that fetches a row from each query.
<?php $stmt88 = $mysqli->prepare("SELECT naam,geld FROM `gebruikers` ORDER BY geld DESC LIMIT 10 "); $stmt88->execute(); $result = $stmt88->get_result(); //only works when nd_mysli is set on the server! $stmt88->close(); $stmt89 = $mysqli->prepare("SELECT naam,user_amountslapped FROM `gebruikers` ORDER BY user_amountslapped DESC LIMIT 10 "); $stmt89->execute(); $result2 = $stmt89->get_result(); //only works when nd_mysli is set on the server! $stmt89->close(); while (($rowrich = $result->fetch_assoc()) && ($rowrich2 = $result2->fetch_assoc())) { ?> <tr><td > <a class="online" href="profile.php?username=<?= htmlspecialchars($rowrich['naam']) ?>"><b><?= htmlspecialchars($rowrich['naam']) ?></b></a></td> <td>🍌<?= htmlspecialchars(number_format($rowrich['geld'],0,",",".")) ?></td> <td > <a class="online" href="profile.php?username=<?= htmlspecialchars($rowrich2['naam']) ?>"><b><?= htmlspecialchars($rowrich2['naam']) ?></b></a></td> <td>🍌<?= htmlspecialchars(number_format($rowrich2['user_amountslapped'],0,",",".")) ?></td> </tr> </tbody> <? } ?></table><br>
Note that if either of the queries returns less than 10 rows, the table will stop at the shorter length. If you want the longer length, use ||
in the while
condition, and then check whether $rowrich
or $rowrich2
is empty before outputting those columns.