How to Connect PHP to MySQL Database

In this tutorial, we will learn how to connect PHP to a MySQL database step by step.

Connecting PHP to MySQL means establishing communication between a PHP script and a MySQL database so that you can store, retrieve, update, and delete data in the database.

For example, when a user submits a registration form, PHP stores the user’s data in the MySQL database. Similarly, when a user logs in, PHP retrieves and checks the user’s data from the MySQL database to verify their credentials. So, let us learn how to connect PHP with a MySQL database.

How to Connect PHP to MySQL?

Before connecting PHP to MySQL database, you must set up some essential tools and a local server environment. Without these, PHP cannot communicate with MySQL. So, you must follow all these steps carefully:

Step 1: Install a Web Server

Since PHP is a server-side programming language, you first need to create a web server on your system to run PHP files in a browser. PHP runs on the server, not directly in the browser.

The web server executes PHP code and sends the output to your browser. The most commonly used web server software includes:

• Apache (most common and beginner-friendly)
• Nginx (lightweight and high-performance)

Easiest Method for Beginners

Install any one local server like XAMPP / WAMP / MAMP. Here, we will use XAMPP from Apache Friends because it installs everything together. XAMPP includes:

• Apache (Web Server)
• MySQL (Database)
• PHP (Programming language)
• phpMyAdmin (Database management tool)

How to Install XAMPP Server?

1. Go to XAMPP official website https://www.apachefriends.org.
2. Download XAMPP for Windows.
3. Install it normally.
4. Open XAMPP Control Panel.
5. Start these services:
   • Apache → Click Start → must be Running (Green)
   • MySQL → Click Start

Now your local server is ready and running on your computer system.
[blocksy-content-block id=”12371″]

Step 2: Install and Verify PHP

If you installed XAMPP on your system, PHP is already installed. To verify the working of PHP, follow these steps:

• Open XAMPP folder and go to C:\xampp\htdocs. This folder is called the root directory because Apache only reads files from this folder.
• Create a file with the name test.php. Here, test is file name and .php is the file extension that tells the server it is a PHP file.
• Write this code inside the PHP file:

<?php
phpinfo();
?>

• Open browser and go to http://localhost/test.php.
• You will see PHP information page. This means PHP is installed successfully.

Step 3: Install MySQL Database

If you installed the XAMPP server, MySQL is already installed. To verify whether MySQL is running or not, follow these steps:

• Open XAMPP Control Panel.
• Check the MySQL module.
• It should show green color and the status “Running”.
• If it is not running, click the Start button.

Step 4: Create Database

You can create a database in two ways:

• Using phpMyAdmin (Easy – GUI method)
• Using MySQL Command Line (Manual – SQL method)

Method 1: Create Database Using phpMyAdmin

phpMyAdmin is a tool used to manage MySQL databases easily. This is the easiest and most popular method. To create database using phpMyAdmin, follow all these steps:

• Open your browser and type: http://localhost/phpmyadmin
• Press Enter.
• phpMyAdmin dashboard will open in your browser.
• Click New (left side panel).
• Enter database name (Example: scientecheasy_db)
• Choose Collation. However, you can also leave default.
• Click Create.
• Database is successfully created.
• You will see your database name in left panel.

Method 2: Create Database Using MySQL Command Line

This method uses SQL commands and is useful for learning. Follow all these steps:

• Open XAMPP Control Panel.
• Click Shell. Or, open Command Prompt.
• Go to MySQL bin folder. Example: cd C:\xampp\mysql\bin
• Now login to MySQL. Type mysql -u root -p
• Press Enter.
• If the password is blank (default XAMPP), just press Enter again.
• To create database, type this sql commond: CREATE DATABASE student_db;
• Press Enter.
• Database with name student_db is created successfully. You will see your database in the list (left panel side).
• If not, refresh the page.

Which Method Should You Use?

• If you are a beginner, choose phpMyAdmin.
• If you are a developer or an advanced user, use Command Line.
• However, you should use and understand both methods for learning.
• Both methods do the same work.

Database Connection Details

You need four important parameters:

Example:

$host = "localhost";
$username = "root";
$password = "";
$database = "testdb";

[blocksy-content-block id=”12121″]

Methods to Connect PHP to MySQL Database

PHP provides two built-in extensions to connect PHP with a MySQL database. These extensions are secure, efficient, and supported in modern PHP versions.

• MySQLi (The letter ‘i’ is an abbreviation for improved)
• PDO (PHP Data Objects)

Should You Use MySQLi or PDO?

You can use whichever you prefer because both are safe and widely used. However, both MySQLi and PDO have their own advantages.

• PDO can work with multiple database systems (about 12 different databases), while MySQLi works only with the MySQL database.
• Both PDO and MySQLi support Object-Oriented Programming (OOP). However, MySQLi also offers Procedural Programming, which many beginners find easier to understand.
• Both support Prepared Statements. Prepared statements help to:
  • Prevent SQL Injection
  • Improve the security of web applications
  • Make database queries safer

Prepared statements are strongly recommended when working with user input.

If you plan to switch databases for your project in the future, PDO makes the process easier. You mainly need to change the connection settings and some queries.

With MySQLi, you usually need to rewrite most of the code, including queries, when switching to another database. Now let us understand both methods one by one.

Connect PHP to MySQL using MySQLi

PHP provides a built-in mysqli_connect() function to connect PHP to MySQL database. This function establishes a connection between a PHP script and a MySQL database. The general syntax to define mysqli_connect() is:

mysqli_connect(host, username, password, database, port, socket);

Parameters of mysqli_connect()

The mysqli_connect() function accepts the following parameters:

Return Value

The mysqli_connect() function returns a connection object on success. Otherwise, it returns false on failure.

Example 1: Basic Connection (Procedural)

Inside the htdocs folder, create a new file with name connect.php and paste this connection code in it.

<?php
// Define the hostname of the MySQL server.
// "localhost" means the database is running on your local computer.
$host = "localhost";

// Define the MySQL login username.
// Default username in XAMPP is "root".
$username = "root";

// Define the MySQL login password.
// Default password in XAMPP is empty ("").
$password = "";

// Define the name of the database you want to connect to.
$database = "scientecheasy_db";

// Establish a connection to the MySQL database using mysqli_connect() function.
// This function returns a connection object on success or false on failure.
$conn = mysqli_connect($host, $username, $password, $database);

// Check whether the connection was successful or not.
// If connection fails, $conn will be false.
if(!$conn)
{
// mysqli_connect_error() returns the error message.
// die() stops the script execution and displays the error message.
die("Connection failed: " . mysqli_connect_error());
}

// If connection is successful, this message will be displayed.
echo "Connected successfully";
?>

Now save this file using Ctrl + S.

Now open the browser and go to http://localhost/connect.php. If database exists, you will see the following output in your browser.

Output:

Connected successfully

In this example, we have defined a variable named $conn to store the details of database connection. We can later use this variable to run queries, such as insert, update, delete, fetch data. The variable $host tells the location of a database server.
[blocksy-content-block id=”12121″]

Example 2: Connect PHP with MySQL (Object-Oriented)

There is an object-oriented approach in MySQLi to establish a connection between a PHP script and MySQL database.

<?php
// These variables store the database login information.
$host = "localhost";
$username = "root";
$password = "";
$database = "scientecheasy_db";

// Create a new MySQLi object to establish a connection between PHP and MySQL database.
// This uses object-oriented programming (OOP) style.
// If connection succeeds, $conn will store the connection object.
$conn = new mysqli($host, $username, $password, $database);

// Check if there is a connection error.
// The connect_error property returns the error message if connection fails.
if($conn->connect_error)
{
// die() function stops the script execution and displays the error message.
die("Connection failed: " . $conn->connect_error);
}
// If connection is successful, this message will be displayed in the browser.
echo "Connected successfully";
?>

We have learned two approaches: Procedural style and Object-Oriented style. Both are correct, but OOP is preferred in modern PHP.

PHP to MySQL Connection Diagram (Internal Working)

The internal working of the PHP to MySQL connection is shown in the diagram below.

Step-by-Step Explanation

Step 1:

The user opens the URL http://localhost/test.php in the browser. The browser sends a request to the Apache web server.

Step 2:

The web server receives the request and checks for the file htdocs/test.php. If the file exists inside the htdocs folder, Apache sends the file to the PHP engine for execution.

Step 3:

PHP executes the following code:

$conn = mysqli_connect("localhost", "root", "", "scientecheasy_db");

PHP tries to establish a connection with the MySQL server using the provided credentials.

Step 4:

MySQL verifies the following credentials:

• Host → localhost
• Username → root
• Password → “”
• Database → scientecheasy_db

If these credentials are correct, the connection is established successfully. Otherwise, the connection fails if any credential is incorrect.

Step 5:

MySQL sends the response back to PHP.

• If the connection is successful, it returns a connection object.
• If the connection fails, it returns an error message.

Step 6:

PHP sends the result to the browser.

• If successful: Connected successfully.
• If failed: Connection failed: Access denied.

Connect PHP to MySQL using PDO (Best Method)

Using PDO is the best and secure method to connect PHP with a MySQL database. PDO stands for PHP Data Objects. It allows PHP to connect to multiple databases like:

• MySQL
• PostgreSQL
• SQLite
• Oracle

The main advantages of using PDO to connect PHP to MySQL are:

• More secure
• Supports prepared statements
• Supports multiple databases
• Better error handling
• Object-oriented approach

Example 3: Connect to MySQL Using PDO

The following code connects PHP to MySQL database.

<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "scientecheasy_db";

// Start a try block.
// The try block contains code that may generate an exception (error).
// If an error occurs, the catch block will handle it.
try
{
// Create a new PDO object to establish a connection between PHP and the MySQL database.
   $conn = new PDO("mysql:host=$host;dbname=$database", $username, $password);


// Set PDO error mode to exception.
// This makes PDO throw an exception if an error occurs.
// It helps in better error handling and debugging.
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Display success message if connection is established successfully.
   echo "Connected successfully";
}
// Catch block executes if an exception occurs inside the try block.
catch(PDOException $e)
{
// Display error message if connection fails.
// $e->getMessage() returns the actual error message.
   echo "Connection failed: " . $e->getMessage();
}
?>

In this example, new PDO() creates PDO connection object to establish a connection between PHP with a MySQL database.

The “mysql:host=$host;dbname=$database” is called the DSN (Data Source Name). It tells PDO which database type and database name to use. We have defined a variable named $conn that stores the PDO connection object.

The line

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

is used to handle errors. It instructs PDO to throw exceptions when an error occurs, instead of just showing warnings.

setAttribute() Method of PDO Class

The setAttribute() method in PDO is a method that configures the behavior of the PDO connection object. Its general syntax is:

public PDO::setAttribute(int $attribute, mixed $value): bool

This method takes two parameters: an attribute constant and the desired value for that attribute.

PDO::ATTR_ERRMODE

This attribute is used to control how PDO reports errors. When you set its value to PDO::ERRMODE_EXCEPTION, PDO will throw exceptions whenever a database error occurs. You can then use a try-catch block to handle these exceptions properly.

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Closing MySQLi Database Connection

If you are using MySQLi, use the mysqli_close() function or the close() method to close MySQL database connection.

Method 1: Procedural Style

mysqli_close(connection);

Example:

<?php
// Close the connection
mysqli_close($conn);
echo "<br>Connection closed";
?>

The mysqli_close($conn) method closes the database connection between PHP and MySQL database. After closing, you cannot run database queries using that connection.

Method 2: Object-Oriented Style

$conn->close(); // Close connection

Closing PDO Database Connection

In PDO, there is no close() function. You close the connection by setting the connection object to null.

$conn = null;

Example:

<?php
try
{
   $conn = new PDO("mysql:host=localhost;dbname=testdb", "root", "");
   echo "Connected successfully";
// Close connection
   $conn = null;
   echo "<br>Connection closed";
}
catch(PDOException $e) {
   echo "Connection failed: " . $e->getMessage();
}
?>

In this example, the line $conn = null; destroys the connection object. This automatically closes the database connection between PHP and MySQL.

Why Close the Connection?

It is always good programming practice to close the connection when finished because it:

• Frees server resources.
• Improves performance.
• Prevents too many open connections.
• Good programming practice.

Note that PHP automatically closes the connection when the script ends.