CODEWITHSUNDEEP
phpmysqliprepared-statement
Jason
Jason

Reputation: 219

Prepared statements and how they affect queries

In an effort to make my scripts more secure, I have started using prepared statements to prevent mysql injection. This script inserts the data just fine, but getting the last inserted id (based on auto incrementation in the database) now returns 0 when it should return the correct id number.

This part inserts just fine.

$stmt = $conn->prepare("INSERT INTO users (userName) VALUES (?)");
$stmt->bind_param("s", $_SESSION['username']);
$stmt->execute();

This is where I am having problems. I am trying to get the last inserted ID of the user and it is returning 0.

// Get the last inserted ID for the users ID
$query = "SELECT LAST_INSERT_ID()";
$result = mysql_query($query);
if ($result) {
    $nrows = mysql_num_rows($result);
    $row = mysql_fetch_row($result);
    $userId = $row[0];
}

This was working when I had my script before starting on Prepared Statements. The only thing I changed was adding the Prepared Statement to insert the data and my db connection is as follows:

$conn = new mysqli($server, $user, $password, $database) or die('Could not connect: ' . mysql_error());

I am a noob with php so any help would be greatly appreciated.

Upvotes: 1

Views: 378

Answers (2)

mishmash
mishmash

Reputation: 4458

mysqli_insert_id().

Upvotes: 1

Pekka
Pekka

Reputation: 449395

You may have a fundamental misunderstanding: When you switch to mysqli, your mysql_* functions will no longer use the same connection.

LAST_INSERT_ID() works on a per-connection basis.

You will have to make that query using the same library/connection as the main query, or as @zerkms points out, use $conn->insert_id.

Upvotes: 4

Related Questions