• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Prepared

← Previous Next →

In modern PHP applications, interacting with a database is a daily task. Whenever users submit data through forms or applications, it’s essential to insert, update, or query the database securely. One of the most important tools to achieve this is prepared statements.

Prepared statements provide a secure, efficient, and maintainable way to execute SQL queries that involve user input or dynamic data. They prevent SQL injection attacks, improve performance when executing repeated queries, and make your code easier to maintain.

In this tutorial, you will learn how prepared statements work, how to implement them in PHP using MySQLi and PDO, and the best practices for real-world applications.

What Are Prepared Statements?

A prepared statement is a template for an SQL query in which the values are not directly included in the query. Instead, placeholders are used, and actual values are supplied separately when executing the statement.

The process consists of two main steps:

  1. Preparation: The SQL statement is sent to the database server with placeholders (e.g., ? or :name). The server parses and compiles the query without executing it.

  2. Execution: The actual values are bound to the placeholders and the statement is executed.

This separation ensures that user input is never directly executed as SQL code, which is what prevents SQL injection.

Example Concept

Suppose you want to insert a student record into a table:

INSERT INTO students (name, age, email) VALUES (?, ?, ?)

Here:

  • The ? symbols are placeholders.

  • You later bind actual PHP variables like $name, $age, and $email to these placeholders.

Why Prepared Statements Are Important

  1. Security: Protects your database from malicious input.

  2. Performance: For repeated queries, the SQL is parsed only once.

  3. Code Clarity: Reduces string concatenation and makes code readable.

  4. Error Handling: Easier to detect and handle database errors.

Real-World Example: If a user registers on a website, their username and email are supplied via a form. Using a prepared statement ensures that even if the user enters malicious SQL code, it will be treated as plain text, not executed.

Using MySQLi Prepared Statements

Procedural Style Example

<?php
$conn = mysqli_connect("localhost", "root", "", "school_db");
if (!$conn) die("Connection failed: " . mysqli_connect_error());

// Prepare the statement
$stmt = mysqli_prepare($conn, "INSERT INTO students (name, age, email) VALUES (?, ?, ?)");

// Bind parameters: s = string, i = integer
mysqli_stmt_bind_param($stmt, "sis", $name, $age, $email);

// Execute with first student
$name = "Neha";
$age = 16;
$email = "neha@example.com";
mysqli_stmt_execute($stmt);

// Execute with second student
$name = "Anika";
$age = 17;
$email = "anika@example.com";
mysqli_stmt_execute($stmt);

echo "Records inserted successfully!";

// Close statement and connection
mysqli_stmt_close($stmt);
mysqli_close($conn);
?>

Explanation:

  • mysqli_prepare() prepares the SQL with placeholders.

  • mysqli_stmt_bind_param() links PHP variables to the placeholders.

  • "sis" indicates the data types: string, integer, string.

  • The statement can be executed multiple times with different values.

Object-Oriented Style Example

<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);

$stmt = $conn->prepare("INSERT INTO students (name, age, email) VALUES (?, ?, ?)");
$stmt->bind_param("sis", $name, $age, $email);

$students = [
    ["Riya", 16, "riya@example.com"],
    ["Sanya", 18, "sanya@example.com"]
];

foreach ($students as $student) {
    $name = $student[0];
    $age = $student[1];
    $email = $student[2];
    $stmt->execute();
}

echo "Records inserted successfully!";
$stmt->close();
$conn->close();
?>

This example demonstrates looping through an array to insert multiple records efficiently.

Using PDO Prepared Statements

PDO provides named placeholders or positional placeholders (?) and is compatible with multiple database types.

Named Placeholders Example

<?php
try {
    $conn = new PDO("mysql:host=localhost;dbname=school_db", "root", "");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $conn->prepare("INSERT INTO students (name, age, email) VALUES (:name, :age, :email)");

    $students = [
        ["Neha", 16, "neha@example.com"],
        ["Anika", 17, "anika@example.com"],
        ["Riya", 16, "riya@example.com"]
    ];

    foreach ($students as $student) {
        $stmt->bindParam(':name', $student[0]);
        $stmt->bindParam(':age', $student[1]);
        $stmt->bindParam(':email', $student[2]);
        $stmt->execute();
    }

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

$conn = null;
?>

Explanation:

  • Named placeholders like :name improve readability.

  • Using bindParam() allows dynamic insertion while protecting against SQL injection.

Troubleshooting Common Errors

Error Cause Solution
"No value bound" Forgetting bind_param Ensure parameters are bound before execution
SQL syntax error Typo in SQL Verify table and column names
Duplicate entry Primary key or unique constraint violation Use AUTO_INCREMENT or check for duplicates
Connection failed Incorrect credentials or host Check database credentials

Performance Tips

  1. Reuse prepared statements when inserting multiple rows to reduce parsing overhead.

  2. Use transactions for batch inserts to ensure atomicity.

  3. Avoid unnecessary concatenation of variables into SQL queries.

Example: Inserting 1000 student records using a loop with a prepared statement is much faster than 1000 separate INSERT queries.

Summary of the Tutorial

  • Prepared statements secure your application by preventing SQL injection.

  • They separate SQL logic from data, ensuring proper handling of dynamic input.

  • MySQLi and PDO both support prepared statements, with minor differences in syntax.

  • Using loops and transactions with prepared statements can efficiently handle large datasets.

  • Always bind parameters, validate input, handle exceptions, and close connections properly.

Prepared statements are widely used in applications like user registration, order processing, bulk data insertion, and form submissions. Implementing them correctly ensures your PHP applications are safe, reliable, and maintainable.

Practice Questions

  1. Write a PHP MySQLi procedural script to insert a student record using a prepared statement.

  2. Write the same insertion using MySQLi object-oriented style.

  3. Write a PDO PHP script to insert a student using named placeholders and bind parameters.

  4. Explain why prepared statements prevent SQL injection.

  5. Modify a prepared statement to insert multiple student records using a loop.

  6. Write a PHP script that updates a student’s email using a prepared statement.

  7. Write a PHP script that deletes a student record using a prepared statement with a bound ID.

  8. Explain the difference between positional placeholders (?) and named placeholders (:name) in PDO.

  9. Write a PHP script that retrieves a student’s record using a prepared SELECT statement.

  10. Explain a real-world scenario, such as user registration or order processing, where prepared statements are crucial for security and performance.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

PHP

online coding class codepractice

PHP Basics

PHP Data

Control Flow

PHP Functions

PHP Arrays

PHP Forms

PHP Advanced

Cookies & Sessions

Filters & JSON

PHP Exceptions

PHP OOP

MySQL with PHP

PHP and XML

AJAX with PHP

codepractice logo
Login
Go Back Top