Working with MySQL

How PHP Talks to MySQL

MySQL is a separate program from PHP. It runs as its own service, listens on a port, holds your tables, and answers questions written in SQL. PHP's job is to open a connection, send SQL, and turn the answers into PHP arrays. MariaDB is a compatible fork of MySQL and everything in this lesson applies equally to it.

PHP 8 offers exactly two ways to do this: the mysqli extension, which works only with MySQL, and PDO, which works with several databases through one interface. This lesson covers mysqli; the next covers PDO. Both are entirely capable, and both support prepared statements, which is the part that actually matters.

You will also find a great deal of code online using functions like mysql_connect() and mysql_query(), without the i. Those were removed from PHP in version 7.0 and do not exist any more. If a tutorial uses them, it is at least a decade out of date and every security practice in it should be assumed wrong too. This is worth knowing because such tutorials are still very highly ranked in search results.

One connection detail to get right immediately: set the character set to utf8mb4. MySQL's confusingly named utf8 is a limited three-byte version that cannot store emoji or some characters, and mismatched charsets between PHP and MySQL are the reason accented and Indian-language text turns into question marks. Set it once on the connection and the whole problem disappears.

<?php
// db.php - one file, included everywhere. Keep it OUTSIDE the public folder.
declare(strict_types=1);

$config = require __DIR__ . '/../config/database.php';

$conn = new mysqli(
    $config['host'],
    $config['user'],
    $config['pass'],
    $config['name']
);

$conn->set_charset('utf8mb4');   // do this on every connection

// From PHP 8.1 mysqli throws exceptions on error by default,
// so a failed connection or query raises mysqli_sql_exception.
try {
    $result = $conn->query('SELECT COUNT(*) AS total FROM students');
    $row    = $result->fetch_assoc();
    echo 'Students: ' . $row['total'];
} catch (mysqli_sql_exception $e) {
    error_log('Database error: ' . $e->getMessage());   // for you
    exit('Sorry, something went wrong.');               // for the visitor
}

SQL Injection: the Most Important Thing in This Course

If you remember one section from these twenty lessons, make it this one. SQL injection is the flaw that has leaked more user data than any other, it is trivially easy to introduce, and it is completely preventable with a technique that is no harder than the dangerous version.

The flaw comes from building a query by gluing strings together. When you write "SELECT * FROM users WHERE email = '$email'", you are not passing $email to the database as a value. You are building a piece of SQL text, and whatever the visitor typed becomes part of the query's structure. The database has no way to tell your intent from theirs — it just receives one string of SQL and runs it.

So consider what happens when someone types ' OR '1'='1 into the email box of a login form. The query the database receives becomes SELECT * FROM users WHERE email = '' OR '1'='1'. That condition is true for every row, so the query returns the first user in the table — often the administrator — and a naive login script logs the attacker in as them. No password was needed.

It gets worse than reading. Because the visitor controls the structure of the statement, they can attach conditions that reveal other tables, or in some configurations terminate your statement and add another. This is how real breaches of student databases and small e-commerce sites happen, and automated tools scan for it constantly.

One tempting fix is wrong enough to name. Escaping the input with real_escape_string() — or worse, filtering out words like OR and SELECT — treats the symptom. It depends on you remembering it in every single place, on the connection charset being correct, and on the value being quoted properly in the SQL. Miss one of the three and the hole is open again. There is a fix that does not depend on your memory, and that is the next section.

<?php
// DANGEROUS - never write a query like this
$email = $_POST['email'] ?? '';
$sql   = "SELECT * FROM users WHERE email = '$email'";

// What the visitor types:   ' OR '1'='1
// What the database runs:
//   SELECT * FROM users WHERE email = '' OR '1'='1'
// Result: every row matches, and a naive login lets them in.

// Another classic, with a numeric field:
$id  = $_GET['id'] ?? '';
$sql = "SELECT * FROM invoices WHERE id = $id";
// ?id=5 OR 1=1     ->  every invoice in the table, belonging to everyone

// The same problem inside a search box:
$q   = $_GET['q'] ?? '';
$sql = "SELECT * FROM products WHERE name LIKE '%$q%'";

// Escaping is NOT the answer. It looks safe and is easy to get wrong:
$sql = "SELECT * FROM users WHERE email = '" . $conn->real_escape_string($email) . "'";
// Forget the quotes around it, or use it on a numeric column, and
// the protection silently vanishes. One forgotten call is one hole.

Prepared Statements: the Fix That Cannot Be Forgotten

A prepared statement separates the query from the data, permanently. You send the SQL first with ? placeholders where values will go, and the database parses and plans it while there is no user data anywhere near it. Then you send the values separately. Because the statement's structure was already decided, a value can never become part of it.

This is a structural guarantee, not a filter. It does not matter what the visitor typed — quotes, semicolons, entire SQL statements — the database has already committed to the shape of the query and treats what arrives as nothing but a value. Someone whose surname is genuinely O'Brien also works correctly, with no escaping, which is a nice sign that you are doing it right.

In mysqli the sequence is prepare(), then bind the values, then execute(), then fetch. The classic way to bind is bind_param(), whose first argument is a string of type characters — i for integer, d for a decimal number, s for string, b for binary — with one character per placeholder. From PHP 8.1 you can skip bind_param() entirely and pass an array straight to execute(), which is shorter and harder to get wrong.

One thing to notice about bind_param(): it binds by reference, so you must pass variables, not literal values. That is why the older style declares the variables first. It also means that if you change a bound variable and call execute() again, the new value is used — useful for inserting many rows with one prepared statement.

Make this a rule with no exceptions: every value that comes from outside your program goes into a query as a bound parameter. Not sometimes, not for the fields that look risky. Every one. A rule with exceptions is a rule you will misapply under deadline pressure, which is precisely when it matters.

<?php
// SELECT with a parameter
$stmt = $conn->prepare('SELECT id, name, email FROM users WHERE email = ?');
$stmt->bind_param('s', $email);       // 's' = string
$stmt->execute();

$result = $stmt->get_result();
$user   = $result->fetch_assoc();      // one row, or null

if ($user === null) {
    echo 'No account with that email.';
}

// From PHP 8.1: pass the values straight to execute()
$stmt = $conn->prepare('SELECT id, name FROM students WHERE marks >= ? AND branch = ?');
$stmt->execute([40, 'CSE']);
$rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

foreach ($rows as $row) {
    echo htmlspecialchars($row['name'], ENT_QUOTES, 'UTF-8') . '<br>';
}

// A LIKE search - the wildcards go in the VALUE, not the SQL
$term = '%' . $_GET['q'] . '%';
$stmt = $conn->prepare('SELECT name FROM products WHERE name LIKE ?');
$stmt->bind_param('s', $term);
$stmt->execute();

// Reusing one statement for many rows
$stmt = $conn->prepare('INSERT INTO attendance (student_id, day, present) VALUES (?, ?, ?)');
$stmt->bind_param('isi', $studentId, $day, $present);
foreach ($records as $r) {
    [$studentId, $day, $present] = [$r['id'], $r['day'], $r['present']];
    $stmt->execute();
}

// It just works, whatever the input contains
$email = "o'brien@example.com";        // no escaping needed
$stmt = $conn->prepare('SELECT id FROM users WHERE email = ?');
$stmt->execute([$email]);

Insert, Update, Delete — and Knowing What Happened

Writing to the database uses the same prepared-statement mechanism as reading. What differs is what you check afterwards.

After an INSERT into a table with an auto-incrementing id, $conn->insert_id gives you the id that was just created. You will need it constantly — to redirect to the new record's page, or to insert related rows such as the items belonging to a new order.

After an UPDATE or DELETE, $conn->affected_rows tells you how many rows actually changed. This is more useful than it first appears: if you update a record by id and the answer is 0, either that id does not exist or the values submitted were identical to what was already stored. Distinguishing "not found" from "nothing to change" matters for the message you show.

Two habits will save you real damage. First, always include a WHERE clause on an UPDATE or DELETE — forgetting it updates or deletes every row in the table, instantly and irreversibly. Test destructive queries as a SELECT with the same WHERE clause first, so you can see exactly what would be affected.

Second, in an application with logins, scope every write to the current user: WHERE id = ? AND user_id = ?. Without that second condition, anyone who edits the id in a form or URL can modify records that are not theirs. This is a distinct bug from SQL injection — the query is perfectly parameterised — and it is at least as common.

<?php
// INSERT, then use the new id
$stmt = $conn->prepare(
    'INSERT INTO enquiries (name, email, message, created_at) VALUES (?, ?, ?, NOW())'
);
$stmt->execute([$name, $email, $message]);

$newId = $conn->insert_id;
header('Location: /enquiry.php?id=' . $newId);
exit;

// UPDATE, scoped to the owner
$stmt = $conn->prepare(
    'UPDATE enquiries SET message = ? WHERE id = ? AND user_id = ?'
);
$stmt->execute([$message, $id, $_SESSION['user_id']]);

if ($conn->affected_rows === 0) {
    echo 'Nothing was changed - either the record is not yours, '
       . 'or the text was already the same.';
}

// DELETE, also scoped
$stmt = $conn->prepare('DELETE FROM enquiries WHERE id = ? AND user_id = ?');
$stmt->execute([$id, $_SESSION['user_id']]);

// Counting rows
$stmt = $conn->prepare('SELECT COUNT(*) AS total FROM enquiries WHERE user_id = ?');
$stmt->execute([$_SESSION['user_id']]);
$total = (int) $stmt->get_result()->fetch_assoc()['total'];

// DANGEROUS - no WHERE clause
// $conn->query('DELETE FROM enquiries');     // the whole table, gone

What Placeholders Cannot Do

Prepared statements protect values. They cannot protect the parts of a query that determine its structure, because those have to be fixed before the database can plan the statement at all.

So you cannot use a placeholder for a table name, a column name, the direction in an ORDER BY, or a keyword such as ASC and DESC. If you try, the database either errors or treats it as a literal string, which produces a query that runs but sorts by the constant text you passed rather than by a column.

This matters because sortable tables are a normal feature. A listing page with clickable column headings sends something like ?sort=marks&dir=desc, and those values have to end up in the SQL somehow. Pasting them in directly is a straightforward injection hole.

The answer is an allow-list. Keep an array of the column names you are willing to sort by, check the incoming value against it, and use a safe default when it does not match. What ends up in the SQL is then a value you wrote, chosen by the user only in the sense of picking from your list. The same technique covers direction, page size, and anything else that changes the query's shape.

Note that this is a different mechanism from a prepared statement and it does not replace one. The values still get bound as parameters; the allow-list only controls the identifiers. Both are needed on the same query.

<?php
// This does NOT work - identifiers cannot be placeholders
// $stmt = $conn->prepare('SELECT * FROM students ORDER BY ? ?');

// This is an injection hole
// $sql = "SELECT * FROM students ORDER BY {$_GET['sort']} {$_GET['dir']}";

// Correct: an allow-list decides, the user only chooses from it
$sortable = ['name' => 'name', 'marks' => 'marks', 'roll' => 'roll_no'];

$sortKey = $_GET['sort'] ?? 'name';
$column  = $sortable[$sortKey] ?? 'name';          // falls back safely

$dir = strtolower($_GET['dir'] ?? 'asc') === 'desc' ? 'DESC' : 'ASC';

$minMarks = (int) ($_GET['min'] ?? 0);
$perPage  = 20;
$page     = max(1, (int) ($_GET['page'] ?? 1));
$offset   = ($page - 1) * $perPage;

// Identifiers come from OUR arrays; values are still bound
$sql = "SELECT roll_no, name, marks
        FROM students
        WHERE marks >= ?
        ORDER BY $column $dir
        LIMIT ? OFFSET ?";

$stmt = $conn->prepare($sql);
$stmt->execute([$minMarks, $perPage, $offset]);
$rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

mysqli or PDO?

Both extensions are maintained, both are safe when used with prepared statements, and both will serve a college project perfectly well. The choice comes down to a few practical differences rather than to one being better written than the other.

mysqli works only with MySQL and MariaDB. If you are certain that is the only database you will ever use, that is not a limitation, and its procedural function style is a gentler step from older tutorial code.

PDO works with MySQL, PostgreSQL, SQLite and others through one consistent interface. It supports named placeholders such as :email, which are far easier to read and reorder than a row of question marks in a long insert. Its fetch modes are more flexible, its error handling through exceptions is uniform, and it is what almost every modern framework and library expects.

For that reason PDO is the more useful thing to learn well, and the next lesson covers it in depth. If you already have working mysqli code, there is no urgency to rewrite it — as long as it uses prepared statements everywhere. The one thing that genuinely matters is the same in both.

• mysqli — MySQL and MariaDB only; PDO — many databases, one interface
• mysqli — ? placeholders only; PDO — ? and named :email placeholders
• mysqli — offers both procedural functions and an object style; PDO — object style only
• mysqli — throws exceptions by default from PHP 8.1; PDO — set ERRMODE_EXCEPTION, which is the default from PHP 8.0
• PDO — richer fetch modes, including fetching rows directly into objects of your own classes
• PDO — what Laravel, Symfony and most libraries expect, so it is the more transferable skill
• Both — completely safe with prepared statements, and completely unsafe without them