Database MySQLi and PDO

Working with databases in PHP involves interacting with a database management system to perform operations such as storing, retrieving, updating, and deleting data. Two common approaches for database interaction in PHP are using the MySQLi extension and the PDO (PHP Data Objects) extension.

MySQLi Extension

MySQLi (MySQL Improved) is an extension in PHP that offers an enhanced and more secure way to interact with MySQL databases. It provides support for MySQL features, improved security, and advanced capabilities for developers.

Let's see an example of using MySQLi to connect to a MySQL database and retrieve data from a "users" table :

<?php
// Create a MySQLi connection
$mysqli = new mysqli("localhost", "username", "password", "database_name");

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

// Execute a query to retrieve user data
$query = "SELECT * FROM users";
$result = $mysqli->query($query);

// Check if the query was successful
if ($result) {
    while ($row = $result->fetch_assoc()) {
        // Process and display user data
    }
   $result->free(); // Free the result set
}else{
    echo "Query failed: " . $mysqli->error;
}

// Close the MySQLi connection
$mysqli->close();
?>

We create a MySQLi connection to the MySQL database, execute a query, retrieve and display user data, and close the connection.
MySQLi provides a straightforward way to work with MySQL databases and includes features like prepared statements for added security.

Prepared Statements : MySQLi supports prepared statements, which are a secure way to prevent SQL injection. Here's an example of using prepared statements to insert data into a table :

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

$username = "JohnDoe";
$email = "john@example.com";

if ($stmt->execute()) {
    // Data inserted successfully
}else{
    echo "Insertion failed: " . $stmt->error;
}
$stmt->close(); // Close the statement

Closing the Connection: After you're done with the database connection, it's essential to close it to free up resources:

$mysqli->close();

PDO (PHP Data Objects)

PDO is a database abstraction layer in PHP that provides a consistent and flexible way to interact with various database systems. It supports multiple database systems (such as MySQL, PostgreSQL, SQLite, and more) using a unified API. PDO is a more modern and versatile choice for database operations and is widely used for building database-driven web applications.

In this example, we'll use PDO to connect to a MySQL database and retrieve data from a "users" table.

<?php
try {
    // Create a PDO connection
    $pdo = new PDO("mysql:host=localhost;dbname=mydatabase", "username", "password");
    
    // Set PDO to throw exceptions for errors
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Execute a query to retrieve user data
    $query = "SELECT * FROM users";
    $stmt = $pdo->query($query);
    
    // Fetch and display user data
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "User ID: " . $row['id'] . ", Username: " . $row['username'] . "
";
    }
} catch (PDOException $e) {
    echo "Database connection failed: " . $e->getMessage();
}
?>

We create a PDO connection to the MySQL database, set PDO to throw exceptions for errors, execute a query, and retrieve and display user data. PDO provides a more versatile and secure way to work with databases.

The key difference is that PDO is more flexible, supports multiple database systems, and is recommended for modern PHP development. It's also safer to use due to its support for prepared statements, which helps prevent SQL injection. While the MySQL extension is deprecated, PDO and MySQLi are the preferred choices for database interaction in PHP.