How to convert a MySQLi statement to PDO in PHP?

How to Make an SQL Conversion with PDO

Introduction

PDO (PHP Data Objects) provides a unified API for interacting with databases in PHP. It offers a structured and consistent method for writing and executing SQL queries. This article demonstrates how to convert the first if statement in the provided PHP code from using mysqli to using PDO for database access.

Updating the Code

To use PDO in the if statement, start by defining the database connection parameters:

$db_host = "127.0.0.1";
$db_name = "name_of_database";
$db_user = "user_name";
$db_pass = "user_password";

// Create a connection to the MySQL database
$pdo = new PDO(
    "mysql:host={$db_host};dbname={$db_name}",
    $db_user,
    $db_pass,
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => FALSE
    ]
);

Next, update the if statement to use PDO prepared statements:

if ($id) {
    $sql = "SELECT email FROM users WHERE u_id = ?";
    $query = $pdo->prepare($sql);    // Prepare the query 
    $query->execute([$id]);          // Bind the parameter and execute the query 
    $email = $query->fetchColumn();  // Return the value from the database
}

The fetchColumn() method retrieves a single column from the result set.

Additional Considerations

• Error Handling: Exceptions should be handled properly when working with PDO.
• Parameter Binding: Use prepared statements to prevent SQL injection vulnerabilities.
• Null Coalescing Operator: Use the null coalescing operator (??) to prevent notices or errors when variables are not defined.

Complete Updated Code

$id = $_SESSION['u_id'] ?? NULL;

if ($id) {
    $sql = "SELECT email FROM users WHERE u_id = ?";
    $query = $pdo->prepare($sql);
    $query->execute([$id]);
    $email = $query->fetchColumn();
}

$email = $email ?? "";
$suggestions = selectAll($table);

$optionOne = $_POST['optionOne'] ?? "";
$optionTwo = $_POST['optionTwo'] ?? "";
$newSuggestion = $_POST['new-suggestion'] ?? "";

if ($newSuggestion && $id && $email && $optionOne && $optionTwo) {
    $sql = "INSERT INTO suggestions (user_id, email, option_1, option_2) VALUES (?, ?, ?, ?)";
    $query = $pdo->prepare($sql);
    $query->execute([$id, $email, $optionOne, $optionTwo]);
} else {
    echo "All options must be entered";
}

By converting the code to use PDO, you enhance its security and improve its portability across different database systems.

The above is the detailed content of How to convert a MySQLi statement to PDO in PHP?. For more information, please follow other related articles on the PHP Chinese website!