3 Methods to Connect to MySQL from PHP using Example Code

To get most out of your MySQL database, it is important to understand how to connect from your custom PHP program to MySQL database.

This tutorial explains the following three methods along with appropriate example PHP program, which will explain how to connect from your PHP to MySQL database.

• Connect using mysqli extension (Recommended)
• Connect using PDO (Recommended)
• Connect using traditional legacy mysql_ functions (Deprecated)

For this, you should install php-mysql package.

On RedHat based distro including CentOS, use yum to install php-mysql as shown below.

yum install php-mysql

Depending on your system, the above will either install or update the following dependencies:
php
php-cli
php-common
php-pdo
php-pgsql

Once it is installed, the phpinfo page will display the mysql module as shown below:

For all the examples below, we’ll be connecting to a MySQL database that already exists. If you are new to MySQL, this is a good place to start: MySQL Beginners Guide

Note: Everything that is explained here will also work with MariaDB, as it is same as MySQL.

1. Connect from PHP Using mysqli Extension

mysqli stands for MySQL Improved.

Please note that on most distros (for example: CentOS), php-mysqli is already part of the php-mysql package. So, you don’t have to search and look for the php-mysqli package. All you have to do is install the php-mysql package to get the mysqli extension working on your system.

Create the following mysqli.php file under the Apache DocumentRoot:

<?php
  $conn = new mysqli("localhost", "root", "mySecretDBpass", "thegeekstuff");
  
  if ($conn->connect_error) {
    die("ERROR: Unable to connect: " . $conn->connect_error);
  } 

  echo 'Connected to the database.<br>';

  $result = $conn->query("SELECT name FROM employee");

  echo "Number of rows: $result->num_rows";

  $result->close();

  $conn->close();
?>

In the above:

• mysqli – This function will initiate a new connection using mysqli extension. This function will take these four arguments
  1. hostname where the MySQL database is running
  2. MySQL username to connect
  3. Password for the mysql user
  4. MySQL database to connect.
• query function – Use this to specify your MySQL query. In this example, we are selecting the name column from employee database.
• Finally, we are displaying the number of rows selected using the num_rows variable in the result. We are also closing both the result and connection variable as shown above.

When you call the above mysqli.php from your browser, you’ll see the following output, which indicates that PHP was able to connect to the MySQL database and fetch data.

Connected to the database.
Number of rows: 6

Note: If you are trying to connect to a remote MySQL database, then you may have to do this to avoid host not allowed error message: How to Allow MySQL Client to Connect to Remote MySQL server

2. Connect from PHP MySQL PDO Extension

PDO stands for PHP Data Objects.

PDO_MYSQL drive implements the PDO interface provided by PHP to connect from your PHP program to MySQL database.

On most Linux distros (for example: CentOS and RedHat), php-pdo package is already part of the php-mysql package. So, you don’t have to search and look for the php-pdo package. All you have to do is install the php-mysql package to get the PDO_MYSQL PHP extension working on your system.

Create the following mysql-pdo.php file under your Apache DocumentRoot:

<?php
  try {
    $conn = new PDO("mysql:host=localhost;dbname=thegeekstuff", "root", "mySecretDBpass");

    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo 'Connected to the database.<br>';

    $sql = 'SELECT name FROM employee';
    
    print "Employee Name:<br>";
    foreach ($conn->query($sql) as $row) {
        print $row['name'] . "<br>";
    }
    $conn = null;

  }
  catch(PDOException $err) {
    echo "ERROR: Unable to connect: " . $err->getMessage();
  }
?>

In the above:

• new PDO – This will create a new PDO object which will take the following three arguments:
  1. mysql connect string: This will be in the format of “mysql:host=$hostname;dbname=$dbname”. In the above example, the db is running on localhost and we are connecting to thegeekstuff database.
  2. MySQL username to connect
  3. Password for the mysql user
• $sql variable – Create the sql query that you want to execute. In this example, we are selecting name column from employee table.
• query($sql) – Here we are executing the sql query that we just created.
• foreach – Here, we are looping through the output from the above query command and storing it in the $row variable, and then we are displaying it using print command.
• In MySQL PDO, to close the connection, simply set the $conn variable to null value.

When you call the above mysqli.php from your browser, you’ll see the following output, which indicates that PHP was able to connect to the MySQL database and fetch data.

Connected to the database.
Employee Name:
Thomas
Jason
Mayla
Nisha
Randy
Ritu

3. Connect from PHP Using Legacy mysql_ Functions (Deprecated)

Use this method only if you are using an older version of PHP and cannot upgrade it to the new version for some reason.

It is recommended that you use the Method#2 and Method#3 shown above instead of this method. I’ve included this method only for reference, and not as a recommendation to use.

This particular extension was deprecated as of PHP 5.5 version. But starting from PHP 7.0 version, this will not even work, as it was removed.

Starting from PHP 5.5 version, when you use these functions, it will generate E_DEPRECATED error.

Create the following mysql-legacy.php file under the Apache DocumentRoot:

<?php
$conn = mysql_connect('localhost', 'root', 'mySecretDBpass');
mysql_select_db("thegeekstuff");
if (!$conn) {
die('ERROR: Unable to connect: ' . mysql_error());
}

echo 'Connected to the database.<br>';

$result = mysql_query('SELECT name FROM employee');

$row = mysql_fetch_row($result);
echo "Employee 1: ", $row[0], "<br>\n";

mysql_close($conn);
?>

In the above:

• mysql_connect function takes three argument: 1) hostname where the MySQL database is running 2) MySQL username to connect 3) Password for the mysql user. Here it is connecting to the MySQL database that is running on the local server using username root and its password.
• mysql_select_db function – As the name suggests, this will pick the database that you want to connect to. This is equivalent to the “use” command. In this example, we are connecting to the database thegeekstuff.
• mysql_query function – Use this to specify your MySQL query. In this example, we are selecting the name column from employee database.
• mysql_fetch_row – Use this function to fetch the rows from the SQL query that we just created.
• Finally close the connection using mysql_close command as shown above.

When you call the above mysql-legacy.php from your browser, you’ll see the following output, which indicates that PHP was able to connect to the MySQL database and fetch data.

Connected to the database.
Employee 1: Thomas