Free and open-source MySQL is one of the most popular relational database management systems. It is used to store the content of websites and web applications. If you want to integrate a PHP-based web application with MySQL, you will need to test whether PHP is able to connect to MySQL and execute database queries. To retrieve data from MySQL, you must establish a connection between the database and a PHP script. There are two ways to establish a connection between the database and a PHP script.

In this post, we will explain:

  • How to Connect to MySQL Using PHP MySQLi Extension
  • How to Connect to MySQL Using PHP Data Objects (PDO)

Install LAMP Stack

Before starting, a LAMP stack must be installed on your server, If not installed, you can install it with the following command:

apt-get install apache2 mysql-server php libapache2-mod-php php-mysqli php-pdo -y

Once the LAMP stack is installed, start and enable the Apache and MySQL service:

systemctl start apache2 mysql
systemctl enable apache2 mysql

Also Read

How to Install Linux, Apache, MySQL, PHP on Ubuntu 20.04

Configure MySQL Database

Next, you will need to create a test database, a table with some data to query for its contents using a PHP script.

First, connect to the MySQL shell with the following command:

mysql

Once you are connected, create a database and user with the following command:

CREATE DATABASE testdb;
CREATE USER 'testuser'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

Next, grant all the privileges to the database with the following command:

GRANT ALL ON testdb.* TO 'testuser'@'%';

Next, switch the database to testdb and create a table named people:

USE testdb;
CREATE TABLE people (
	item_id INT AUTO_INCREMENT,
	content VARCHAR(255),
	PRIMARY KEY(item_id)
);

Next, insert some data into the table:

INSERT INTO people (content) VALUES ("Hitesh Jethva");
INSERT INTO people (content) VALUES ("Jayesh Jethva");
INSERT INTO people (content) VALUES ("Vyom Jethva");
INSERT INTO people (content) VALUES ("Ninav Jethva");

Next, verify all inserted data with the following command:

SELECT * FROM people;

You will get the following output:

+---------+---------------+
| item_id | content       |
+---------+---------------+
|       1 | Hitesh Jethva |
|       2 | Jayesh Jethva |
|       3 | Vyom Jethva   |
|       4 | Ninav Jethva  |
+---------+---------------+

Next, flush the privileges and exit from the MySQL with the following command:

FLUSH PRIVILEGES;
EXIT;

Also Read

How to Delete or Remove Databases in MySQL

Connect to MySQL Using PHP MySQLi

MySQLi is a MySQL extension that supports MySQL databases and allows users to connect to MySQL and access its content.

First, create a PHP script inside the Apache web root directory for connecting to a MySQL database using MySQLi:

nano /var/www/html/php-mysqli-connection.php

Add the following code:

<?php
$servername = "localhost";
$database = "testdb";
$username = "testuser";
$password = "password";
$table = "people";

$conn = mysqli_connect($servername, $username, $password, $database);

if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

echo "Connected to MySQL Using Mysqli Successfully";
  echo "<h2>List Table Content</h2><ol>"; 
  foreach($conn->query("SELECT content FROM $table") as $row) {
    echo "<li>" . $row['content'] . "</li>";
  }
  echo "</ol>";
mysqli_close($conn);

?>

Save and close the file when you are done.

Now, open your web browser and access the PHP script using the URL http://your-server-ip/php-mysqli-connection.php. If PHP is connected to the MySQL server, you will get the table contents on the following screen:
Connect PHP to MySQL with MySQLi

Connect to MySQL Using PHP PDO

PDO, also called “PHP Data Objects,” is a PHP extension used for connecting to MySQL databases.

First, create a PHP script inside the Apache web root directory for connecting to a MySQL database using PDO:

nano /var/www/html/php-pdo-connection.php

Add the following code:

<?php

$servername = "localhost";
$database = "testdb";
$username = "testuser";
$password = "password";
$table = "people";
$charset = "utf8mb4";

try {

$dsn = "mysql:host=$servername;dbname=$database;charset=$charset";
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

echo "Connected to MySQL Using PDO Successfully";
  echo "<h2>List Table Content</h2><ol>"; 
  foreach($pdo->query("SELECT content FROM $table") as $row) {
    echo "<li>" . $row['content'] . "</li>";
  }
  echo "</ol>";
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

?>

Save and close the file when you are done.

Now, open your web browser and access the PHP script using the URL http://your-server-ip/php-pdo-connection.php. If PHP is connected to the MySQL server, you will get the table contents on the following screen:
Connect PHP to MySQL with PDO

Conclusion

In this post, we explained two ways to connect to a MySQL database using PHP. Both PDO and MySQLi are very useful PHP extensions. However, the MySQLi extension is only used for MySQL databases, while the PDO extension works with 12 different databases. Try it on VPS hosting from Atlantic.Net!