user6618037 user6618037 - 3 months ago 9
MySQL Question

Selecting the highest ID from a database table with PHP

I am trying to select one row from the database with the highest ID and echo it out to the page in a table.

I can run the query manually and it works correctly but when I try to do it dynamically via PHP; it does not work. What is wrong with my code?

<?php
$sql_query = "SELECT * FROM single_user_orders ORDER BY order_id DESC LIMIT 1";
$result = mysqli_query($dbconfig, $sql_query);
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
$count = mysqli_num_rows($result);

echo '<div class="row" style="margin-top: 30px;">';
echo '<div class="col-md-12">';
echo '<table class="table">';
if($count > 1) {
echo '<tr>';
echo '<th>Order ID</th>';
echo '<th>Status</th> ';
echo '<th>Order Total</th>';
echo '<th>Order Description</th>';
echo '</tr>';
while ($row = mysqli_fetch_array($result)) {
echo '<tr>';
echo '<td>'. $row['order_id'] .'</td>';
echo '<td>'. $row['status'] .'</td> ';
echo '<td>'. $row['order_total'] .'</td>';
echo '<td>'. $row['order_description'] .'</td>';
echo '</tr>';
}
}
echo '</table>';
echo '</div>';
echo '</div>';
?>

Answer

You are using while loop for fetching data again where as there is no need to fetch data again as it is already been fetched using $row = mysqli_fetch_array($result, MYSQLI_ASSOC); at the beginning.

Try following

<?php
$sql_query = "SELECT * FROM single_user_orders ORDER BY order_id DESC LIMIT 1";
$result = mysqli_query($dbconfig, $sql_query);
$row = mysqli_fetch_assoc($result);
$count = mysqli_num_rows($result);

echo '<div class="row" style="margin-top: 30px;">';
echo '<div class="col-md-12">';
echo '<table class="table">';
if ($count >= 1)
{
    echo '<tr>';
    echo '<th>Order ID</th>';
    echo '<th>Status</th> ';
    echo '<th>Order Total</th>';
    echo '<th>Order Description</th>';
    echo '</tr>';
    // Removed while loop as data is already fetched
    // you just need to echo the values now
    echo '<tr>';
    echo '<td>' . $row['order_id'] . '</td>';
    echo '<td>' . $row['status'] . '</td> ';
    echo '<td>' . $row['order_total'] . '</td>';
    echo '<td>' . $row['order_description'] . '</td>';
    echo '</tr>';
}
echo '</table>';
echo '</div>';
echo '</div>';
?>

while loop is not required here as only one row is being retried by using LIMIT 1. if more than one row is being retrieved, in that case while loop will do the trick for you.