PHP connects to MySQL

Author：Eve Cole Update Time：2025-01-08 12:12:01

For PHP 5 and above, it is recommended to use the following method to connect to MySQL:

MySQLi extension ("i" means improved)

PDO (PHP Data Objects)

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

Should I use MySQLi or PDO?

If you need a short answer, "use whichever you're comfortable with".

MySQLi and PDO have their own advantages:

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

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

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

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

MySQLi and PDO connect to MySQL instance

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

MySQLi (object-oriented)

MySQLi (procedure-oriented)

PDO

MySQLi installation

Linux and Windows: In most cases the MySQLi extension is automatically installed 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 is successful through phpinfo():

PDO installation

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

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

Connect to MySQL

Before we 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 ) ; // Detect connection if ( $conn -> connect_error ) { die ( " Connection failed: " . $conn -> connect_error ) ; } echo " Connection successful " ; ?>

	Note that $connect_error in the above object-oriented example was added in PHP 5.2.9 and 5.3.0. If you need compatibility with earlier versions, please use the following code to replace: // Check connection if (mysqli_connect_error()) { die("Database connection failed: " . mysqli_connect_error());}

Example (MySQLi - Procedure 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 successful " ; ?>

Example(PDO)

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

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

close connection

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

Example (MySQLi - Object Oriented)

$conn->close();

Example (MySQLi - Procedure Oriented)

mysqli_close($conn);

Example(PDO)

$conn = null;