Three Ways to Make PHP Connect to MySQL: Make the Best Choice

Developers all around the world have preferred MySQL from other database management systems for more than a decade now. It is sturdy, reliable, and powerful. Now, when we have answered why you should use MySQL, we should get into understanding How.

In this tutorial, we will review two approaches to make PHP connect to MySQL. Coders can choose from MySQLi extension and PDO (PHP Data Objects). Both are safe and convenient to use to connect PHP to MySQL, but they have some differences. To make the best choice, you have to be informed about them both.

You might also have heard about a built-in PHP MySQL extension and mysql_connect function, but it is now a part of IT history: both of them have been deprecated. Therefore, the newer PHP versions don't support them.

PHP Connect to MySQL: Main Tips

• There are two main methods to work with the MySQL database: MySQLi extension and PDO. Both have their advantages.
• This post demonstrates three different examples how to connect to a database: MySQLi (object-oriented), MySQLi (procedural) and PDO.

Installation of MySQLi

The MySQLi extension works a bit faster than PDO, though the difference is somewhat insignificant. It also offers procedural connection. Unfortunately, it does not support named parameters, which PDO easily manages.

MySQLi extension usually installs automatically with PHP MySQL connection package and works on Linux and Windows operating systems. For installation details, visit PHP MySQLi installation page.

PDO Setup

PDO is more convenient to use if you plan to work with more databases than just MySQL. It can handle more than ten different databases, including Oracle, Informix, and SQLite. It also supports named parameters.

For installation details, go to the PHP PDO installation page.

Opening Connection to Database

Before starting to modify the database, we need to establish the connection with a server. The code example below shows how to make a PHP MySQL connection using MySQLi object-oriented method.

<?php
  $servername = "localhost";
  $username = "name_of_the_user"; 
  $password = "users_password";
 
  // Create connection
  $conn = new mysqli($servername, $username, $password); 
  // Check connection  
  if ($conn->connect_error) 
  {      
     die("Failed to connect: " . $conn->connect_error);
  }  
   echo "Connected successfully";  
?>

Now, in the example below, a different PHP MySQLi connection method is demonstrated. It also uses MySQLi and is known as procedural. Take a look:

<?php
  $servername = "localhost";
  $username = "name_of_the_user";  
  $password = "users_password";
  
  // Create connection  
  $conn = mysqli_connect($servername, $username, $password);

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

There is a database (myDatabase) specified in the PHP PDO example below. It is necessary because PDO works with multiple databases. Therefore, it requires to declare an exact one to proceed with the connection.

Examples above do not require a database name for starting a PHP MySQL connection (it will be needed when accessing the database itself).

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

  try 
  { 
     $conn = new PDO("mysql:host=$servername; dbname=myDatabase", $username, $password);
     $conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);    
     echo "Connected successfully";
  }
  catch(PDOException $e)    
  {      	
     echo "Failed to connect:  " . $e->getMessage();    
  }  
?>

Note: One of the benefits of using PDO is the try-catch blocks. It can catch any errors in the try block and execute error handling code in catch block.

Closing Connection

The connection closes automatically when the script stops running. If you want to close the connection earlier, use the following syntax:

• For PHP MySQLi object-oriented connection: $conn->close();.
• For PHP MySQLi procedural connection: mysqli_close($conn);.
• For PHP PDO connection: $conn = null;.

PHP Connect to MySQL: Summary

• You can connect to MySQL database using either MySQLi extension or PDO. PHP MySQL extension and mysql_connect function that existed previously has been deprecated.
• You can connect to MySQLi in object-oriented or procedural ways.
• With PDO your work is not limited to MySQL: it supports more than ten databases.