The mysqli_stmt_execute() function in PHP is used to execute a prepared statement. It's a crucial part of the MySQLi extension when working with prepared statements, which offer better security against SQL injection attacks.

Basic Syntax

bool mysqli_stmt_execute(mysqli_stmt $statement)

Simple Example

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");

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

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

// Bind parameters
$name = "John Doe";
$email = "john@example.com";
$age = 30;
$stmt->bind_param("ssi", $name, $email, $age);

// Execute the statement
if ($stmt->execute()) {
    echo "Record inserted successfully!";
} else {
    echo "Error: " . $stmt->error;
}

// Close statement and connection
$stmt->close();
$mysqli->close();
?>

Complete CRUD Examples

1. INSERT Operation

<?php
$mysqli = new mysqli("localhost", "username", "password", "test_db");

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

// Multiple insertions using the same prepared statement
$products = [
    ['Laptop', 999.99, 'Electronics'],
    ['Book', 19.99, 'Education'],
    ['Chair', 49.99, 'Furniture']
];

foreach ($products as $product) {
    $stmt->bind_param("sds", $product[0], $product[1], $product[2]);
    
    if ($stmt->execute()) {
        echo "Inserted: " . $product[0] . "<br>";
    } else {
        echo "Error inserting " . $product[0] . ": " . $stmt->error . "<br>";
    }
}

$stmt->close();
$mysqli->close();
?>

2. SELECT Operation

<?php
$mysqli = new mysqli("localhost", "username", "password", "test_db");

// Prepare SELECT statement
$stmt = $mysqli->prepare("SELECT id, name, price FROM products WHERE category = ?");
$category = "Electronics";

$stmt->bind_param("s", $category);
$stmt->execute();

// Bind result variables
$stmt->bind_result($id, $name, $price);

echo "Electronics Products:<br>";
while ($stmt->fetch()) {
    echo "ID: $id, Name: $name, Price: $$price<br>";
}

$stmt->close();
$mysqli->close();
?>

3. UPDATE Operation

<?php
$mysqli = new mysqli("localhost", "username", "password", "test_db");

// Prepare UPDATE statement
$stmt = $mysqli->prepare("UPDATE products SET price = ? WHERE name = ?");
$new_price = 1099.99;
$product_name = "Laptop";

$stmt->bind_param("ds", $new_price, $product_name);

if ($stmt->execute()) {
    if ($stmt->affected_rows > 0) {
        echo "Price updated successfully!";
    } else {
        echo "No rows affected. Product may not exist.";
    }
} else {
    echo "Error: " . $stmt->error;
}

$stmt->close();
$mysqli->close();
?>

4. DELETE Operation

<?php
$mysqli = new mysqli("localhost", "username", "password", "test_db");

// Prepare DELETE statement
$stmt = $mysqli->prepare("DELETE FROM products WHERE id = ?");
$product_id = 5;

$stmt->bind_param("i", $product_id);

if ($stmt->execute()) {
    echo "Deleted " . $stmt->affected_rows . " row(s)";
} else {
    echo "Error: " . $stmt->error;
}

$stmt->close();
$mysqli->close();
?>

Advanced Example with Transactions

<?php
$mysqli = new mysqli("localhost", "username", "password", "test_db");

// Disable autocommit
$mysqli->autocommit(false);

try {
    // Prepare statements
    $update_stmt = $mysqli->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
    $insert_stmt = $mysqli->prepare("INSERT INTO transactions (account_id, amount, type) VALUES (?, ?, ?)");
    
    // Transaction data
    $from_account = 1;
    $to_account = 2;
    $amount = 100;
    $transaction_type = "transfer";
    
    // Deduct from sender
    $update_stmt->bind_param("di", $amount, $from_account);
    if (!$update_stmt->execute()) {
        throw new Exception("Failed to deduct from sender");
    }
    
    // Add to receiver (using negative amount for deduction, positive for addition)
    $update_stmt->bind_param("di", -$amount, $to_account);
    if (!$update_stmt->execute()) {
        throw new Exception("Failed to add to receiver");
    }
    
    // Record transaction
    $insert_stmt->bind_param("ids", $from_account, $amount, $transaction_type);
    if (!$insert_stmt->execute()) {
        throw new Exception("Failed to record transaction");
    }
    
    // Commit transaction
    $mysqli->commit();
    echo "Transaction completed successfully!";
    
} catch (Exception $e) {
    // Rollback on error
    $mysqli->rollback();
    echo "Transaction failed: " . $e->getMessage();
}

$update_stmt->close();
$insert_stmt->close();
$mysqli->close();
?>

Key Benefits of mysqli_stmt_execute()

1. Security: Prevents SQL injection through parameter binding
2. Performance: Better performance for repeated executions
3. Efficiency: Reduced parsing time for identical queries
4. Type Safety: Proper data type handling through parameter binding

Parameter Types in bind_param()

• i - integer
• d - double (float)
• s - string
• b - blob (sent in packets)

Always remember to close statements and connections when you're done with them to free up resources.