CRUD describes four common database tasks: create a row, read rows, update existing rows, and delete rows. The SQL is the same kind of work you learned in the main PDO CRUD lesson; MySQLi changes the PHP API used to send statements and read results. This is an optional MySQL-specific track. You will practice a small products table, bind submitted values, check outcomes, and protect updates with a WHERE condition. A reliable CRUD handler also validates input, checks authorization, and chooses a useful response. Database syntax alone does not provide that application behavior.
Prepare the table and connection contract
Assume products has id, name, price, and stock columns, with a primary key and appropriate constraints. Use a MySQLi connection configured for strict error reporting and utf8mb4. Keep schema setup outside public request handling and use a runtime account with only required row privileges. Product names are text, prices have an agreed decimal representation, and stock is a nonnegative integer. Validate each field before binding it. A form may send arrays where you expect strings, so check input shape before conversions. This lesson uses the object-oriented MySQLi API consistently. Existing procedural code can be valid too, but mixing styles without a reason makes a beginner's code harder to follow and review.
Insert rows with explicit columns
Write INSERT INTO products (name, price, stock) VALUES (?, ?, ?) so the meaning remains clear if the table later gains another column. Bind variables using types appropriate to their validated representation, then execute. A decimal price can be sent as a validated string to preserve the intended decimal text, while stock uses an integer binding. Retrieve insert_id on the same connection immediately after the insert when you need the generated key. Do not assume generated IDs form a gapless sequence; rollbacks, failed attempts, and concurrent sessions can create gaps. An insert may fail because of a unique constraint even after application validation. Treat expected conflicts as a business result and unexpected infrastructure failures as private operational errors.
Select only what the interface needs
A read can use a fixed query when no external values influence it. For a requested product ID, use prepare(), bind_param(), and execute() with the validated ID. bind_result() and fetch() support prepared reads without requiring get_result()'s mysqlnd dependency. Select named columns rather than SELECT * when the result contract is stable. A missing row should have an explicit not-found outcome, not become a warning when code reads null properties. For lists, use a deterministic order and a bounded limit or pagination. Escape names when rendering HTML; safe SQL binding does not make database text safe markup. Return only fields the current visitor is permitted to see.
Update with a narrow WHERE condition
An UPDATE without WHERE changes every row. Use a validated primary key and any ownership conditions needed by the feature. For example, a seller's product edit may require both product ID and seller ID in the predicate. Check affected_rows, but understand its semantics: assigning a field its existing value can produce zero changed rows even when the row matched. If your API needs to distinguish not-found from unchanged, use a deliberate strategy such as reading under the required consistency policy or configured matched-row behavior. Avoid a separate ownership check that can be invalidated by later changes unless your transaction design accounts for that race. Database constraints should remain the final protection for valid stock and prices.
Delete deliberately and authorize the action
Use a prepared DELETE with a specific ID and ownership or tenant condition. A browser GET should not delete records because links, crawlers, and prefetching can trigger reads automatically. Session-authenticated state changes need CSRF protection. Choose between hard deletion and an explicit archival policy based on relationships, audits, and business needs. Foreign keys may reject deletion or cascade it according to the schema; understand that before presenting a delete button. A zero affected-row count can mean the row was already absent or not accessible under the predicate. A user-facing response can preserve privacy while the application logs the precise operation. Never construct the DELETE table or condition from arbitrary submitted text.
Keep multi-step operations consistent
If inserting an order and reducing stock must succeed together, use a supported transaction. An atomic conditional stock update can prevent stock from going negative under concurrency. Do not send external email or charge a payment inside a retryable database transaction without an idempotency and coordination policy. For ordinary single-row edits, keep the handler small: validate, authorize through a suitable predicate, execute, and respond. Close statements when their lifecycle is complete, especially in loops. Avoid one database connection per row. Test missing rows, unchanged updates, duplicate inserts, forbidden ownership, and constraint failures. These cases reveal whether your handler's behavior is clear beyond the happy-path example.
Practice task and worked solution
Implement an edit endpoint for a seller's product stock. The solution validates a nonnegative integer and product ID, then prepares UPDATE products SET stock = ? WHERE id = ? AND seller_id = ?. Bind the authenticated seller ID from trusted session state, not from a submitted hidden field. Add method and CSRF checks under the application framework. Decide how unchanged updates and unavailable rows are reported. Test seller A trying to edit seller B's product, stock zero, invalid numeric text, and an unchanged value. Keep SQL exceptions private and retain the database nonnegative constraint. Explain how a delete handler would use the same ownership predicate.
Continue learning
Compare the main CRUD lesson, PDO, and form validation.
The MySQLi quick start describes prepared reads and row operations.
Insert, Select, Update and Delete with MySQLi Examples
Each example shows the code, the output it produces and what each part does.
Insert and then read a product
Assume a configured MySQLi connection in $db and the stated products schema in a disposable development database. This optional example uses bind_result().
The expected row values are Notebook and stock 8. The generated ID depends on existing database state. Execute only in the intended development schema; the example changes data.
<?php
$name = 'Notebook';
$price = '4.50';
$stock = 8;
$insert = $db->prepare('INSERT INTO products (name, price, stock) VALUES (?, ?, ?)');
$insert->bind_param('ssi', $name, $price, $stock);
$insert->execute();
$id = $db->insert_id;
$insert->close();
$read = $db->prepare('SELECT name, stock FROM products WHERE id = ?');
$read->bind_param('i', $id);
$read->execute();
$read->bind_result($savedName, $savedStock);
if (!$read->fetch()) throw new RuntimeException('Inserted product missing');
echo $savedName . ': ' . $savedStock . PHP_EOL;
$read->close();Notebook: 8Insert, Select, Update and Delete with MySQLi FAQ
Can an UPDATE omit WHERE?
Only for a deliberately authorized whole-table operation, never accidentally. Without WHERE, every row changes. Write the WHERE clause first, test it as a SELECT, and check affected_rows to confirm the number of rows you expected.
Why is affected_rows zero?
The row may be absent, or the new value may equal the old value: by default MySQL counts only rows that actually changed. Distinguish the two cases with a SELECT, or enable the found-rows client flag when you need matched rows rather than changed rows.
Does prepared SQL stop XSS?
No. Prepared statements protect the database query, not the HTML page. A comment containing <script> is stored exactly as typed, so escape HTML output separately with htmlspecialchars() whenever you display stored values.
Must I switch from PDO?
No. This is an optional API track for projects and hosting environments that already use mysqli. If your code uses PDO, keep using it; the concepts of prepared statements, error handling and transactions are the same in both.
Insert, Select, Update and Delete with MySQLi Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
A prepared UPDATE changes a name to its current value and reports zero. What follows?
Insert, Select, Update and Delete with MySQLi Quiz
2 questions to check what you learned.
What prevents an ordinary update from changing every product?
- A deliberate WHERE predicate including the target ID
- The PHP file extension
- A JSON response alone
- Calling echo first
What value should supply seller ownership in an update?
- Any submitted seller ID
- The authenticated seller identity
- The seller ID from an unverified query parameter
- The product ID treated as the seller ID