A Beginner’s Guide to Working with Databases in PHP

Where necessary, you may need to have access to a VPS server so you can follow how to implement the steps in this article.  You can get a cheaper VPS Server from Contabo with 4vCPU cores, 8GM RAM, and 32TB Bandwidth for less than $5.50 per month. Get this deal here now

Table of Contents

Cloud VPS S

$5.50 Monthly
  • 4 vCPU Cores | 8GB RAM


$15.50 Monthly
  • 6 vCPU Cores | 16GB RAM


$17.50 Monthly
  • 8 vCPU Cores | 24GB RAM

PHP applications are capable of interacting with databases to store and retrieve data. This guide will walk you through the basics of working with databases in PHP, focusing on database connectivity.

MysSQLi and Database Connectivity in PHP

Before diving into the code, it’s crucial to understand the concept of database connectivity. PHP relies on several extensions to interact with databases, and the most commonly used one is MySQLi.

MySQLi, short for MySQL Improved, is a PHP extension specifically designed to work with the MySQL database. It provides an improved and modernized interface for interacting with MySQL databases in PHP applications. MySQLi was introduced as an enhancement over the original MySQL extension to address certain limitations and offer additional features.

On Linux, installing the MySQLi extension for PHP involves installing the necessary packages through the package manager. The specific commands may vary slightly depending on the Linux distribution you’re using. Here are instructions for a few popular distributions:


sudo apt-get update
sudo apt-get install php-mysql


sudo yum install php-mysqlnd


sudo dnf install php-mysqlnd

Arch Linux

sudo pacman -S php-mysql


sudo zypper install php-mysql

Generic Installation (for custom configurations)

If you have a custom PHP installation or need to configure PHP with MySQLi manually, you can use the pecl command to install the MySQLi extension:

sudo pecl install mysqli

After installing or enabling the MySQLi extension, you need to restart the web server to apply the changes. The following commands can be used to restart Apache or Nginx:

  • For Apache:
sudo service apache2 restart   # or systemctl restart apache2
  • For Nginx
sudo service nginx restart   # or systemctl restart nginx

To confirm that MySQLi has been successfully installed, you can create a PHP file (e.g., info.php) with the following content and access it through a web browser:


Look for the MySQLi section on the PHP information page, which should indicate that MySQLi is enabled and configured.

The above instructions assume you already have PHP installed on your Linux system. If PHP is not installed, you can install it along with the MySQLi extension using the appropriate package manager commands.

Establishing Connection

To work with a database in PHP, you need to establish a connection first. The mysqli_connect() function is used for this purpose. Here’s an example:

$servername = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

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

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
echo "Connected successfully";

The above code begins by defining four variables: $servername, $username, $password, and $database. These represent the server where the database is hosted (localhost in this case), the username and password to access the database, and the name of the specific database to connect to.

The mysqli_connect() function is then used to create a connection to the MySQL database. It takes the server name, username, password, and database name as parameters and returns a connection object ($conn). This object will be used for executing queries and interacting with the database.

The code then checks if the connection was successful using an if statement. If $conn is not true (meaning the connection failed), it executes the die function, which terminates the script and prints an error message indicating the reason for the connection failure, retrieved using mysqli_connect_error().

If the connection is successful, the script echoes “Connected successfully.” This message indicates that the PHP application has successfully connected to the MySQL database.

You’re free to replace “your_username,” “your_password,” and “your_database” with your actual database credentials.

Executing SQL Queries

Once the connection is established, you can execute SQL queries to interact with the database. Here’s an example of inserting data into a table:

$sql = "INSERT INTO users (username, email, password) VALUES ('john_doe', '[email protected]', 'hashed_password')";

if (mysqli_query($conn, $sql)) {
    echo "Record inserted successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);

This example inserts a new record into the “users” table. Replace the values with your data and modify the SQL query accordingly.

Retrieving Data

Fetching data from the database is a common operation. The mysqli_query() function is used to execute SELECT queries. Here’s an example:

$sql = "SELECT username, email FROM users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_assoc($result)) {
        echo "Username: " . $row["username"] . " - Email: " . $row["email"] . "<br>";
} else {
    echo "0 results";


This script retrieves usernames and emails from the “users” table and displays them. Customize the SQL query based on your database structure.

Deleting Data

This script deletes the user with the username “john_doe” from the “users” table.

$sql = "DELETE FROM users WHERE username='john_doe'";

if (mysqli_query($conn, $sql)) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . mysqli_error($conn);

Closing the Connection

Finally, it’s good practice to close the database connection using the mysqli_close() function:


Closing the connection is essential to free up resources and avoid potential security risks.

Hire us to handle what you want

Hire us through our Fiverr Profile and leave all the complicated & technical stuff to us. Here are some of the things we can do for you:

  • Website migration, troubleshooting, and maintenance.
  • Server & application deployment, scaling, troubleshooting, and maintenance
  • Deployment of Kubernetes, Docker, Cloudron, Ant Media, Apache, Nginx,  OpenVPN, cPanel, WHMCS, WordPress, and more
  • Everything you need on AWS, IBM Cloud, GCP, Azure, Oracle Cloud, Alibaba Cloud, Linode, Contabo, DigitalOcean, Ionos, Vultr, GoDaddy, HostGator, Namecheap, DreamHost, and more.

We will design, configure, deploy, or troubleshoot anything you want. Starting from $10, we will get your job done in the shortest time possible. Your payment is safe with Fiverr as we will only be paid once your project is completed.