PHP Connect to MySQL

It is recommended to connect to MySQL using PHP 5 and above:

  • MySQLi extension ("i" means improved)
  • PDO (PHP Data Objects)

In earlier versions of PHP we used the MySQL extension. The extension was deprecated in 2012.

Should I use MySQLi or PDO?

If you need a short answer, "Use whichever you like."

MySQLi and PDO have their own advantages:

PDO is used in 12 different databases. MySQLi only targets MySQL databases.

So if your project needs to switch between multiple databases, PDO is recommended, so you only need to modify the connection string and some query statements. With MySQLi, if you have a different database, you need to rewrite all your code, including queries.

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

Both prepared statements are supported. Prepared statements can prevent SQL injection and are very important for the security of web projects.

MySQLi and PDO MySQL Examples

In this chapter and the following chapters, we will use the following three ways to demonstrate PHP operation MySQL:

  • MySQLi (object-oriented)
  • MySQLi (Process-oriented)
  • PDO

MySQLi installation

Linux and Windows: The MySQLi extension is installed automatically most of the time when the php5 mysql package is installed.

For installation details, please see: http://php.net/manual/en/mysqli.installation.php

You can check whether the installation was successful through phpinfo ():

PDO installation

For installation details, please see: http://php.net/manual/en/pdo.installation.php

You can check whether the installation was successful through phpinfo ():

Connect to MySQL

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

Examples (MySQLi-object-oriented)

<?php $servername = "localhost"; $username = "username"; $password = "password"; // Create connection $conn = new mysqli($servername, $username, $password); // Detect connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } echo "connection succeeded"; ?>

Note that in the above object-oriented examples $ connect_error was added in PHP 5.2.9 and 5.3.0. If you need to be compatible with earlier versions Please use the following code to replace:

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

Examples (MySQLi - Process-oriented)

<?php $servername = "localhost"; $username = "username"; $password = "password"; // Create connection $conn = mysqli_connect($servername, $username, $password); // Detect connection if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } echo "connection succeeded"; ?>

Examples (PDO)

<?php $servername = "localhost"; $username = "username"; $password = "password"; try { $conn = new PDO("mysql:host=$servername;", $username, $password); echo "connection succeeded"; } catch(PDOException $e) { echo $e->getMessage(); } ?>

Note that we have specified the database (myDB) in the above PDO example. PDO needs to set the database name during the connection process. If not specified, an exception is thrown.

Close the connection

The connection will be closed automatically after the script finishes executing. You can also use the following code to close the connection:

Instance (MySQLi-Object Oriented)


Examples (MySQLi-Process Oriented)


Examples (PDO)

$conn = null;