How to connect to MySQL database in PHP

Introduction

In modern web development, the ability to interact with databases is crucial. PHP, being a server-side scripting language, is extensively used to create dynamic web pages that interact with databases. One of the most popular databases is MySQL. This tutorial shows you how to establish a connection between PHP and a MySQL database.

Prerequisites:

• A local or remote MySQL server is running.
• Access to a MySQL user account with the necessary privileges.
• PHP is installed and configured appropriately on your system.
• Basic knowledge of PHP and MySQL.

Step 1: Create a Database and User in MySQL

Before connecting to MySQL from PHP, you must have a database. Use the following SQL queries to create a new database and user:

CREATE DATABASE exampleDB;
USE exampleDB;
CREATE USER 'exampleUser'@'localhost' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON exampleDB.* TO 'exampleUser'@'localhost';
FLUSH PRIVILEGES;

Step 2: Connecting to MySQL using PDO

PDO (PHP Data Objects) is a database access layer providing a uniform method of access to multiple databases.

$host = 'localhost';
$db   = 'exampleDB';
$user = 'exampleUser';
$pass = 'your_password';
$dsn = "mysql:host=$host;dbname=$db";
try {
    $pdo = new PDO($dsn, $user, $pass);
    echo 'Connection successful!';
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

Replace ‘localhost’, ‘exampleDB’, ‘exampleUser’, and ‘your_password’ with your actual database host, name, user, and user’s password. It is more secure and gives more functionality than the old mysql_connect method.

Step 3: Using MySQLi Extension

The MySQLi extension (MySQL Improved) is a relational database driver used in the PHP programming language to provide an interface with MySQL databases.

$host = 'localhost';
$db = 'exampleDB';
$user = 'exampleUser';
$pass = 'your_password';
$mysqli = new mysqli($host, $user, $pass, $db);
if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}
echo 'Connected successfully.';

Be cautious with the MySQLi extension; it’s only compatible with MySQL, whereas PDO supports multiple database systems. However, MySQLi is a good option if you work exclusively with MySQL databases and need features that are not available in PDO, like stored procedures.

Step 4: Closing the Connection

Once your application is done with the database operations, it’s a good practice to close the connection. With PDO, the connection is closed by setting the object to null. With MySQLi, you close it by calling close method:

// For PDO
$pdo = null;

// For MySQLi
$mysqli->close();

Step 5: Handling Errors

It’s important to handle potential errors that emerge from database operations. In PDO, you set the error mode to exception, which is a PDO attribute, so it throws PDOExceptions on error:

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

In MySQLi, error handling can be done using the `mysqli_connect_error()` function.

if (mysqli_connect_error()) {
    die('Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
}

Step 6: Secure Your Connection

Using prepared statements is crucial for the security of your application, as it prevents SQL injection attacks. Both PDO and MySQLi offer prepared statements.

Conclusion

By following this guide, you should have a stable and secure connection to a MySQL Database using PHP. Make sure you use PDO or MySQLi and take advantage of prepared statements. Happy coding!