Use mysqli_stmt::prepare to handle INSERT operations

1. Why use mysqli_stmt::prepare for INSERT operation?

1. 
   Through preprocessing statements, the structure and data of SQL statements are separated, avoiding the risk of malicious user injection.

2. Improve execution efficiency <br> For repeated execution statements, the database server only needs to parse the SQL structure once to improve performance.

3. Clear code structure <br> Parameter binding makes the code easier to read and reduces the complexity of splicing strings.

2. The complete process of using mysqli_stmt::prepare to process INSERT

Here is a standard usage example:

// Check the connection
if ($mysqli->connect_errno) {
die("Connection failed: " . $mysqli->connect_error);
}

// 2. Prepare SQL statements
$sql = "INSERT INTO users (username, email, age) VALUES (?, ?, ?)";
$stmt = $mysqli->prepare($sql);

if (!$stmt) {
die("Preprocessing failed: " . $mysqli->error);
}

// 3. Bind parameters
// 'ssi' represents parameter type, s represents string, and i represents integer
$username = "alice";
$email = " [email protected] ";
$age = 25;
$stmt->bind_param("ssi", $username, $email, $age);

// 4. Execute statement
if ($stmt->execute()) {
echo "Insert successfully, the insert ID is: ". $stmt->insert_id;
} else {
echo "Execution failed: " . $stmt->error;
}

// 5. Close statements and connections
$stmt->close();
$mysqli->close();
?>
</code>

3. Detailed description of each step

1. Create a mysqli object to connect to the database

Use new mysqli(host, user, pass, dbname) to connect to the database.
It is recommended to use exception handling or error logs instead of die() in production environments.

2. Create a preprocessing statement using prepare()

• Use a question mark ? as a parameter placeholder in SQL statements.

• If prepare() returns false , it means a syntax error or a connection exception.

3. Bind parameter bind_param()

• Bind variables into the arguments of the statement.

• Parameter types are represented by strings, common ones are:

  • s string (string)

  • i integer

  • d double precision (double)

  • b binary (blob)

• The variable must be a reference pass and must be assigned before execution.

4. Execute execute()

• Call $stmt->execute() to execute the preprocessing statement.

• Judging whether the execution is successful based on the returned result.

5. Close the resource

• Close the preprocessing statement $stmt->close() to free up the server resources.

• Close the database connection $mysqli->close() .

4. Precautions for handling INSERT operations using mysqli_stmt::prepare

1. Parameter types must match correctly <br> An incorrect parameter type may cause data insertion failure or data type conversion errors.

2. The bound variable must be assigned before execution <br> Otherwise, null or incorrect values ​​may be inserted.

3. Exception handling and error log <br> It is not recommended to use die() directly. You can use the exception mechanism to catch errors to ensure the robustness of the program.

4. Batch Insert <br> If batch inserts are required, you can use loop binding parameters and execution, but it is recommended to enable transactions to ensure atomicity.

5. Character encoding consistency <br> Ensure that the character encoding of the database and PHP scripts is consistent and avoid garbled code.

6. insert_id Get attention <br> When using $stmt->insert_id to get the autoincrement primary key, make sure there are autoincrement columns in the table.