Lesson 12 • Expert
Database Integration (MySQL) 🗄️
Connect PHP to MySQL with PDO — perform CRUD operations, use prepared statements, transactions, and protect against SQL injection.
What You'll Learn in This Lesson
- • Connecting to MySQL with PDO (recommended) and MySQLi
- • CRUD: Create, Read, Update, Delete operations
- • Prepared statements to prevent SQL injection
- • Transactions for atomic multi-query operations
- • Fetch modes and best practices for production
🎯 Real-World Analogy: A database is like a spreadsheet with superpowers. Tables are sheets, rows are records, columns are fields. PDO is your translator — it speaks SQL to the database and returns results in a format PHP understands. Prepared statements are like a fill-in-the-blank form — the structure is fixed, only the blanks change, so nobody can sneak in extra instructions.
Try It: CRUD Operations
Insert, select, update, and delete data with PDO prepared statements
JavaScript
// PHP + MySQL CRUD Operations (simulated in JavaScript)
console.log("=== Connecting to MySQL ===");
console.log();
console.log("Two options: MySQLi or PDO");
console.log();
console.log("// PDO (recommended — works with any database)");
console.log("$pdo = new PDO(");
console.log(" 'mysql:host=localhost;dbname=my_app;charset=utf8mb4',");
console.log(" 'username', 'password',");
console.log(" [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]");
console.log(");");
console.log();
// Simulate
...Try It: Security & Transactions
Prevent SQL injection, use named parameters, transactions, and compare PDO vs MySQLi
JavaScript
// Database Security & Best Practices
console.log("=== SQL Injection: The #1 Vulnerability ===");
console.log();
// BAD example
console.log("❌ NEVER DO THIS:");
console.log(' $sql = "SELECT * FROM users WHERE name = \'$name\'";');
console.log();
console.log(" If $name = \' OR 1=1 --");
console.log(" Query becomes:");
console.log(" SELECT * FROM users WHERE name = '' OR 1=1 --'");
console.log(" → Returns ALL users! 💀");
console.log();
// GOOD example
console.log("✅ ALWAYS USE PREPARED STA
...⚠️ Common Mistakes
⚠️
Concatenating user input into SQL — this is the #1 cause of data breaches. ALWAYS use prepared statements with
? or :name placeholders.⚠️
Not setting error mode — without
ERRMODE_EXCEPTION, PDO silently fails. Always set it on connection.⚠️
Exposing database errors to users — catch exceptions and show generic messages. Log the real error server-side.
💡
Pro Tip: Use PDO over MySQLi for new projects. PDO supports 12+ databases with the same API — switch from MySQL to PostgreSQL without rewriting queries.
📋 Quick Reference — PDO
| Method | Purpose |
|---|---|
| prepare() + execute() | Safe parameterized queries |
| fetch(FETCH_ASSOC) | Get one row as array |
| fetchAll(FETCH_ASSOC) | Get all rows as array |
| beginTransaction() | Start a transaction |
| commit() / rollBack() | Finish or undo transaction |
| lastInsertId() | Get ID of last inserted row |
🎉 Lesson Complete!
You can now connect PHP to a database securely! Next, learn comprehensive security best practices to protect your web application.
Sign up for free to track which lessons you've completed and get learning reminders.