Intermediate 5 min · March 06, 2026

CRUD PHP MySQL — WHERE Clause Disasters & Safe Deletes

A commented-out WHERE clause deleted an entire customer table.

Naren · Founder

Plain-English first. Then code. Then the interview question.

⚡Quick Answer

CRUD stands for Create, Read, Update, Delete — the four operations that power nearly every web app
PDO with prepared statements is the only safe way to interact with MySQL from PHP
Every input must be bound via placeholders, never concatenated into SQL strings
Always configure charset=utf8mb4, ERRMODE_EXCEPTION, and EMULATE_PREPARES=false at connection time
Use transactions when multiple queries must all succeed or all fail together
The most dangerous mistake: omitting WHERE on UPDATE/DELETE — it wipes all rows instantly

Almost every application you have ever used runs on CRUD. When you post a photo, that is a Create. When your feed loads, that is a Read. When you edit your bio, that is an Update. When you delete a tweet you regret, that is a Delete. PHP has been powering this cycle on the web since the late 1990s, and MySQL remains one of the most widely deployed relational databases on the planet. Understanding how they work together is not just academic — it is the backbone of nearly every commercial PHP project in existence.

The problem most tutorials create is teaching you the mechanics without the safety net. They show you how to fire a query but not how to stop an attacker from rewriting it. They show you how to fetch a row but not how to handle the moment the row does not exist. Real production code needs both: the functionality and the guardrails, written together from the start.

By the end of this article you will have a fully working CRUD system built on PDO with prepared statements, proper error handling, and the mental model to extend it to any project. You will know not just what each operation does but why PDO exists, why prepared statements are non-negotiable, and exactly where junior developers leave dangerous holes.

Setting Up Your Database and PDO Connection the Right Way

Before writing a single query you need two things: a database table with real structure, and a connection object you can trust. Most tutorials bury the connection in a one-liner and move on. That is a mistake, because the connection is where security and reliability are either baked in or left out.

PDO — PHP Data Objects — is the modern way to talk to MySQL from PHP. Unlike the old mysql_* functions (removed in PHP 7) or even MySQLi, PDO gives you a single consistent API that works across multiple database engines. More importantly, it makes prepared statements the natural default, which is your primary defence against SQL injection.

The key settings to configure at connection time are the charset (always utf8mb4 — the older utf8 cannot store emoji or certain Unicode characters), the error mode (set it to exceptions so failures are loud and catchable, not silent), and the emulated prepares flag (turn it off so the database driver handles the actual parameterisation, not PHP). Get these three right once in your connection file and every query you write inherits those protections automatically.

Create and Read — Inserting Records and Fetching Them Safely

Create and Read are the two operations you will write most often, so they deserve the most attention. The golden rule for Create is: never concatenate user input directly into a query string. Ever. Instead, use a prepared statement — you write the query with named placeholders like :title, then bind the actual values separately. The database driver handles escaping, not you.

For Read, the decisions are about scope: are you fetching one specific row by ID, or a filtered list? fetch() returns a single row (or false if nothing matched), while fetchAll() returns every matching row as an array of arrays. Both are useful; choosing the wrong one is a common source of bugs.

A subtlety beginners miss: execute() on a prepared statement does not return the inserted row — it returns a boolean. To get the ID of the row you just created, call $pdo->lastInsertId() immediately after. This ID is what you use to redirect the user to the new record's page, which is the standard Post-Redirect-Get pattern that prevents duplicate submissions on browser refresh.

Update and Delete — Modifying Records Without Destroying Your Data

Update and Delete are where the stakes get higher. A missing WHERE clause on either one is catastrophic — Update rewrites every row in the table, Delete wipes them all. This is not a hypothetical: it is one of the most common causes of data loss incidents in production systems.

The discipline to adopt is: always require a specific ID (or equivalent unique identifier) before executing an Update or Delete. Never build a generic 'update whatever matches' function that accepts arbitrary WHERE conditions from user input.

For Update, return the number of affected rows from rowCount() after executing. Zero affected rows does not always mean an error — it might mean the user submitted the form with identical values. Your application should distinguish between 'record not found' (the ID does not exist) and 'nothing changed' (the ID exists but the data was the same). The cleanest way to check existence is a separate getArticleById() call before the update.

For Delete, the same rowCount() check applies. Always give the calling code a boolean signal it can act on — redirect on success, show an error on failure.

Transactions and Error Handling — Keeping Your Data Consistent

CRUD operations rarely happen in isolation. In a real application, creating an order might require inserting into three tables: orders, order_items, and payments. If any one of those inserts fails, the others must never commit. That's what database transactions are for.

PDO supports transactions with beginTransaction(), commit(), and rollBack(). They are not optional — they are the difference between atomic operations and corrupt data. Without a transaction, a failed second query leaves half-written data that can trigger cascading failures in downstream systems.

Error handling is equally critical. Setting PDO::ERRMODE_EXCEPTION means any failed query throws a PDOException immediately. You catch it, log the details (without exposing them to the user), and decide whether to retry, rollback, or return an error. Never let raw database errors reach the browser — they leak column names, table structures, and sometimes credentials.

Security Best Practices — Defend Against SQL Injection, XSS, and Data Exposure

CRUD operations are the primary attack surface of any PHP application. SQL injection is the most dangerous — an attacker who can inject SQL can read, modify, or delete any data in your database. Prepared statements with PDO are your first and most effective defence, but they are not enough on their own.

You also need to protect against Cross-Site Scripting (XSS) when displaying data that users submitted. Always escape output with htmlspecialchars($value, ENT_QUOTES, 'UTF-8') before rendering in HTML. Never trust data from the database — even if it was inserted via prepared statements, it could contain JavaScript that executes in other users' browsers.

Beyond injection, you must control access at the application level: don't let User A delete User B's articles. Implement authorization checks for every Update and Delete operation. Use session-based authentication and verify ownership or role before executing any destructive action.

CRUD Operations with PHP and MySQL: The Complete Picture

Aspect	MySQLi (Procedural)	PDO
Database support	MySQL only	12+ databases (MySQL, PostgreSQL, SQLite, etc.)
Prepared statements	Supported — verbose syntax	Supported — cleaner named placeholder syntax
Default fetch mode	Requires explicit flag each time	Configurable once at connection, inherited by all queries
Named placeholders	Not supported — positional ? only	Supported — :name syntax improves readability
Error handling	Must check return values manually	Throws PDOException — works with try/catch naturally
Switching databases later	Requires full rewrite	Change DSN string — queries stay the same
Recommended for new projects	No — legacy preference only	Yes — PDO is the modern standard

Key Takeaways

PDO with ATTR_EMULATE_PREPARES set to false is the correct, modern way to connect PHP to MySQL — the three options at connection time (errmode, fetch mode, emulated prepares) define the safety and behaviour of every query you write.
Prepared statements are not optional performance overhead — they are a structural separation of query logic from data, which is the only reliable defence against SQL injection. bindValue() with an explicit PDO::PARAM_* type is clearer and more predictable than passing an array to execute().
Every UPDATE and DELETE must have a WHERE clause targeting a specific, unique identifier. Zero rows affected from rowCount() is ambiguous — it means either the row does not exist or the data was identical — and good application logic handles both cases explicitly.
Transactions are the mechanism for keeping two or more related database operations atomic. If any step fails, rollBack() undoes all of them — without transactions, a half-completed multi-query operation can leave your data in an inconsistent state permanently.
Security is layered: prepared statements for SQL injection, htmlspecialchars() for XSS, and ownership checks for authorization. Skipping any one layer leaves your CRUD application vulnerable to a class of attacks.

Common Mistakes to Avoid

Using string concatenation instead of prepared statements
Symptom: Code works fine until a user enters `' OR '1'='1` in a field and suddenly reads data they should not, or worse, drops a table.
Fix: Always use prepare() with named placeholders and bindValue(). Never concatenate user input into a SQL string. There is no legitimate reason to do so.
Forgetting to check `fetch()` return value before accessing array keys
Symptom: PHP throws `Warning: Undefined index: title` or a fatal error when trying to access `$article['title']` on a `false` value.
Fix: Always guard with if ($article === false) before using the result. fetchAll() is safer for lists since it returns an empty array, never false.
Omitting the WHERE clause on UPDATE or DELETE
Symptom: Every row in the table is modified or deleted — silent data destruction with no PHP error or warning.
Fix: Write the WHERE clause first, then fill in the rest of the query. Use MySQL's safe-updates mode in development, and always run untested destructive queries against a database dump, not live data.
Using `bindParam()` when `bindValue()` is appropriate
Symptom: Subtle bugs where the bound value doesn't evaluate until `execute()`, often causing loops to bind the reference to the last iteration's value.
Fix: Use bindValue() for most cases. Reserve bindParam() only when you need to bind a variable reference inside a loop and want the current value at execution time.

Interview Questions on This Topic

QWhat is SQL injection and how do prepared statements in PDO prevent it? Can you walk me through exactly what happens at the database driver level when you call prepare() followed by execute()?SeniorReveal
SQL injection occurs when user input is concatenated into a SQL query string, allowing an attacker to alter the query structure. With prepared statements, prepare() sends the query template to the database server, which parses and compiles it with placeholders. execute() then sends the actual parameter values separately, as data, not SQL. The database engine treats the placeholders as data containers, never as executable code — even if a parameter contains malicious SQL, it cannot change the query structure because the query plan is already compiled. PDO, when emulated prepares are disabled, forces this real preparation. When enabled, PDO emulates the behaviour but still escapes values, though some edge cases may be missed.
QWhat is the difference between PDO::FETCH_ASSOC and PDO::FETCH_OBJ, and when would you choose one over the other in a real application?Mid-levelReveal
FETCH_ASSOC returns each row as an associative array keyed by column name (e.g., $row['title']). FETCH_OBJ returns each row as a stdClass object with properties (e.g., $row->title). Use FETCH_ASSOC when you need to manipulate the data with array functions like array_map() or when working with JSON serialization that expects arrays. Use FETCH_OBJ when you prefer object syntax and have no need for array functions. Performance differences are negligible. In a production CRUD API, FETCH_ASSOC is more common because prepared responses often need array access.
QIf rowCount() returns 0 after an UPDATE query, does that definitely mean the record does not exist? How would you distinguish between 'row not found' and 'row found but nothing changed', and why does that distinction matter for an API response?SeniorReveal
No, rowCount() returning 0 after an UPDATE can mean either: (1) the WHERE clause matched no rows (record does not exist) or (2) the WHERE clause matched a row but the SET values were identical to the existing values, and the database optimized away the update (no changes made). To distinguish, first run a SELECT with the same WHERE clause. If the SELECT returns a row, then the record exists but the data was unchanged. If the SELECT returns nothing, the record does not exist. This distinction matters because for an API, returning a 404 (not found) vs a 200 with a message 'no changes needed' provides correct semantics to the client. A frontend that expects a success might otherwise display an error incorrectly.

Frequently Asked Questions

Should I use MySQLi or PDO for PHP and MySQL CRUD operations?

Use PDO for all new projects. It supports named placeholders which are more readable, lets you configure error handling and fetch mode once at connection time, and is database-agnostic — so if you ever switch from MySQL to PostgreSQL, your query code stays the same. MySQLi is only worth using if you are maintaining existing code that already uses it.

What is the difference between fetch() and fetchAll() in PDO?

fetch() returns a single row as an array (or false if no rows matched) and moves the internal cursor forward — ideal when you expect exactly one result, like a lookup by ID. fetchAll() returns all matching rows as an array of arrays — ideal for lists. fetchAll() on a large result set loads everything into memory at once, so for very large datasets consider looping with fetch() instead.

How do I handle errors in PDO without showing raw database messages to users?

Set PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION at connection time so PDO throws a PDOException on any failure. Wrap your database calls in try/catch blocks — log the full exception details server-side (to a file or monitoring service) and show the user a generic, friendly error message. Never echo $e->getMessage() directly to the browser in production, as it can expose table names, column names and query structure to attackers.

What is the difference between bindValue() and bindParam() in PDO?

bindValue() binds the actual value of a variable at the time bindValue() is called. If the variable changes later, the new value is not used. bindParam() binds a reference to the variable, meaning the value is evaluated at execute() time. This is useful when looping and you want each iteration to use the current variable value. For most CRUD operations, bindValue() is safer and more predictable.

Should I use PDO::ERRMODE_EXCEPTION or PDO::ERRMODE_WARNING?

Use ERRMODE_EXCEPTION in both development and production. ERRMODE_WARNING will issue a PHP warning but continue execution, which can leave your application in an inconsistent state or silently return corrupted data. Exceptions force you to handle errors explicitly with try/catch, making your code more reliable.

🔥

That's PHP & MySQL. Mark it forged?

5 min read · try the examples if you haven't