Easy Tutorial
❮ Func Ftp Nb Fget Func Ftp Fput ❯

Connecting to MySQL with PHP


For PHP 5 and later versions, it is recommended to connect to MySQL using the following methods:

In earlier versions of PHP, we used the MySQL extension. However, this extension has been deprecated since 2012.


Should I use MySQLi or PDO?

For a quick answer, it's "use whichever you are comfortable with."

MySQLi and PDO have their own advantages:

PDO works with 12 different database systems, while MySQLi is specific to MySQL.

Therefore, if your project requires switching between multiple databases, PDO is recommended. You only need to modify the connection string and some query statements. With MySQLi, if you switch databases, you need to rewrite all the code, including queries.

Both are object-oriented, but MySQLi also provides an API interface.

Both support prepared statements. Prepared statements help prevent SQL injection, which is crucial for web application security.


MySQLi and PDO Examples to Connect to MySQL

In this and the following sections, we will demonstrate PHP operations on MySQL using the following three methods:


MySQLi Installation

On Linux and Windows, the MySQLi extension is usually installed automatically when the PHP5 MySQL package is installed.

For detailed installation information, see: http://php.net/manual/en/mysqli.installation.php

You can check if the installation was successful using phpinfo():


PDO Installation

For detailed installation information, see: http://php.net/manual/en/pdo.installation.php

You can check if the installation was successful using phpinfo():


Connecting to MySQL

Before we can access the MySQL database, we need to connect to the database server:

Example (MySQLi - Object-oriented)

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

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

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

Note: In the above object-oriented example, $connect_error was added in PHP 5.2.9 and 5.3.0. If you need to be compatible with earlier versions, use the following code instead:

// Check connection
if (mysqli_connect_error()) {
    die("Database connection failed: " . mysqli_connect_error());
}

Example (MySQLi - Procedural)

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

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

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

Example (PDO)

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

try {
    $conn = new PDO("mysql:host=$servername;", $username, $password);
    echo "Connected successfully";
} catch (PDOException $e) {
    echo $e->getMessage();
}
?>

Note: In the above PDO example, we have specified the database (myDB). PDO requires the database name during the connection process. If not specified, it will throw an exception.


Closing the Connection

To close the connection, you can simply unset the connection object or call the close() method for MySQLi, or set the PDO connection object to null. The connection will automatically close after the script execution. You can also use the following code to close the connection:

Example (MySQLi - Object-Oriented)

$conn->close();

Example (MySQLi - Procedural)

mysqli_close($conn);

Example (PDO)

$conn = null;
❮ Func Ftp Nb Fget Func Ftp Fput ❯