The mysqli_prepare() function in PHP is used to prepare an SQL statement for execution. It’s part of the MySQLi (MySQL Improved) extension and is commonly used to prevent SQL injection, especially when working with user input.

Syntax:

mysqli_prepare(mysqli $link, string $query): mysqli_stmt|false

• $link: The MySQLi connection object.
• $query: The SQL query string with placeholders (?) for parameters.
• Returns a mysqli_stmt object on success or false on failure.

Why Use mysqli_prepare()?

• It separates the query structure from the data.
• Helps avoid SQL injection attacks.
• Required for using bound parameters (?).

Basic Example (SELECT)

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

// Prepare the SQL statement
$stmt = mysqli_prepare($mysqli, "SELECT name, email FROM users WHERE id = ?");

// Bind parameters (i = integer)
mysqli_stmt_bind_param($stmt, "i", $userId);

$userId = 1;

// Execute the prepared statement
mysqli_stmt_execute($stmt);

// Bind result variables
mysqli_stmt_bind_result($stmt, $name, $email);

// Fetch the results
while (mysqli_stmt_fetch($stmt)) {
    echo "Name: $name, Email: $email\n";
}

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

Example (INSERT)

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

$stmt = mysqli_prepare($mysqli, "INSERT INTO users (name, email) VALUES (?, ?)");

// Bind parameters (s = string)
mysqli_stmt_bind_param($stmt, "ss", $name, $email);

$name = "John Doe";
$email = "john@example.com";

mysqli_stmt_execute($stmt);

echo "Inserted ID: " . mysqli_insert_id($mysqli);

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

Parameter Types

• "i" = integer
• "d" = double
• "s" = string
• "b" = blob