Numbering the array rows

Question

I have selected the whole table with mysql, there are records of sold tickets. When I am writing them out with foreach, they are showing correctly from the newest sold ticket at the top to the oldest purchase at the bottom. The problem is that the IDs do not look good. I have done many test purchases before launching it and the id numbers are starting from 50 now. I would like to keep it as it is now but only number the records with normal numbers from 1. The problem is that the highest number must be at the top since the latest record is there on top. Could somebody advice me on how to do this please?

Answer 1

to display with custom index in descending order using php

$sql ="SELECT * FROM  tbl_purchases ORDER BY id DESC";
if ($result = $link->query($sql)) {
    $index = mysqli_num_rows($result);
    echo "<table border='1'>";
    while($row = mysqli_fetch_assoc($result)) {
        echo "<tr>".
                "<td>{$index}</td/>".
                "<td>{$row['id']}</td/>".
                "<td>{$row['name']}</td>".
             "</tr>";
        $index--;
    }
    echo "</table>";
}

for ascending index

$sql ="SELECT * FROM  tbl_purchases ORDER BY id DESC";
    if ($result = $link->query($sql)) {
        $index = 1;
        echo "<table border='1'>";
        while($row = mysqli_fetch_assoc($result)) {
            echo "<tr>".
                    "<td>{$index}</td/>".
                    "<td>{$row['id']}</td/>".
                    "<td>{$row['name']}</td>".
                 "</tr>";
            $index++;
        }
        echo "</table>";
    }

==================

To reset the auto incremental column in DB (start again from 1)

For MYISAM

ALTER TABLE tbl_purchases AUTO_INCREMENT = 1;

For INNO DB

SET  @num := 0;
UPDATE tbl_purchases SET id = @num := (@num+1);
ALTER TABLE tbl_purchases AUTO_INCREMENT =1;

Answer 2

Usually, when you're fetching from DB, the ID column which is usually on auto-increment is not reliable, so when fetching, you should run your own auto-increment. i.e.

<?php foreach($rows as $index => $row): ?>
    <table>
      <td><?php echo $index + 1 ?></td>

Answer 3

When you use while loop you have to create one variable before start loop and increatement it by 1 in loop and use it as row number :)

$sql ="SELECT * FROM  tbl_purchases ORDER BY id DESC";
if ($result = $link->query($sql)) {
    echo "<table border='1'>";
    $line_counter=mysqli_num_rows($result);
    while($row = mysqli_fetch_assoc($result)) {
        echo "<tr>".
                "<td>{$line_counter}</td/>".
                "<td>{$row['id']}</td/>".
                "<td>{$row['name']}</td>".
             "</tr>";
        $line_counter--;
    }
    echo "</table>";
}