PHP and Databases with PDO
PHP and Databases (PDO) is an intermediate-level course of 5 lessons (about 3 h), with 15 exercises and 15 quiz questions. Prerequisites: Object-Oriented PHP and Composer, Intermediate SQL (joins, subqueries). The course needs a full project environment, planned for a later phase of the platform.
- PHP
- Intermediate
- 5 lessons
- 3 h
- 15 exercises
- Full project environment (phase 2)
The course in brief
This course teaches you to make a PHP application talk to a relational database using PDO. You will write queries that are safe against SQL injection, handle writes and transactions, then organize your code with entities and testable repositories. All exercises run on in-memory SQLite and transpose directly to MySQL or PostgreSQL.
What you will be able to do
- Configure a safe PDO connection for SQLite, MySQL or PostgreSQL
- Read data with the appropriate fetch mode
- Write prepared statements that prevent SQL injection
- Insert, update and delete data and use transactions
- Structure data access with entities and a paginated repository
Syllabus: 5 lessons
- Understand the role of PDO and database drivers
- Write a DSN for SQLite, MySQL and PostgreSQL
- Configure PDO to throw exceptions and return associative arrays
- 02
Reading Data: query, fetch and fetchAll
- Run a SELECT query and iterate over its result
- Choose the right fetch mode (ASSOC, COLUMN, KEY_PAIR, OBJ)
- 03
Prepared Statements and SQL Injection
- Understand the mechanism of an SQL injection
- Use prepare() and execute() with named or positional parameters
- Handle special cases: LIKE, dynamic IN, column names
- 04
Insert, Update, Delete and Transactions
- Write INSERT, UPDATE and DELETE statements with prepared statements
- Retrieve the inserted id and the number of modified rows
- Group several writes in a transaction
- 05
Organizing Data Access: Entities and Repository
- Isolate SQL in a Repository class
- Turn rows into typed objects (hydration)
- Paginate results cleanly
Final project
Order Manager in PHP and PDO
Build the data access layer of a small shop: products, customers and orders tables, one repository per entity and a transactional order-placement service. A CLI script lets you add a product, list the paginated catalog and place an order. The code works with SQLite in development and accepts a MySQL DSN through an environment variable.
Deliverables
- SQL script creating the schema (primary keys, foreign keys, UNIQUE and NOT NULL constraints)
- Entity classes and repositories (find, save, page) receiving PDO through the constructor
- A passerCommande() service using a transaction and checking the stock
- A demonstration CLI script and a README explaining the configuration via environment variables
- A verification script that runs scenarios on in-memory SQLite
Assessment criteria
- No external data concatenated into an SQL query
- Correct transactions: rollback on failure and exception propagated
- Typed hydration and null returned when an item is not found
- Bounded and sorted pagination
- Connection credentials read from the environment, readable and commented code
Frequently asked questions
Should I use PDO or mysqli in PHP?
PDO is generally recommended: it works with many database systems (MySQL, PostgreSQL, SQLite...) with the same API and offers named parameters. mysqli remains valid but only works with MySQL/MariaDB.
Are prepared statements enough against SQL injection?
They protect all values. Elements that cannot be bound, such as a sort column name, must additionally be validated with an allowlist.
Do I need a MySQL server to follow the course?
No. The exercises use in-memory SQLite, built into PHP. The code transposes to MySQL or PostgreSQL by simply changing the DSN.
Does this course prepare me for Laravel?
Yes. It explains what an ORM like Eloquent does under the hood (prepared statements, hydration, pagination) and comes before the Laravel Fundamentals course in the path.
This course, told for your job
Say what you want to be able to do: “PHP and Databases (PDO)” takes its place in your path, with examples from your field. The concepts stay those of the course.