Moving a step ahead towards MySQL with PHP, we will learn how to connect to MySQL database in PHP.

Connect with MySQL in PHP

There are two methods in PHP to make the connection with MySQL.

  1. MySQLi extension
  2. PHP Data Objects (PDO)

MySQLi is an improved version of MySQL which was used in the earlier versions of PHP.

Which is better MySQLi or PDO?

The answer depends on the scenario. Both of these methods provide an easy method to connect with the database. Here is the difference between MySQLi and PDO.

PDOMySQLi
PDO support 12 Databases connections.MySQLi extension in PHP support only MySQL database.
PDO is more compatible with the PHP code in which you have to use multiple Databases or switch from one database to another. PDO allows you to switch without changing the entire code.MySQL is not preferred where we have multiple databases to work with or switching the connection among multiple databases. It required entire code and queries changes with MySQLi. while switching between databases.
PDO does not provide procedural API. However, it is object-oriented.MySQLi is objected-oriented as well as it provides procedural API.
PDO supports prepared statements.MySQLi also supports prepared statements.
MySQLi vs PDO

MySQL injection is a method to exploit or hack the application. Hackers try to inject some false piece of code into the database connection or queries. It allows the hackers to create loopholes and enter the application. We will learn about SQL injection later while discussing the SQL queries.

Examples of Connection in PDO and MySQLi

In this article, we will present examples to connect PHP with your database using the following methods.

  • PDO
  • MySQLi – Procedure
  • MySQLI – Object Oriented

How to install MySQLi and PDO?

Connect to MySQL (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";
?>

Connect to MySQL (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";
?>

Opening Connection with MySQL – PDO

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

try {
  $conn = new PDO("mysql:host=$servername;dbname=myDB", $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();
}
?>
  • Notice in the above example, PDO requires valid database to make the connection with. If no valid database is provided to PDO, it will throw exception while establishing the connection.
  • PDO exception is very helpful for executing the queries or establishing the connection. ANy exception in the query or connection in the try {} block is captured in the catch {} block.

How to close the connection?

The connection with the database closes automatically when the execution of the script completes. To close the connection manually, use the following code.

$conn->close(); MySQLi object-oriented
mysqli_close($conn); // MySQLi Procedural
$conn = null; // PDO

For official documentation and MySQL guide click here.