TimesCoding

Learn, Practice, and Excel with our Coding Tutorials!

PHP and MySQL Connection: MySQLi vs PDO Explained

June 8, 2024 2 Min read PHP

Connecting PHP to MySQL is a common task in web development. Below are the steps to establish a connection using the MySQLi (MySQL Improved) extension and the PDO (PHP Data Objects) extension, which are the two most commonly used methods.

Method 1: Using MySQLi

MySQLi stands for MySQL Improved. It provides both procedural and object-oriented interfaces.

Establish a connection:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

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

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

Close the connection:

<?php
mysqli_close($conn);
?>

Establish a connection:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

Close the connection:

<?php
$conn->close();
?>

Method 2: Using PDO

PDO (PHP Data Objects) is a more flexible way to connect to databases as it supports multiple database types.

Establish a connection:

<?php
$dsn = 'mysql:host=localhost;dbname=database';
$username = 'username';
$password = 'password';

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

Close the connection:

<?php
$conn = null;
?>

Summary

Both MySQLi and PDO extensions provide robust ways to connect to a MySQL database. MySQLi is specifically designed for MySQL and offers both procedural and object-oriented ways to connect, whereas PDO provides a more flexible object-oriented approach that can be used with different database types. Your choice between the two depends on your specific requirements and preferences.

Related Post:
CSRF vs XSS: Key Differences and How to Prevent Attacks − For web security, CSRF (Cross-Site Request Forgery) and XSS (Cross-Site Scripting) both are potent vulnerabilities that can wreak havoc if exploited, but they are fundamentally.
How to Use PHP iconv() Function for Effective Character Encoding Conversion − The iconv() function in PHP is used for character set conversion. This function converts a string from one character encoding to another. It is particularly.
PHP Date and Time Functions Explained with Examples − PHP offers a variety of functions for working with dates and times. These functions allow you to: Here are some commonly used functions and examples:.
How to Use the PHP chr() Function with Examples − The chr() function in PHP is used to return a character from a specified ASCII value. ASCII (American Standard Code for Information Interchange) values range.
How to Use PHP chunk_split() Function for String Manipulation − PHP chunk_split() Function The chunk_split() function in PHP is used to split a string into smaller chunks and add a specified string (usually a separator).
How to Use the PHP chop() Function to Trim Strings Effectively − The chop() function in PHP is used to remove whitespace or other predefined characters from the right end of a string. It’s an alias for.
How to Share Session Variables Between PHP Files for Secure CSRF Token Handling − In this tutorial, we will learn how to share session variables between PHP files, specifically focusing on handling a CSRF (Cross-Site Request Forgery) token securely..
PHP bin2hex() Function with Example − The bin2hex() function in PHP converts binary data into its hexadecimal representation. This can be particularly useful for encoding binary data in a readable format.
Secure Your PHP Strings with the addslashes() Function − The addslashes() function in PHP is used to escape certain characters in a string by adding backslashes before them. This is particularly useful when preparing.
PHP Sanitize Input with Example: Keeping Your Website Secure − In the dynamic world of web development, user input is the lifeblood of many applications. However, this very input can also pose a significant security.
Creating a Mobile Number Show-Hide Feature with HTML, CSS, and PHP − In today’s web development landscape, creating a user-friendly and secure user interface is crucial. One common requirement is the need to display sensitive information, such.
URL Exist or Not Exist Check PHP − We can easily check a given URL or Link is active or not. By using below PHP function we can easily get whether the given.