How to Use MySQL with PHP

MySQL and PHP are a powerful duo for building dynamic, database-driven websites and applications. Whether you're creating a blog, an e-commerce platform, or a custom web application, understanding how to integrate MySQL with PHP is a fundamental skill for web developers. In this guide, we’ll walk you through the process of connecting PHP to a MySQL database, running queries, and managing data effectively.

Why Use MySQL with PHP?

Before diving into the technical details, let’s quickly explore why MySQL and PHP are such a popular combination:

1. Open Source: Both MySQL and PHP are open-source technologies, making them cost-effective and widely supported.
2. Ease of Use: PHP offers built-in functions to interact with MySQL, simplifying database operations.
3. Scalability: MySQL is highly scalable, making it suitable for small projects and large-scale applications alike.
4. Community Support: With millions of developers using PHP and MySQL, you’ll find extensive documentation, tutorials, and forums to help you troubleshoot issues.

Now, let’s get started with the practical steps.

---

Step 1: Setting Up Your Environment

Before you can use MySQL with PHP, you need to set up your development environment. Here’s what you’ll need:

1. PHP: Install PHP on your server or local machine. You can download it from php.net.
2. MySQL: Install MySQL Server. You can download it from mysql.com.
3. Web Server: Use a web server like Apache or Nginx. Alternatively, you can use an all-in-one solution like XAMPP, WAMP, or MAMP, which includes PHP, MySQL, and Apache.

Once your environment is ready, you can start writing PHP code to interact with MySQL.

---

Step 2: Connecting PHP to MySQL

To connect PHP to a MySQL database, you can use the mysqli extension or the more modern PDO (PHP Data Objects). Here’s how to establish a connection using both methods:

Using mysqli:

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database = "test_db";

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

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

Using PDO:

<?php
$dsn = "mysql:host=localhost;dbname=test_db";
$username = "root";
$password = "";

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Both methods are effective, but PDO is preferred for its flexibility and support for multiple database types.

---

Step 3: Creating a MySQL Database and Table

Before you can store or retrieve data, you need to create a database and table. You can do this using PHP or directly in MySQL.

Creating a Database:

CREATE DATABASE test_db;

Creating a Table:

USE test_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

---

Step 4: Inserting Data into the Database

To insert data into your MySQL table using PHP, you can use the following code:

Using mysqli:

<?php
$name = "John Doe";
$email = "[email protected]";

$sql = "INSERT INTO users (name, email) VALUES ('$name', '$email')";

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}
?>

Using PDO:

<?php
$name = "John Doe";
$email = "[email protected]";

$sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
$stmt = $pdo->prepare($sql);

$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);

if ($stmt->execute()) {
    echo "New record created successfully";
} else {
    echo "Error: " . $stmt->errorInfo()[2];
}
?>

---

Step 5: Retrieving Data from the Database

Fetching data from the database is just as important as inserting it. Here’s how you can retrieve data:

Using mysqli:

<?php
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
    }
} else {
    echo "0 results";
}
?>

Using PDO:

<?php
$sql = "SELECT id, name, email FROM users";
$stmt = $pdo->query($sql);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "ID: " . $row["id"] . " - Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
}
?>

---

Step 6: Updating and Deleting Data

Updating Data:

<?php
$sql = "UPDATE users SET name = 'Jane Doe' WHERE id = 1";

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}
?>

Deleting Data:

<?php
$sql = "DELETE FROM users WHERE id = 1";

if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $conn->error;
}
?>

---

Best Practices for Using MySQL with PHP

1. Use Prepared Statements: Always use prepared statements to prevent SQL injection attacks.
2. Validate User Input: Sanitize and validate all user inputs before using them in queries.
3. Close Connections: Always close your database connection when it’s no longer needed.
4. Use Environment Variables: Store sensitive information like database credentials in environment variables instead of hardcoding them.

---

Conclusion

Integrating MySQL with PHP is a crucial skill for any web developer. By following the steps outlined in this guide, you can connect to a MySQL database, perform CRUD (Create, Read, Update, Delete) operations, and build robust, data-driven applications. As you gain more experience, you can explore advanced topics like database optimization, indexing, and using frameworks like Laravel to streamline development.

Start experimenting with PHP and MySQL today, and unlock the potential to create dynamic, interactive web applications!