Connecting PHP to MySQL Database

In this tutorial, we will learn to connect a MySQL database to PHP by different methods. We will use the MariaDB which is a fork of MySQL throughout the tutorial. Note that, all the syntaxes, statements and codes given below are the same for MySQL as well as MariaDB. Therefore, you can choose either MySQL or MariaDB as per the availability.

There are multiple methods to connect the PHP program to the MySQL database. You can connect a PHP program to MySQL using-

• MySQLi Extension (Procedural method)
• MySQLi Extension (Object-Oriented)
• PDO (PHP Data Object)

The MySQLi is an improved version of the MySQL extension. The latest PHP version supports MySQLi only because the MySQL extension was deprecated in 2012.

The PDO stands for PHP Data Object which is used to connect the database with the PHP. The main advantage of PDO is that it supports 12 different databases whereas MySQLi only supports MySQL database.

Also read: Connect MySQL with Nodejs – Simple Guide

PDO or MySQLi? What to Use?

Both PDO and MySQLi have their own features and advantages. So, it’s up to you to decide which way to use.

If you are not likely to change the database in the future for your project, you may go with MySQLi. But, if you are not sure about keeping the same database for your entire life, then PDO is the best option for you as it supports 12 different database systems.

PHP provides support for both MySQLi and PDO by default. You don’t need to install the drivers for them explicitly. However, if your program doesn’t recognize PDO or MySQLi, you can check the installation details from-

MySQLi Installation Details- http://php.net/manual/en/mysqli.installation.php

PDO Installation Details- http://php.net/manual/en/pdo.installation.php

Examples to Connect PHP with MySQL

Before connecting the MySQL database with PHP, we have to create a database. You can create a database through MySQL CLI as well as MySQL workbench or PhpMyAdmin.

You can create a database by using the following command.

CREATE DATABASE journaldev;Code language: SQL (Structured Query Language) (sql)

We are now set to connect the ‘journaldev’ database to the PHP program.

Connect Using MySQLi (Procedural Method)

In this method, we connect to the MySQL server without creating an object of the MySQLi.

<?php
$server = "localhost";
$username = "root";
$password = "";
$dbname = "journaldev";

$conn = mysqli_connect($server, $username, $password, $dbname);
if ($conn) {
    echo "Connection Successful by mysqli procedural method";
} else {
    echo "Connection Failed " . mysqli_connect_error();
}

mysqli_close($conn);
?>Code language: PHP (php)

Here, we have first specified all the credentials such as server name, username and password of the database and database name. Then, we used the mysqli_connect() method to connect the PHP program with the server.

Then we used the IF condition to check if the connection is successful. If the connection is not successful, we print the message that the connection failed along with the error message generated using the mysqli_connect_error() function.

Connect Using MySQLi (Object-Oriented Method)

<?php
$server = "localhost";
$username = "root";
$password = "";
$dbname = "journaldev";

$conn = new mysqli($server, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} else {
    echo "Connected successfully by mysqli object-oriented method";
}

$conn->close();
?>Code language: PHP (php)

Connect Using PDO

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "journaldev";
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // set the PDO error mode to exception
    echo "Connected successfully using PDO";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

$conn= NULL;

?>Code language: PHP (php)

The PDO offers an exception handling class that is useful to handle any problem that occurs in our database. If there is any problem with the MySQL query written inside the try block, the PHP program will stop its execution right there and control will go to the catch block.

Closing the Connection

Note that, whatever method you use to connect the MySQL database to the PHP, don’t forget to close the MySQL connection. It is because, the connection between a client process and a database can be reused by a client process, rather than being created and destroyed multiple times. This reduces the overhead of creating fresh connections every time one is required, as unused connections are cached and ready to be reused.

Conclusion

In this tutorial, we learned to connect the MySQL server with the PHP program by both methods- MySQLi and PDO. You are free to use any method in your project. However, it is good to have the knowledge and a little bit of information about other methods too.