Database Interaction in PHP: MySQLi and PDO

1. Connecting to MySQL Database using MySQLi

To connect to a MySQL database using MySQLi, use the mysqli_connect() function. Here's how:

// MySQLi Connection
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

2. Executing Queries with MySQLi

To execute queries using MySQLi, use mysqli_query(). You can perform SELECT, INSERT, UPDATE, and DELETE operations.

// Example of SELECT query
$sql = "SELECT id, name FROM users";
$result = mysqli_query($conn, $sql);

// Check if there are results
if (mysqli_num_rows($result) > 0) {
    // Output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
    }
} else {
    echo "0 results";
}

3. Closing the MySQLi Connection

After executing your queries, it's important to close the connection:

mysqli_close($conn);

4. Connecting to MySQL Database using PDO

Connecting to a MySQL database using PDO is straightforward. You need to create a new PDO instance:

// PDO Connection
try {
    $pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // Set the PDO error mode to exception
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully"; 
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

5. Executing Queries with PDO

To execute queries with PDO, use the prepare() and execute() methods for better security against SQL injection:

// Example of SELECT query
$stmt = $pdo->prepare("SELECT id, name FROM users");
$stmt->execute();

// Set the resulting array to associative
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach($result as $row) {
    echo "id: " . $row["id"] . " - Name: " . $row["name"] . "&t;br>";
}

6. Closing the PDO Connection

Closing a PDO connection is done by setting the object to null:

$pdo = null;

7. Differences Between MySQLi and PDO

Both MySQLi and PDO provide ways to interact with MySQL databases, but they have some differences:

• Database Support: MySQLi is specific to MySQL, while PDO supports multiple database types.
• Prepared Statements: Both support prepared statements, but PDO provides a more consistent approach.
• Error Handling: PDO uses exceptions for error handling, making it more flexible.

Conclusion

Both MySQLi and PDO are effective tools for interacting with MySQL databases in PHP. While MySQLi is suitable for basic interactions, PDO offers enhanced security and flexibility for more complex applications. It's essential to choose the one that best fits your project requirements.