Using MySQLi prepared statements in PHP is a good practice for securing database queries, especially when dealing with user input, as it helps prevent SQL injection attacks. Prepared statements allow you to bind values to SQL queries before executing them, ensuring that the user input is properly escaped.

1. Simple Prepared Statement (INSERT) Example in PHP

This example demonstrates how to use a prepared statement to insert data into a users table using MySQLi.

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Database where the table is located// Create connection$conn = new mysqli($host, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);}// Prepare and bind the statement$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");$stmt->bind_param("ss", $name, $email); // "ss" means two string parameters// Set parameters and execute the statement$name = "John Doe";$email = "john.doe@example.com";$stmt->execute();// Check if the insert was successfulif ($stmt->affected_rows > 0) {    echo "New record created successfully.";} else {    echo "Error: Could not insert data.";}// Close the statement and connection$stmt->close();$conn->close();?>

Explanation:

• $conn->prepare(): Prepares the SQL query for execution. The ? placeholders are used to represent the values that will be bound later.

• $stmt->bind_param("ss", $name, $email): Binds the parameters to the SQL query. The ss means that the values being passed are strings (s for string).

• $stmt->execute(): Executes the prepared statement.

• $stmt->affected_rows: Checks if the statement affected any rows, i.e., whether the insert was successful.

2. Prepared Statement for SELECT Query (Read Data)

This example demonstrates how to use a prepared statement to select data from the users table.

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Database where the table is located// Create connection$conn = new mysqli($host, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);}// Prepare and bind the statement$stmt = $conn->prepare("SELECT id, name, email FROM users WHERE email = ?");$stmt->bind_param("s", $email); // "s" means one string parameter// Set the parameter value and execute the statement$email = "john.doe@example.com";$stmt->execute();// Bind the result to variables$stmt->bind_result($id, $name, $email);// Fetch the resultwhile ($stmt->fetch()) {    echo "ID: $id - Name: $name - Email: $email<br>";}// Close the statement and connection$stmt->close();$conn->close();?>

Explanation:

• $stmt->prepare(): Prepares the SQL query. The ? placeholder is for the email value.

• $stmt->bind_param("s", $email): Binds the email value (which is a string) to the prepared statement.

• $stmt->bind_result($id, $name, $email): Binds the columns from the result set to PHP variables.

• $stmt->fetch(): Fetches each row of the result and populates the variables.

3. Prepared Statement for UPDATE Query (Update Data)

This example demonstrates how to use a prepared statement to update data in the users table.

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Database where the table is located// Create connection$conn = new mysqli($host, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);}// Prepare and bind the statement$stmt = $conn->prepare("UPDATE users SET name = ?, email = ? WHERE id = ?");$stmt->bind_param("ssi", $name, $email, $id); // "ssi" means two strings and one integer parameter// Set the parameters and execute the statement$name = "Jane Doe";$email = "jane.doe@example.com";$id = 1;$stmt->execute();// Check if the update was successfulif ($stmt->affected_rows > 0) {    echo "Record updated successfully.";} else {    echo "Error: Could not update data.";}// Close the statement and connection$stmt->close();$conn->close();?>

Explanation:

• $stmt->prepare(): Prepares the SQL query to update a record in the users table.

• $stmt->bind_param("ssi", $name, $email, $id): Binds the name and email (strings) and id (integer) to the SQL query.

• $stmt->execute(): Executes the prepared statement.

4. Prepared Statement for DELETE Query (Delete Data)

This example demonstrates how to use a prepared statement to delete data from the users table.

<?php// Database connection parameters$host = "localhost";$username = "root";$password = "";$dbname = "testDB"; // Database where the table is located// Create connection$conn = new mysqli($host, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {    die("Connection failed: " . $conn->connect_error);}// Prepare and bind the statement$stmt = $conn->prepare("DELETE FROM users WHERE id = ?");$stmt->bind_param("i", $id); // "i" means one integer parameter// Set the parameter value and execute the statement$id = 2;$stmt->execute();// Check if the delete was successfulif ($stmt->affected_rows > 0) {    echo "Record deleted successfully.";} else {    echo "Error: Could not delete data.";}// Close the statement and connection$stmt->close();$conn->close();?>

Explanation:

• $stmt->prepare(): Prepares the SQL query for deleting a record based on the id.

• $stmt->bind_param("i", $id): Binds the id (integer) to the prepared statement.

• $stmt->execute(): Executes the prepared statement to delete the record.

Key Points to Remember:

1. Binding Parameters: Use bind_param() to bind variables to the prepared statement. The first argument specifies the types of the parameters:

   • i for integer

   • d for double (float)

   • s for string

   • b for blob (binary data)

2. Prepared Statement Benefits: Using prepared statements helps prevent SQL injection by automatically escaping special characters in the parameters.

3. Object-Oriented Approach: You can use the object-oriented version of MySQLi for better readability and maintainability.

Let me know if you need further clarifications!