How to Use mysqli::stmt_init to Execute an INSERT Operation and Retrieve the Auto-Increment ID (insert_id)

In PHP, when working with a MySQL database, you can interact with it using the mysqli extension. If you want to execute an INSERT operation and retrieve the auto-increment ID of the newly inserted data, you can do so using the mysqli::stmt_init function. In the following sections, we’ll explain in detail how to use mysqli::stmt_init to accomplish this task.

Step 1: Create a Database Connection

First, we need to establish a connection to the database. To ensure security and best practices, it’s recommended to use the object-oriented approach provided by mysqli. Here's a sample code snippet to connect to the database:

<?php
$host = 'localhost'; // Database host
$user = 'root';      // Database username
$password = '';      // Database password
$dbname = 'test';    // Database name
<p>// Create a database connection<br>
$conn = new mysqli($host, $user, $password, $dbname);</p>
<p>// Check if the connection was successful<br>
if ($conn->connect_error) {<br>
die("Connection failed: " . $conn->connect_error);<br>
}<br>
?><br>

Step 2: Prepare the SQL Statement

When preparing to execute an INSERT operation, we first write the SQL statement. Using the mysqli::stmt_init function, we can initialize a prepared statement. In the example below, we’ll insert a new user into the users table.

$sql = "INSERT INTO users (username, email) VALUES (?, ?)";

Step 3: Initialize the Statement

We use mysqli::stmt_init to initialize a statement object. Through this object, we can bind parameters and execute the query.

$stmt = $conn->stmt_init();
if (!$stmt->prepare($sql)) {
    die("SQL Error: " . $stmt->error);
}

Step 4: Bind Parameters

Next, we need to bind actual values to the placeholders (?) in the SQL statement using the bind_param method. Assuming we want to insert the username johndoe and the email johndoe@m66.net, here's how we do it:

$username = 'johndoe';
$email = 'johndoe@m66.net';
<p>// Bind parameters<br>
$stmt->bind_param("ss", $username, $email);<br>

In the code above, "ss" indicates that we’re binding two string parameters.

Step 5: Execute the SQL Statement and Retrieve the Auto-Increment ID

After executing the INSERT operation, MySQL will automatically generate an auto-increment ID for the new record. We can retrieve this ID using the insert_id property.

if ($stmt->execute()) {
    echo "New record inserted successfully. Inserted ID is: " . $conn->insert_id;
} else {
    echo "Insert failed: " . $stmt->error;
}

Here, $conn->insert_id returns the auto-increment ID of the inserted record.

Step 6: Close the Connection

After completing the insert operation, don’t forget to close both the statement and the database connection.

$stmt->close();
$conn->close();

Complete Example

Here’s a full example demonstrating how to use mysqli::stmt_init to execute an INSERT operation and retrieve the auto-increment ID.

<?php
$host = 'localhost';
$user = 'root';
$password = '';
$dbname = 'test';
<p>$conn = new mysqli($host, $user, $password, $dbname);</p>
<p>if ($conn->connect_error) {<br>
die("Connection failed: " . $conn->connect_error);<br>
}</p>
<p>$sql = "INSERT INTO users (username, email) VALUES (?, ?)";</p>
<p>$stmt = $conn->stmt_init();<br>
if (!$stmt->prepare($sql)) {<br>
die("SQL Error: " . $stmt->error);<br>
}</p>
<p>$username = 'johndoe';<br>
$email = '<a class="cursor-pointer" rel="noopener">johndoe@m66.net</a>';</p>
<p>$stmt->bind_param("ss", $username, $email);</p>
<p>if ($stmt->execute()) {<br>
echo "New record inserted successfully. Inserted ID is: " . $conn->insert_id;<br>
} else {<br>
echo "Insert failed: " . $stmt->error;<br>
}</p>
<p data-is-last-node="" data-is-only-node="">$stmt->close();<br>
$conn->close();<br>
?><br>