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.

---

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.

---

mysqli_insert_id().