MySQL with PHP

PHP

⏱ 25 min read📖 BeginnerNot completed

PHP and MySQL are a classic combination. In this lesson you'll learn how to connect to a database and perform CRUD operations (Create, Read, Update, Delete) using PDO — the modern, secure way.

Connecting with PDO

PHP

<?php
$host = "localhost";
$dbname = "myapp";
$username = "root";
$password = "secret";

try {
    $pdo = new PDO(
        "mysql:host=$host;dbname=$dbname;charset=utf8",
        $username,
        $password,
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
    );
    echo "Connected successfully!";
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

SELECT — Read Data

PHP

<?php
// Get all users
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($users as $user) {
    echo $user["name"] . " — " . $user["email"] . "<br>";
}

// Get single user by ID (prepared statement - safe!)
$id = 1;
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$id]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);
echo $user["name"];
?>

INSERT — Create Data

PHP

<?php
$name  = "John Doe";
$email = "john@example.com";
$password = password_hash("secret123", PASSWORD_DEFAULT);

$stmt = $pdo->prepare(
    "INSERT INTO users (name, email, password) VALUES (?, ?, ?)"
);
$stmt->execute([$name, $email, $password]);

$newId = $pdo->lastInsertId();
echo "Created user with ID: $newId";
?>

UPDATE — Modify Data

PHP

<?php
$id    = 1;
$name  = "Jane Doe";
$email = "jane@example.com";

$stmt = $pdo->prepare(
    "UPDATE users SET name = ?, email = ? WHERE id = ?"
);
$stmt->execute([$name, $email, $id]);

echo "Updated " . $stmt->rowCount() . " row(s)";
?>

DELETE — Remove Data

PHP

<?php
$id = 1;
$stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
$stmt->execute([$id]);

echo "Deleted " . $stmt->rowCount() . " row(s)";
?>

Why Prepared Statements?

PHP — SQL Injection Prevention

<?php
// DANGEROUS — SQL Injection vulnerable!
$id = $_GET["id"];  // attacker sends: 1 OR 1=1
$sql = "SELECT * FROM users WHERE id = $id";
// This becomes: SELECT * FROM users WHERE id = 1 OR 1=1
// Returns ALL users!

// SAFE — Prepared statement
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$id]);  // $id is escaped automatically
?>

💡

In Laravel, Eloquent handles all of this for you! User::all(), User::find($id), User::create($data), $user->update($data), $user->delete() — all with built-in SQL injection protection.

🎉

Congratulations! You've completed the PHP Basics track! You now know enough PHP to start learning Laravel. Head to the Laravel lessons next!

← Previous Lesson All Lessons →

🧠

Test your knowledge!

Take the PHP Basics quiz.

Take Quiz →