Spaced-repetition deck on PDO, transactions, isolation levels, indexing, query optimisation, and REST API design (status codes, auth, CORS, rate limiting, versioning). Uses the Obsidian spaced-repetition Question::Answer format.
What are the two essential PDO connection attributes for secure, correct behaviour?::PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION and PDO::ATTR_EMULATE_PREPARES => false.
What is the difference between bindValue() and bindParam()?::bindValue() binds the value at call time; bindParam() binds by reference, so the bound variable's value is read when execute() runs.
Why pass an array to execute([...]) instead of binding each parameter?::It is concise and binds all placeholders as strings safely; use explicit bindValue() with a type when you need a non-string type (e.g. PDO::PARAM_INT for LIMIT).
How do you fetch results as associative arrays?::Set PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC or call fetch(PDO::FETCH_ASSOC).
What three methods drive a PDO transaction?::beginTransaction(), commit(), and rollBack() (roll back inside a catch).
What do the ACID properties stand for?::Atomicity, Consistency, Isolation, Durability.
What is a "dirty read" and which isolation level prevents it?::Reading another transaction's uncommitted changes; prevented at READ COMMITTED and above.
What are the four SQL isolation levels from weakest to strongest?::READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.
What is a deadlock and the standard application response?::Two transactions each hold a lock the other needs; the DB aborts one victim — the app should catch the deadlock error and retry the transaction with backoff.
What does an index speed up, and what does it cost?::It speeds up lookups/joins/sorts on the indexed columns; it costs extra storage and slows writes (index maintenance on INSERT/UPDATE/DELETE).
What does EXPLAIN show you?::The query execution plan — which indexes are used, join order, and estimated rows scanned — used to diagnose slow queries.
Why is keyset (cursor) pagination preferred over large OFFSET?::OFFSET n still scans and discards n rows (slower as you page deeper); keyset pagination uses WHERE id > :last_seen ORDER BY id LIMIT k, which stays fast via the index.
What is a covering index?::An index that contains every column a query needs, so the DB answers from the index alone without reading the table rows.
Why use database migrations instead of ad-hoc SQL?::They version the schema in code, apply changes reproducibly across environments, and are reversible — keeping every deployment's schema consistent.
What does a FOREIGN KEY constraint enforce?::Referential integrity — a child row's key must reference an existing parent row, preventing orphaned records.
What HTTP methods map to CRUD?::POST=Create, GET=Read, PUT/PATCH=Update, DELETE=Delete.
What status code for a successful resource creation?::201 Created (typically with a Location header pointing to the new resource).
What is the difference between 401 and 403?::401 Unauthorized means "not authenticated" (no/invalid credentials); 403 Forbidden means "authenticated but not permitted".
What status code should a validation failure return?::422 Unprocessable Entity (or 400 Bad Request) with a body describing the field errors.
Why must an API be stateless, and how does auth work then?::Each request carries all needed context (no server session) so it scales horizontally; auth travels per-request via a bearer token / API key in the Authorization header.
What does CORS control and which header grants access?::Which cross-origin browsers may read the response; the server sets Access-Control-Allow-Origin (echo a validated allow-listed origin, never blindly * for credentialed requests).
What algorithm underpins most rate limiting?::Token bucket (or leaky bucket / fixed/sliding window) — tokens refill over time; a request without a token is rejected with 429 Too Many Requests.
Why version an API and what are two common schemes?::To evolve without breaking existing clients; via URL path (/v1/…) or a header (Accept: application/vnd.app.v1+json).
Why should API error responses avoid stack traces / SQL messages?::They leak internal structure to attackers (A05 Security Misconfiguration); return a generic message + code and log details server-side.
- Using PHP to Access MySQL and REST API — the modules these cards summarise.
- Flashcards — deck index.