How to Connect PHP to MySQL Database: A Comprehensive Guide

Connecting PHP to a MySQL database is a fundamental step in creating dynamic web applications.

MySQL is a popular relational database management system, and PHP is a powerful server-side scripting language.

In this comprehensive guide, we will walk you through the process of establishing a connection between PHP and MySQL, enabling you to interact with databases, retrieve data, and perform various database operations.

Introduction to PHP and MySQL

PHP is a versatile scripting language commonly used for web development. It can be embedded within HTML to create dynamic web pages.

MySQL, on the other hand, is a popular open-source relational database management system (RDBMS) known for its speed and reliability. When combined, PHP and MySQL form a powerful stack for building database-driven web applications.

1. Setting Up Your Environment

Before you begin, ensure that you have PHP and MySQL installed on your server or local development environment. Learn – How to Setup PHP Development Environment?

2. Creating a Database and Table

Using a tool like phpMyAdmin or the MySQL command line, create a database and table that you want to interact with in your PHP script. For example:

1. Create a Database called my_test_db.
2. After that, In the my_test_db create a table called users that has four columns (id, name, age, email)

Run the following SQL code on the database (my_test_db) to create the users table and its columns.

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) NOT NULL,
  `email` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

3. PHP MySQL Connection

Database (DB) information

To establish a connection between PHP and MySQL, you can use the mysqli extension or PDO extension. Here are three examples of connecting to the MySQL database:

4. Executing SQL Queries

With a successful connection, you can now execute SQL queries. Here are few examples of inserting data into the users table:

<?php
$db_host = 'localhost';
$db_user = 'root';
$db_password = '';
$db_name = 'my_test_db';

$connection = mysqli_connect($db_host, $db_user, $db_password, $db_name);

// SQL Query to Insert user
$sql = "INSERT INTO `users` (`name`,`age`,`email`) VALUES ('John Doe',22,'[email protected]')";

// Executing the SQL Query 
$query = mysqli_query($connection, $sql);

if ($query) {
    echo "User has been successfully Inserted.";
}

<?php
$db_host = 'localhost';
$db_user = 'root';
$db_password = '';
$db_name = 'my_test_db';

$connection = new mysqli($db_host, $db_user, $db_password, $db_name);

// SQL Query to Insert user
$sql = "INSERT INTO `users` (`name`,`age`,`email`) VALUES ('John Doe',22,'[email protected]')";

// Executing the SQL Query 
$query = $connection->query($sql);

if ($query) {
    echo "User has been successfully Inserted.";
}

<?php
$db_host = 'localhost';
$db_user = 'root';
$db_password = '';
$db_name = 'my_test_db';

// The Data Source Name, or DSN, contains the information required to connect to the database.
$dsn = "mysql:host=$db_host;dbname=$db_name;charset=utf8";

$connection = new PDO($dsn, $db_user, $db_password);

// SQL Query to Insert user
$sql = "INSERT INTO `users` (`name`,`age`,`email`) VALUES ('John Doe',22,'[email protected]')";

// Executing the SQL Query 
$query = $connection->query($sql);

if ($query->rowCount()) {
    echo "User has been successfully Inserted.";
}

5. Handling Errors

It’s essential to handle errors gracefully. You can use conditional statements to check for errors after executing queries. Additionally, consider implementing try-catch blocks for more advanced error handling.

Handling MySQLi Errors:

Before diving into MySQLi-specific error handling, it’s essential to configure PHP to display errors and exceptions during development. You can do this by adding the following lines to the top of your PHP script:

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

These lines enable error reporting, including all types of errors and notices. It’s crucial to address these issues during development to ensure your code runs smoothly.

PDO Error Handling:

By default, PDO does not throw exceptions for database errors. To enable PDO to throw exceptions when errors occur, you can set the error mode to PDO::ERRMODE_EXCEPTION using the setAttribute method. This mode allows you to catch and handle exceptions gracefully.

Here’s how to enable exception mode when creating a PDO connection:

try {
    $pdo = new PDO("mysql:host=localhost;dbname=your_database", "your_username", "your_password");
    // Enabling PDO to Throw Exceptions
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}

6. Closing the Database Connection

The PHP database connection will be closed automatically when the script ends. But if you want to close it earlier, PHP also allows you to close the DB connection at any time.

MySQLi Procedural Close Connection:

mysqli_close($connection);

MySQLi OOP Close Connection:

$connection->close();

PDO Close Connection:

To close the DB connection in PHP PDO, you have to set the connection variable to null, or you can use the unset() function to unset the connection variable.

$connection = null;
// OR
unset($connection);

7. Best Practices and Security

• Use prepared statements to prevent SQL injection attacks.
• Avoid storing database credentials directly in your code; use environment variables or configuration files.
• Implement proper error handling and logging to troubleshoot issues.
• Regularly back up your databases to prevent data loss.

In this guide, we’ve covered the basics of setting up your environment, creating a database, establishing a connection, executing queries, and handling errors. As you continue your journey in web development, you’ll discover the limitless possibilities of building dynamic, data-driven web applications with PHP and MySQL.