How Can I Optimize Prepared Statement Usage in PHP with MySQLi?

Optimized Prepared Statement Usage in PHP with MySQLi

Your code appears to be missing the binding of parameters to the prepared statement. As per the mysqli::prepare documentation, parameter markers must be bound before executing a statement.

// Bind parameters for the first insertion
$name1 = 'one';
$age1  = 1;
$stmt->bind_param('si', $name1, $age1);

// Execute for the first insertion
$stmt->execute();

// Bind parameters for the second insertion
$name2 = 'two';
$age2  = 1;
$stmt->bind_param('si', $name2, $age2);

// Execute for the second insertion
$stmt->execute();

Advantages of mysqli

While mysqli is not the only library that supports prepared statements, it offers several advantages:

• Improved security: Prepared statements protect against SQL injection attacks by preventing the execution of arbitrary SQL queries.
• Enhanced performance: Prepared statements reuse parsed queries, leading to increased efficiency.
• Error handling: Prepared statements provide better error reporting and handling capabilities.

Complete Example with Error Handling

Here's a complete example of using prepared statements in PHP with MySQLi, including error handling:

// Database connection
$mysqli = new mysqli("localhost", "root", "root", "test");

if ($mysqli->connect_errno) {
    echo "Failed to connect: " . $mysqli->connect_error;
    exit;
}

// Prepare statement
$stmt = $mysqli->prepare("INSERT INTO users (name, age) VALUES (?,?)");

if (!$stmt) {
    echo "Prepare failed: " . $mysqli->error;
    exit;
}

// Bind parameters for the first insertion
$name1 = 'one';
$age1  = 1;
$stmt->bind_param('si', $name1, $age1);

// Execute for the first insertion
$stmt->execute();

if ($stmt->errno) {
    echo "Execution failed: " . $stmt->error;
    exit;
}

// Bind parameters for the second insertion
$name2 = 'two';
$age2  = 1;
$stmt->bind_param('si', $name2, $age2);

// Execute for the second insertion
$stmt->execute();

if ($stmt->errno) {
    echo "Execution failed: " . $stmt->error;
    exit;
}

// Selection statement
$stmt = $mysqli->prepare("SELECT * FROM users WHERE age = ?");

if (!$stmt) {
    echo "Prepare failed: " . $mysqli->error;
    exit;
}

// Bind parameters for selection
$age  = 1;
$stmt->bind_param('i', $age);

// Execute selection
$stmt->execute();

if ($stmt->errno) {
    echo "Execution failed: " . $stmt->error;
    exit;
}

// Fetch results
$result = $stmt->get_result();

if (!$result) {
    echo "Fetch failed: " . $stmt->error;
    exit;
}

while ($row = $result->fetch_assoc()) {
    echo $row['name'] . " " . $row['age'] . "<br>";
}

The above is the detailed content of How Can I Optimize Prepared Statement Usage in PHP with MySQLi?. For more information, please follow other related articles on the PHP Chinese website!