PHP MySQL Prepared Statements

Welcome to The Coding College! In this guide, we’ll learn about Prepared Statements in PHP for interacting with MySQL databases. Prepared Statements are a secure and efficient way to execute SQL queries, especially when dealing with user inputs.

What Are Prepared Statements?

Prepared Statements are SQL queries that are precompiled by the database. They allow you to safely pass parameters to your SQL query without directly embedding them into the query string, thereby avoiding risks like SQL injection.

How Prepared Statements Work:

  1. The SQL query is sent to the database server with placeholders (? or named placeholders like :param).
  2. The server precompiles the query for execution.
  3. Parameter values are bound to the placeholders and the query is executed.

Benefits of Prepared Statements

  • Security: Protects against SQL injection attacks.
  • Performance: The database compiles the query once and reuses it for multiple executions, making it faster for repeated queries.
  • Cleaner Code: Makes your SQL queries easier to read and maintain.

Syntax of Prepared Statements

There are two ways to use prepared statements in PHP:

  1. MySQLi (MySQL Improved)
  2. PDO (PHP Data Objects)

Let’s explore both approaches.

Prepared Statements Using MySQLi

Example: Insert Data with MySQLi Prepared Statements

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

$conn = new mysqli($servername, $username, $password, $database);

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

// SQL with placeholders
$sql = "INSERT INTO Users (username, email, age) VALUES (?, ?, ?)";

// Prepare the statement
$stmt = $conn->prepare($sql);

// Bind parameters
// 'ssi' -> string, string, integer (data types for placeholders)
$stmt->bind_param("ssi", $username, $email, $age);

// Set values and execute
$username = "JohnDoe";
$email = "[email protected]";
$age = 25;
$stmt->execute();

$username = "JaneDoe";
$email = "[email protected]";
$age = 30;
$stmt->execute();

echo "Records inserted successfully.";

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

Explanation:

  • prepare(): Prepares the SQL query with placeholders.
  • bind_param(): Binds the actual values to the placeholders. The data types must match:
    • s for string
    • i for integer
    • d for double
  • execute(): Executes the prepared statement.

Example: Select Data with MySQLi Prepared Statements

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

$conn = new mysqli($servername, $username, $password, $database);

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

// SQL with a placeholder
$sql = "SELECT username, email FROM Users WHERE age > ?";

// Prepare the statement
$stmt = $conn->prepare($sql);

// Bind parameters
$stmt->bind_param("i", $min_age);

// Set the parameter value
$min_age = 20;

// Execute the statement
$stmt->execute();

// Get the result
$result = $stmt->get_result();

// Fetch data
while ($row = $result->fetch_assoc()) {
    echo "Username: " . $row['username'] . " - Email: " . $row['email'] . "<br>";
}

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

Prepared Statements Using PDO

Example: Insert Data with PDO Prepared Statements

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SQL with placeholders
    $sql = "INSERT INTO Users (username, email, age) VALUES (:username, :email, :age)";

    // Prepare the statement
    $stmt = $conn->prepare($sql);

    // Bind parameters
    $stmt->bindParam(':username', $username);
    $stmt->bindParam(':email', $email);
    $stmt->bindParam(':age', $age);

    // Set values and execute
    $username = "AliceDoe";
    $email = "[email protected]";
    $age = 22;
    $stmt->execute();

    $username = "BobSmith";
    $email = "[email protected]";
    $age = 28;
    $stmt->execute();

    echo "Records inserted successfully.";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// Close connection
$conn = null;
?>

Explanation:

  • prepare(): Prepares the SQL query with named placeholders (:param).
  • bindParam(): Binds values to the named placeholders.
  • Named placeholders make the query more readable.

Example: Select Data with PDO Prepared Statements

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SQL with a placeholder
    $sql = "SELECT username, email FROM Users WHERE age > :age";

    // Prepare the statement
    $stmt = $conn->prepare($sql);

    // Bind parameter
    $stmt->bindParam(':age', $min_age);

    // Set the parameter value
    $min_age = 20;

    // Execute the statement
    $stmt->execute();

    // Fetch data
    $result = $stmt->fetchAll();
    foreach ($result as $row) {
        echo "Username: " . $row['username'] . " - Email: " . $row['email'] . "<br>";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// Close connection
$conn = null;
?>

Best Practices for Using Prepared Statements

  1. Always Use Prepared Statements for User Input: Prevent SQL injection by binding user inputs.
  2. Choose PDO for Flexibility: PDO supports multiple database systems (e.g., MySQL, SQLite, PostgreSQL).
  3. Use Error Handling: Use try-catch blocks with PDO and check errors in MySQLi.
  4. Bind Parameters Properly: Always match parameter types (s, i, d) to the database column types.

Common Errors and Solutions

  1. Error: Call to a member function bind_param() on bool:
    • This occurs when the SQL query fails to prepare. Always check your SQL syntax.
  2. Error: SQLSTATE[HY093]: Invalid parameter number:
    • Ensure the placeholders in the query match the bound parameters.
  3. Error: Undefined variable:
    • Ensure all variables passed to bindParam() or bind_param() are initialized.

Conclusion

Prepared Statements in PHP are a powerful tool to write secure, efficient, and reusable database queries. Whether you use MySQLi or PDO, they offer strong protection against SQL injection and improve performance for repeated queries.

For more tutorials and insights, visit The Coding College and take your PHP skills to the next level.

Leave a Comment