start learning
Image 1

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();
?>

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(); } ?>

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.