24. PHP MySQL Basics

What you will learn

• What a database is, and what SQL does
• How to connect with PDO
• How to read, add, change, and delete rows safely
• Why prepared statements are essential

What is a database?

A database stores organised data in tables of rows and columns, like a set of spreadsheets, and lets you search it quickly. MySQL (and the very similar MariaDB) is the database behind WordPress. You talk to it with SQL, a small language of commands.

Set up a practice database in LocalWP

1. Create a fresh LocalWP site called php-practice (or reuse your PHP practice site, not the BeingXpert one).
2. Open the site in LocalWP and click the Database tab. Note the database name, user, password, and host shown there.
3. Click Open Adminer to manage the database in your browser.
4. In Adminer, open the SQL command page and run this to create a practice table:

CREATE TABLE students (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL,
  score INT NOT NULL DEFAULT 0
);

INSERT INTO students (name, email, score) VALUES
  ('Sam', 'sam@example.com', 72),
  ('Ali', 'ali@example.com', 90),
  ('Mia', 'mia@example.com', 85);

The details in LocalWP’s Database tab are normally: host localhost, database local, user root, password root. Check your own tab, because it may differ. These values are for local practice only.

Why PDO?

PDO (PHP Data Objects) is the modern, recommended way to use a database from PHP. It works with several database types and supports prepared statements, the key defence against SQL injection.

Connecting

<?php
$host = "localhost";
$name = "local";
$user = "root";
$pass = "root";     // practice site only. Never reuse this on a live site

$dsn = "mysql:host=$host;dbname=$name;charset=utf8mb4";

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
    // Do not show details to visitors on a live site
    exit("Could not connect to the database.");
}

• charset=utf8mb4 supports all languages and emoji.
• ERRMODE_EXCEPTION makes problems throw errors you can catch, instead of failing silently.
• Keep connection details in one file, loaded with require_once.

SELECT: reading rows

<?php
require_once __DIR__ . "/db.php";

$stmt = $pdo->query("SELECT id, name, score FROM students ORDER BY score DESC");

foreach ($stmt as $row) {
    echo htmlspecialchars($row["name"], ENT_QUOTES, "UTF-8") . ": " . $row["score"] . "<br>";
}

query() is fine for a fixed SQL command with no outside data in it. Each row arrives as an associative array.

SQL injection: the danger

Never build SQL by joining visitor input into the text:

<?php
$name = $_GET["name"];
$sql = "SELECT * FROM students WHERE name = '$name'";   // injectable!

A visitor who sends a name such as ' OR '1'='1 changes the meaning of the command and can read, change, or delete data. This attack, SQL injection, is one of the most common and damaging vulnerabilities.

Prepared statements: the safe way

Send the SQL with placeholders, then send the values separately. The database treats the values only as data, never as commands.

<?php
require_once __DIR__ . "/db.php";

$min = filter_input(INPUT_GET, "min", FILTER_VALIDATE_INT);
if ($min === null || $min === false) {
    $min = 0;
}

$stmt = $pdo->prepare("SELECT name, score FROM students WHERE score >= :min ORDER BY name");
$stmt->execute(["min" => $min]);

foreach ($stmt->fetchAll() as $row) {
    echo htmlspecialchars($row["name"], ENT_QUOTES, "UTF-8") . " (" . $row["score"] . ")<br>";
}

• :min is a named placeholder. You can also use ?.
• fetchAll() returns all rows as an array. fetch() returns one row, or false.
• Placeholders work for values. They cannot stand for table or column names. If those must vary, choose them from an allowed list in PHP.

INSERT: adding a row

<?php
require_once __DIR__ . "/db.php";

if ($_SERVER["REQUEST_METHOD"] === "POST") {
    $name  = trim($_POST["name"] ?? "");
    $email = trim($_POST["email"] ?? "");

    if ($name === "" || filter_var($email, FILTER_VALIDATE_EMAIL) === false) {
        echo "Please enter a name and a valid email.";
    } else {
        $stmt = $pdo->prepare("INSERT INTO students (name, email) VALUES (:name, :email)");
        $stmt->execute(["name" => $name, "email" => $email]);

        echo "Added student number " . $pdo->lastInsertId();
    }
}

Validate first, then insert. lastInsertId() returns the new row’s automatic ID.

UPDATE and DELETE

<?php
require_once __DIR__ . "/db.php";

// Change one student's score
$stmt = $pdo->prepare("UPDATE students SET score = :score WHERE id = :id");
$stmt->execute(["score" => 95, "id" => 1]);
echo $stmt->rowCount() . " row updated.<br>";

// Delete one student
$stmt = $pdo->prepare("DELETE FROM students WHERE id = :id");
$stmt->execute(["id" => 3]);
echo $stmt->rowCount() . " row deleted.";

Transactions in brief

When several changes must succeed or fail together, such as moving money between accounts, wrap them in a transaction: $pdo->beginTransaction(), then $pdo->commit() if all went well, or $pdo->rollBack() in a catch block if not.

Passwords

Never store a plain password. Store a hash, and check it at login:

<?php
$hash = password_hash("secret123", PASSWORD_DEFAULT);   // store this in the database

var_dump(password_verify("secret123", $hash));
var_dump(password_verify("wrong", $hash));

bool(true)
bool(false)

The hash text differs each time. Use password_verify() to compare, never ==.

WordPress and databases

Inside WordPress, do not open your own PDO connection. Use the $wpdb object, which is already connected and has its own $wpdb->prepare() for safe queries.

Common mistakes

• Joining visitor input into SQL text instead of using placeholders.
• Forgetting WHERE in UPDATE or DELETE.
• Showing database error messages to visitors.
• Storing passwords in plain text.
• Leaving connection details in a public file.
• Printing database values without htmlspecialchars().
• Running practice code against a real or WordPress database.

<?php
require_once __DIR__ . "/db.php";

$row = $pdo->query("SELECT name, score FROM students ORDER BY score DESC LIMIT 1")->fetch();

if ($row === false) {
    echo "No students yet.";
} else {
    echo "Top student: " . htmlspecialchars($row["name"], ENT_QUOTES, "UTF-8") . " (" . $row["score"] . ")";
}

Summary

• Use PDO with exceptions, and prepared statements with placeholders for every outside value.
• SELECT reads, INSERT adds, UPDATE changes, and DELETE removes. Always use WHERE where needed.
• Hash passwords, hide error details, protect connection details, and practise on a separate database.