$stmt=$dbc-> prepare ('SELECT id FROM myTble ORDER BY id DESC LIMIT 1');
$row=$stmt->fetch();
echo $row['id'];
The code above produces the result below.
In order to get the last ID without the notice above, I test the code below. And it produces my target result.
$stmt=$dbc-> query ('SELECT lime FROM log ORDER BY lN DESC LIMIT 1');
WHILE ($row = $stmt ->fetch()) {
$lastID=$row['ID'];
}
echo $lastID;
However, Turning with the code “WHILE” which is in the code above for getting just one value is, I think, a little strange.
Is there any way for get the lastID without the code “WHILE”?
In the first example, you only prepared the query, you didn’t execute it, that’s why it didn’t work. Nothing to do with the WHILE loop. You could edit the second code like this (and change it to use the correct column name)
$stmt=$dbc-> query ('SELECT lime FROM log ORDER BY lN DESC LIMIT 1');
$row = $stmt ->fetch();
$lastID=$row['lime']; // you only retrieve lime, so you can't then use id
echo $lastID;
Or, you could do this:
$stmt=$dbc-> query ('SELECT lime FROM log ORDER BY lN DESC LIMIT 1');
$lastID = $stmt ->fetchColumn();
echo $lastID;
Or if you edit the first code like this
$stmt=$dbc-> prepare ('SELECT id FROM myTble ORDER BY id DESC LIMIT 1');
$stmt->execute(); // add this line, to execute the query you prepared ^
$row=$stmt->fetch();
echo $row['id'];
If you’re just doing this to get the new ID after you’ve inserted a new row, there are better ways to do it.
There are two types of $stmt.
The one is prepare type and the other is query type.
The first one needs execute, and the later doesn’t.
I like to use the later one because It is, I think, quite simple.
I am in puzzle with the prepare type.
When to use the prepare type?
Is there any cases must-use prepare type because query type can NOT work?
Any time that you have variable data in the query, especially if that variable data has come from user input, you must use a prepared statement. Outside of that, there are other good reasons to use them - if you might need to worry about quotes around or inside your values, or if you’re going to call the query with multiple values over and over again as your extract from the doc below covered. In this latter case, you can prepare the statement before the loop starts, then call execute() inside the loop with your new values. Something like this pseudo-code
connect to database
sql = "insert into mytable (name, email, phone) values (?, ?, ?)
prep = prepare(sql)
open csv file
while (data = read_from_csv) {
prep->execute ([data[0], data[1], data[2])
}
I’m never 100% certain when it’s a good time to use a prepared statement and when there is no need, so I’ve just got into the habit of doing it all the time now.
But my point above is that where you used prepare, you must then use execute() to actually run the query, which is the bit you’d missed out and the cause of your error.
It may be one of the main characteristics of prepared statements, but mysqli supports prepared statements too. I don’t think it’s a characteristic of PDO specifically, but then I haven’t used mysqli in any great depth, I’ve only really read about it in here. To me, the main characteristic of PDO that made me choose it over mysqli was the fact that it can connect to other databases such as Microsoft SQL Server, where mysqli can only connect to MySQL.
If the query includes any variable values, use prepare and execute.
If you are repeating the same query multiple times (Eg, in a loop), prepare before the loop, execute within the loop.
This first is mostly for security, as when the statement is prepared, its structure is set in stone, therefore cannot be altered by user injection.
The second is mainly for efficiency, where the statement is prepared only once, then re-used. Also by its nature, it would likely include variable values, that change on each iteration of the loop, so the first reason would apply too.
The initial SELECT query in post #1, has no variables, so ->query() would be fine in that instance.
Prepared statements are not unique to PDO, they can be used on mysqli, but it’s not as easy as in PDO.
There are numerous reasons to choose PDO over mysqli. But I can’t think of one reason to choose mysqli.
In order to simplify PDO I created a class with numerous methods, each accepted a SQL statement and was guaranteed to return a certain type. Methods were called something like this: