0

Hello!
I have a little problem with a For Loop, this is a For Loop in my script:

        $last_id = $conn->lastInsertId();

        for($a = $last_id ; $a > 0 ; $a--){
            code....
        }

But it doesn't work because of ($a = $last_id ;, the variable $a can't be set to the value of $last_id, but if set this for example ($a = 5 ; it will work.

Edited by Niloofar24

3
Contributors
7
Replies
33
Views
2 Years
Discussion Span
Last Post by Niloofar24
0
<?php

    include('connection.php');
    try {
        $last_id = $conn->lastInsertId();

        for($a = $last_id ; $a > 0 ; $a--){

            $stmt = $conn->prepare("SELECT ID, Title, Author, Content FROM Posts WHERE ID=(:id)");
            $stmt->bindParam(':id', $a, PDO::PARAM_INT);
            $stmt->execute();

            $result = $stmt->fetchAll();
            foreach($result as $key => $row) {
                echo $row['Title'] . "<br>";
            }
        }

    } catch(PDOException $e) {
        echo "Query error:".$sql . "<br>" . $e->getMessage();
    }

    $conn = null;
?>
1

If there are not previous insert queries, then lastInsertId() will return 0, for this reason the loop does not run.

The MySQL documentation explains the reason:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

Link: https://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id

To get the last id you can do:

SELECT MAX(id) FROM tablename;

A consideration about the loop: if you delete one of the previous rows then you will get some errors, for example:

> select id from tablename;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+

> delete from tablename where id = 2;

> select max(id) as last_id from tablename;
+---------+
| last_id |
+---------+
|       5 |
+---------+

The last id will always be 5, MySQL will not scale the values to occupy the missing row. As result, the loop will go through the id with value 2 even if it does not exists anymore. It's better to get the list of the ids in reverse order by the database:

select id from tablename order by id desc;

And loop them directly.

Edited by cereal

0

I understood your explanation.
Would you please help me more by give an example?
Should i change this:
$last_id = $conn->lastInsertId();
to This?
$last_id = "SELECT MAX(ID) FROM Posts";
Or like this? Because it seems like sql syntax.

    include('connection.php');
    $sql = "SELECT MAX(ID) FROM Posts";
    $result = $conn->query($sql);
    foreach($result as $key => $row) {
        echo $row;
    }

Edited by Niloofar24

1

@Nilo, do:

$row = $conn->query("SELECT MAX(id) AS last_id FROM Posts")->fetch(PDO::FETCH_OBJ);
echo $row->last_id;

It's important to define an alias MAX(id) AS last_id otherwise you get:

stdClass Object
(
    [MAX(id)] => 100
)

Which can be accessed with another syntax:

echo $row->{'MAX(id)'};
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.