How to number a fetched rows from MySQL with a limit of 10 in an ascending order

I have this table that is fetched from MySQL but I have a column on my website where it says id number which is supposed to number the rows but I wanted to sort the rows so the ones with the highest value shows first but now the id gets scattered because the one with the highest value is not number one and I want that to be although I can go change it manually. But I want this to be automated. So basically how do I number the rows so that no matter what the row is it just shows the number it is on the table?

so this is my code

<?php
include_once 'connection.php';
$result = mysqli_query($con,"SELECT * FROM Support ORDER BY amount DESC LIMIT 10");
?>
<?php
if (mysqli_num_rows($result) > 0) {
?>
<table class='table table-hover table-custom spacing8'>
<tr>
<td>ID</td>
<td>Username</td>
<td>Amount</td>
<td>Status</td>
<td>Country</td>
</tr>
<?php
$i=0;
while($row = mysqli_fetch_array($result)) {
?>
<tr>
<td><?php echo $row["sn"]; ?></td>
<td> <div class="d-flex align-items-center">
                                    <div class="icon-user" data-toggle="tooltip" data-placement="top" title="Country"></div>
                                    <div class="ml-3">
                                        <a href="#"><?php echo $row["username"]; ?></a>
                                        
</div>
</div></td>
<td>$<?php echo $row["amount"]; ?></td>
<td ><span class="badge badge-success ml-0 mr-0"><?php echo $row["status"]; ?></span></td>
<td><?php echo $row["country"]; ?></td>
</tr>
<?php
$i++;
}
?>
</table>
<?php
}
else{
echo "No result found";
}
?>

Answer

You could modify your query to

SELECT * FROM
 (
  SELECT * FROM Support 
  ORDER BY amount DESC 
  LIMIT 10
 )
ORDER BY id ASC;

which would run the 10 highest values of amount, ordered by id.
NB. ORDER BY is ascending by default. I only added ASC to be unambiguous.

Advertisement

Answer