Php Using Pdo And Mysqli Complete Guide

PHP Using PDO and MySQLi

PHP Data Objects (PDO) and MySQL Improved Extension (MySQLi) are two popular database access layers used by PHP to interact with MySQL databases. Both extensions offer methods for executing SQL queries, managing transactions, and handling prepared statements. However, they have different philosophies and capabilities:

Overview and Comparison

1. Purpose:

   • Both PDO and MySQLi provide a robust way to connect to and manipulate MySQL databases.

2. Driver-Based Approach:

   • PDO supports multiple database drivers (not limited to MySQL), allowing developers to easily switch between different database systems without changing the application code.
   • MySQLi is specifically designed for MySQL and includes enhanced features and optimizations tailored for MySQL.

3. Object-Oriented and Procedural Interfaces:

   • PDO uses an object-oriented interface exclusively.
   • MySQLi offers both an object-oriented and procedural interface, providing flexibility depending on individual coding styles.

4. Prepared Statements Support:

   • Both PDO and MySQLi support prepared statements, which help prevent SQL injection attacks and improve performance by pre-compiling SQL commands.

5. API Features:

   • PDO emphasizes portability and provides a unified API across various database types, which can be advantageous in large applications that may need to run against different database backends.
   • MySQLi focuses on offering advanced features specific to MySQL, such as enhanced error reporting, multiple statement execution, and improved performance through persistent connections.

Using PDO

Connecting to MySQL Database using PDO

try {
    $dsn = 'mysql:host=localhost;dbname=yourdatabase;charset=utf8mb4';
    $options = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ];
    $pdo = new PDO($dsn, 'yourusername', 'yourpassword', $options);
} catch (\PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

// Alternatively, without exception handling:
$pdo = new PDO($dsn, 'yourusername', 'yourpassword');

• DSN (Data Source Name):

  • Specifies the host, database name, and optionally other parameters like the character set used.
  • E.g., mysql:host=localhost;dbname=testdb;charset=utf8mb4

• Options Array:

  • PDO::ATTR_ERRMODE: Set to PDO::ERRMODE_EXCEPTION to force PDO to throw exceptions upon encountering errors.
  • PDO::ATTR_DEFAULT_FETCH_MODE: Determines the default fetch mode, e.g., PDO::FETCH_ASSOC to return results as an associative array.
  • PDO::ATTR_EMULATE_PREPARES: Set to false to utilize real prepared statements rather than emulated ones, enhancing security and performance.

Executing SQL Statements with PDO

Example: Selecting Data

$stmt = $pdo->query("SELECT * FROM users");

while ($row = $stmt->fetch()) {
    echo $row['name'] . '<br>';
}

Example: Inserting Data

$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$newUser = ['John Doe', 'john@example.com'];
$stmt->execute($newUser);

• Query Method:

  • Ideal for executing non-prepared statements that do not require any input from the user.

• Prepare Method:

  • Use when dealing with SQL queries containing dynamic data to prevent SQL injection.
  • Utilizes placeholders (?) in the query string which are then replaced by actual values at runtime.

Binding Parameters

$stmt = $pdo->prepare("SELECT * FROM users WHERE age > :age LIMIT :limit");
$stmt->bindValue(':age', 18, PDO::PARAM_INT);
$stmt->bindValue(':limit', 10, PDO::PARAM_INT);
$stmt->execute();

while ($row = $stmt->fetch()) {
    echo $row['name'] . '<br>';
}

• bindValue Method:
  • Binds a value to a parameter in a prepared statement.
  • The third argument specifies the data type of the parameter.

Fetching Results

• Fetch Methods:
  • fetch(PDO::FETCH_ASSOC): Returns a single row as an associative array.
  • fetchAll(PDO::FETCH_ASSOC): Retrieves all rows into an associative array.
  • fetchColumn(): Fetches a single column from the next row of a result set.

Transactions with PDO

try {
    $pdo->beginTransaction();

    $sql1 = 'INSERT INTO users (name, email) VALUES (?, ?)';
    $stmt1 = $pdo->prepare($sql1);
    $stmt1->execute(['Alice Johnson', 'alice@example.com']);

    $sql2 = 'UPDATE profiles SET bio = ? WHERE user_id = ?';
    $stmt2 = $pdo->prepare($sql2);
    $stmt2->execute(['New bio text here', $pdo->lastInsertId()]);

    $pdo->commit();
} catch (\Exception $e) {
    $pdo->rollBack();
    echo 'Transaction failed: ' . $e->getMessage();
}

• beginTransaction Method:

  • Starts a transaction.

• commit Method:

  • Commits the current transaction, making all changes permanent.

• rollBack Method:

  • Rolls back the current transaction, undoing all changes made during the transaction.

Using MySQLi

Connecting to MySQL Database using MySQLi

Object-Oriented Interface:

$mysqli = new mysqli('localhost', 'yourusername', 'yourpassword', 'yourdatabase');

if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') '
        . $mysqli->connect_error);
}

Procedural Interface:

$mysqli = mysqli_connect('localhost', 'yourusername', 'yourpassword', 'yourdatabase');

if (!$mysqli) {
    die('Connect Error (' . mysqli_connect_errno() . ') '
        . mysqli_connect_error());
}

• Connection:
  • MySQLi accepts four parameters for establishing a connection: hostname, username, password, and database name.
  • In the procedural approach, $mysqli is a resource, whereas in the object-oriented approach, it is an instance of the mysqli class.

Executing SQL Statements with MySQLi

Object-Oriented Interface:

Example: Selecting Data

$result = $mysqli->query("SELECT * FROM users");

while ($row = $result->fetch_assoc()) {
    echo $row['name'] . '<br>';
}

Example: Inserting Data

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

$name = "Bob Smith";
$email = "bob@example.com";

$stmt->bind_param("ss", $name, $email); // "ss" indicates two strings
$stmt->execute();

• query Method:

  • Executes a query and retrieves the results.
  • Suitable for non-prepared statements.

• prepare Method:

  • Used for creating a prepared statement with placeholders.
  • Enforces secure data handling.

Binding Parameters:

$stmt->bind_param("is", $age, $name); // "is" indicates integer and string

• bind_param Method:
  • Binds variables to the prepared statement as parameters.
  • The first argument is a string specifying the types of each parameter: i for integer, d for double, s for string, and b for blob.

Fetching Results:

• fetch_assoc Method:
  • Retrieves a result row as an associative array.

Procedural Interface:

Example: Inserting Data

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

$name = "Charlie Brown";
$email = "charlie@example.com";

mysqli_stmt_bind_param($stmt, "ss", $name, $email);
mysqli_stmt_execute($stmt);

echo mysqli_stmt_affected_rows($stmt) . ' rows inserted.';

• mysqli_prepare:

  • Creates a prepared statement for executing against the database.

• mysqli_stmt_bind_param:

  • Binds variables to a prepared statement as parameters.

• mysqli_stmt_execute:

  • Executes the prepared statement.

• mysqli_stmt_affected_rows:

  • Returns the number of affected rows from the last executed statement.

Multiple Statement Execution with MySQLi (Not Available in PDO)

MySQLi allows running multiple queries within a single call:

if ($mysqli->multi_query($queries)) {
    do {
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_assoc()) {
                echo $row['name'] . "<br>";
            }
            $result->free();
        } else {
            printf("Error: %s\n", $mysqli->error);
        }

        if ($mysqli->more_results()) {
            printf("--------------------\n");
        }
    } while ($mysqli->next_result());
}

• multi_query:

  • Executes multiple queries separated by semicolons.

• store_result:

  • Transfers the result set from the last query to memory.

• more_results and next_result:

  • Iterates through multiple result sets returned by multi_query.

Error Handling with MySQLi

Using Exceptions:

MySQLi does not natively support exceptions for error handling, but this behavior can be enabled using:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $mysqli = new mysqli('localhost', 'nonexistent_user', '', 'test');
} catch (\mysqli_sql_exception $e) {
    echo 'Error connecting to database: ' . $e->getMessage();
}

• mysqli_report:
  • Configures the MySQLi driver to report an error using exceptions.
  • Combines flags MYSQLI_REPORT_ERROR and MYSQLI_REPORT_STRICT to ensure all errors are reported as exceptions.

Traditional Error Handling:

if (!$mysqli = mysqli_connect('localhost', 'nonexistent_user', '', 'test')) {
    echo 'Connection error: ' . mysqli_connect_error();
}

$stmt = $mysqli->prepare("SELECT * FROM non_existent_table");
if (!$stmt) {
    echo 'Error preparing statement: ' . $mysqli->error;
}

• error Message:
  • Retrieves the last error message string from the most recent function call that can succeed or fail.

Important Security Considerations

1. SQL Injection Prevention:

   • Always use prepared statements with bound parameters instead of directly concatenating user inputs into SQL queries.

2. Character Encoding:

   • Specify the character encoding in the DSN or explicitly after connecting using set_charset.
   • E.g., $pdo->exec("SET NAMES utf8mb4"); or $mysqli->set_charset("utf8mb4");
   • This ensures proper character encoding, preventing potential security issues and data corruption.

3. Password Storage:

   • Never store plain passwords in your database.
   • Use strong hashing algorithms like bcrypt via PHP's password_hash and password_verify functions.

Conclusion

Both PDO and MySQLi are powerful tools for interacting with MySQL databases in PHP. The choice between them depends on specific project requirements and developer preferences:

• PDO: Suitable for projects requiring multiple database support, with its portable and unified API.
• MySQLi: Ideal for projects specifically targeting MySQL, leveraging enhanced MySQL-specific features.

Regardless of the chosen extension, always implement best practices in terms of security and performance to ensure the robustness of your application.