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.
Hi, can you show the query?
<?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;
?>
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.
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;
}
Maybe you should var_dump($conn->lastInsertId()) before you use it in for($a = $last_id ; $a > 0 ; $a--). As I checked in http://php.net/manual/en/pdo.lastinsertid.php, the pdo->lastInsertId() return string, cast it into integer before usage.
@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)'};
Yes it works ok now, thank you @creal.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.