How to Connect PHP to a MySQL Database

Connecting PHP to a MySQL database is a foundational skill that is crucial for any web developer. Whether you are developing a small personal website or a complex web application, PHP and MySQL work seamlessly together to provide an efficient and dynamic solution. This comprehensive guide will walk you through the process of establishing a connection between PHP and a MySQL database.

Prerequisites

Before you start, ensure that you have:

1. PHP installed on your server.
2. MySQL server up and running.
3. Access credentials for the MySQL database.

You might also want to know how to change the MySQL root password or how to insert data into a MySQL table.

Step-by-Step Guide

Step 1: Setting Up the Database

First, you need to create a database and a table that you will connect to. Open the MySQL command line using your credentials and use the following command to create a database:

CREATE DATABASE myDatabase;
USE myDatabase;

Step 2: Create a MySQL Table

For demonstration purposes, let's create a simple table called users.

CREATE TABLE users (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL
);

Step 3: Connecting PHP to MySQL

PHP provides several ways to connect to a MySQL database, but we will focus on using MySQLi (MySQL Improved) and PDO (PHP Data Objects).

Using MySQLi

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";

Using PDO

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully"; 
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

Important Security Considerations

While establishing a connection is important, security cannot be overlooked. SQL injection is a common security threat that can be mitigated by using prepared statements and parameterized queries. Learn more about how to prevent SQL injection in PHP.

Additionally, consider connecting MySQL over SSL in PHP for secure data transmission.

Next Steps

With your connection established, you can now perform various database operations. For instance, you can learn how to insert blob data in MySQL using PHP or retrieve information to display within your web application.

Mastering the connection between PHP and MySQL opens the door to dynamic web development. With these basics, you're ready to build more robust and interactive applications.