Skip to content
Advertisement

SQL select multiple arrays and populate one table

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>&#127820;<?= 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>&#127820;<?= 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>&#127820;<?= 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>&#127820;<?= 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.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement