Introduction: Why PHP and MySQL Are Still a Power Duo

If you're building any dynamic web application—from a basic contact form to a full-featured content management system—you need a reliable way to store, retrieve, and manipulate data. This is where PHP and MySQL come in.

PHP, a server-side scripting language, pairs seamlessly with MySQL, an open-source relational database management system. Together, they form one of the most widely-used stacks in web development.

In this article, we’ll dive into how to use PHP with MySQL—from establishing connections to executing secure queries. Whether you're a beginner or brushing up your skills, this guide will get you up to speed.

Setting Up: Prerequisites

To follow along, make sure you have the following:

• A web server (like Apache)

• PHP installed (v7.0+ recommended)

• MySQL or MariaDB

• A code editor (e.g., VS Code)

• A database tool (e.g., phpMyAdmin, MySQL Workbench)

Tip: Use XAMPP or MAMP to get a bundled setup of Apache, PHP, and MySQL.

Connecting to a MySQL Database in PHP

Using MySQLi (Improved)

<?php
// MySQL server credentials
$servername = "localhost";
$username = "root";
$password = "";
$database = "demo_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 Data Objects)

<?php
try {
    $pdo = new PDO("mysql:host=localhost;dbname=demo_db", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully (PDO)";
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

✅ Use PDO if you want flexibility (supports multiple DBs) and built-in protection against SQL injection.

CRUD Operations with MySQLi

Let’s explore Create, Read, Update, Delete—the core of any database operation.

1. Create (Insert Data)

$sql = "INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]')";
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
}

2. Read (Retrieve Data)

$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>";
    }
}

3. Update Data

$sql = "UPDATE users SET name='Jane Doe' WHERE id=1";
if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
}

4. Delete Data

$sql = "DELETE FROM users WHERE id=1";
if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
}

Complete Functional Example

Here’s a complete script that connects to a database, creates a table, inserts a record, and fetches it.

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

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

// Check connection
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);

// Create table
$conn->query("CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50)
)");

// Insert data
$conn->query("INSERT INTO users (name, email) VALUES ('Alice', '[email protected]')");

// Read data
$result = $conn->query("SELECT * FROM users");
while($row = $result->fetch_assoc()) {
    echo "ID: ".$row['id']." - Name: ".$row['name']." - Email: ".$row['email']."<br>";
}

$conn->close();
?>

Tips & Common Pitfalls

✅ Best Practices

• Use prepared statements to prevent SQL injection.

• Sanitize user input before using it in queries.

• Use PDO for flexibility and better error handling.

• Always close connections to free up server resources.

• Check if the database exists before running queries.

⚠️ Common Pitfalls

Feature Comparison Table: MySQLi vs PDO

Conclusion: Your Next Steps

Using PHP with MySQL is a foundational skill for any web developer. Whether you're building login systems, dashboards, or full CMS platforms, mastering these basics will unlock serious backend power.

Actionable Takeaways

• Start with MySQLi, then progress to PDO for better flexibility.

• Practice CRUD operations regularly.

• Always validate and sanitize inputs.

• Explore advanced topics like joins, transactions, and indexes.