PHP无法正确获取插入的最后一个ID

Question

I have two possible solutions for my my probrem i want to echo last id inserted to know how many people fill the form, but it affects the data on the table below. Note: this doesn't affect the database itself, just the output from the browser.

connect.php

@$db = new mysqli('127.0.0.1', 'blop', 'blop', 'blop');

if ($db->connect_errno) {
    die ('Sorry, we are having some problems.');
}

displaying the results:

function returnData() {
    global $db;

    echo ('<style> td {border: 1px solid #000; background:#ed8043;} th {border:1px solid #000; background:#fff</style>
                    <table style="width:100%; text-align:center;">
                        <tr>
                        <th>Line</th>
                        <th>id</th>
                        <th>Name</th>
                        <th>Email</th>
                        <th>Visited</th>
                        </tr>');


    $result = $db->query("SELECT * FROM `people` ORDER BY `created` DESC");
    $count = $result->num_rows;
    echo 'Number of visits: ' .$count.'<br><br>';

    /*$visits1 = $result->fetch_assoc();
    echo $visits1['id']. '<br>';
    $visits2 = $db->insert_id;
    echo $visits2. '<br>';*/

    $row_num = 1;
        while ($row = $result->fetch_object()) {

            echo ('
                    <tr>
                    <td>' .$row_num. '</td>
                    <td>' .$row->id. '</td>
                    <td>' .$row->first_name. '</td>
                    <td>' .$row->mail. '</td>
                    <td>' .$row->created.'</td>
                    </tr>'
                );

            $row_num++;
        }
            echo ('</table><br>');
            $result->free();
}

The problem is that when i try like this: 1.

$result = $db->query("SELECT * FROM `people` ORDER BY `created` DESC");
$visits1 = $result->fetch_assoc();
echo $visits1['id']. '<br>';

i actualy have one more id than what appears on the table like the image below show:

As you can see i have "756" witch is fine, because it's the last id inserted but i don't want the respective row (with the id 756), it disapeared from the table below it. When i coment the code above it works fine the first row is the last id inserted.

I have an alternative code to resolve this, the problem is this doesn't work either: 2.

$result = $db->query("SELECT * FROM `people` ORDER BY `created` DESC");
$visits2 = $db->insert_id;
echo $visits2. '<br>';

This time the table it's correct, it shows all rows but the number of total id's it's 0.

Answer 1

That's what fetch_* methods are doing, and that's what you need so you can iterate over them. It's simple logic of programming loops. To execute a function n times, you put it in a loop, or call it manually n times. You call it manually one time, so you have already fetched the first item from this resultset. Putting it in a while() loop will fetch the remaining elements from the resultset.

To avoid this, you'd better switch to already fetched resultset and manipulate over it.

Right now, you are breaking the single responsibility principle, because returnData gets data, makes HTML and so on.

Make a function getData() with the query, and then use it with foreach to get all the resultset.

Use it only once to get the last Id (first).

function getData() {
     global $db;
     $result = $db->query("SELECT .......");
     while ($row = $result->fetch_object()) {
          $rows[] = $row;
     }
     return $rows;
}

Now in returnData() you can use

getData()[0]->id

To fetch the last id, as you can also iterate through the method without losing the resultset:

foreach (getData() as $data) {
    echo $data->id;
}

You'd better make function for each thing you will need in this application. Fetching the last id, fetching the whole resultset, getting the rowcount, etc. It's rarely good practice to make it from a single query/method.

To be honest, there are a lot of issues to be fixed in your code, starting from global variables, ending to dropping the functional/procedural style in favor of OOP