MindMap Gallery Mind Map: PHP Database Operations
Unlock the power of PHP database operations with our comprehensive guide! This overview covers essential goals, connection methods, and core CRUD operations. Explore the differences between MySQLi and PDO, including when to use each. Learn how to execute queries safely, manage transactions, and handle errors effectively. Dive into prepared statements to prevent SQL injection and improve performance. Understand various fetching techniques and parameter binding options. We also address best practices for security, including input validation and secrets management. Enhance your PHP projects with robust database interactions today!
Edited at 2026-03-25 15:27:11Join us in learning the art of applause! This engaging program for Grade 3 students focuses on the appropriate times to applaud during assemblies and performances, emphasizing respect and appreciation for performers. Students will explore the significance of applauding, from encouraging speakers to maintaining good audience manners. They will learn when to applaudsuch as after performances or when speakers are introducedand when to refrain from clapping, ensuring they don't interrupt quiet moments or ongoing performances. Through fun activities like the "Applause or Pause" game and role-playing a mini assembly, students will practice respectful applause techniques. Success will be measured by their ability to clap at the right times, demonstrate respect during quiet moments, and support their peers kindly. Let's foster a community of respectful audience members together!
In our Grade 4 lesson on caring for classmates who feel unwell, we equip students with essential skills for handling such situations compassionately and effectively. The lesson unfolds in seven stages, starting with daily preparedness, where students learn to recognize signs of illness and the importance of communicating with adults. Next, they practice checking in with a classmate politely and keeping them comfortable. Students are then guided to inform the teacher promptly and offer safe help while waiting. In case of serious symptoms, they learn to seek adult assistance immediately. After the situation is handled, students reflect on their actions and continue improving their response skills for future incidents. This comprehensive approach fosters empathy and responsibility in our classroom community.
Join us in Grade 2 as we explore the important topic of keeping friends' secrets! In this engaging session, students will learn what a secret is, how to distinguish between safe and unsafe secrets, and identify trusted adults they can turn to for help. We’ll discuss the difference between surprises, which are short-lived and joyful, and secrets that can sometimes cause worry. Through interactive activities like sorting games and role-playing, children will practice recognizing unsafe situations and the importance of sharing concerns with adults. Remember, safety is always more important than secrecy!
Join us in learning the art of applause! This engaging program for Grade 3 students focuses on the appropriate times to applaud during assemblies and performances, emphasizing respect and appreciation for performers. Students will explore the significance of applauding, from encouraging speakers to maintaining good audience manners. They will learn when to applaudsuch as after performances or when speakers are introducedand when to refrain from clapping, ensuring they don't interrupt quiet moments or ongoing performances. Through fun activities like the "Applause or Pause" game and role-playing a mini assembly, students will practice respectful applause techniques. Success will be measured by their ability to clap at the right times, demonstrate respect during quiet moments, and support their peers kindly. Let's foster a community of respectful audience members together!
In our Grade 4 lesson on caring for classmates who feel unwell, we equip students with essential skills for handling such situations compassionately and effectively. The lesson unfolds in seven stages, starting with daily preparedness, where students learn to recognize signs of illness and the importance of communicating with adults. Next, they practice checking in with a classmate politely and keeping them comfortable. Students are then guided to inform the teacher promptly and offer safe help while waiting. In case of serious symptoms, they learn to seek adult assistance immediately. After the situation is handled, students reflect on their actions and continue improving their response skills for future incidents. This comprehensive approach fosters empathy and responsibility in our classroom community.
Join us in Grade 2 as we explore the important topic of keeping friends' secrets! In this engaging session, students will learn what a secret is, how to distinguish between safe and unsafe secrets, and identify trusted adults they can turn to for help. We’ll discuss the difference between surprises, which are short-lived and joyful, and secrets that can sometimes cause worry. Through interactive activities like sorting games and role-playing, children will practice recognizing unsafe situations and the importance of sharing concerns with adults. Remember, safety is always more important than secrecy!
PHP Database Operations
Overview
Goals
Establish connections
Execute queries safely
Handle results efficiently
Manage transactions and errors
Common database types
MySQL / MariaDB
PostgreSQL
SQLite
Connection Methods
MySQLi (MySQL Improved)
Modes
Procedural
Object-oriented
Connection examples
`mysqli_connect(host, user, pass, db)`
`new mysqli(host, user, pass, db)`
When to use
MySQL-only projects
Simple apps with limited DB portability needs
PDO (PHP Data Objects)
Concept
Unified interface for multiple DB drivers
DSN format
`mysql:host=...;dbname=...;charset=utf8mb4`
`pgsql:host=...;dbname=...`
`sqlite:/path/to/db.sqlite`
When to use
Multi-database support
Consistent prepared statements and transactions
Choose MySQLi for MySQL-only simplicity; choose PDO for portability and consistent prepared statements/transactions.
Core Operations (CRUD)
Create (INSERT)
Use prepared statements
Retrieve last inserted ID
MySQLi: `$stmt->insert_id` / `$mysqli->insert_id`
PDO: `$pdo->lastInsertId()`
Read (SELECT)
Fetch patterns
Single row
Multiple rows
Pagination
`LIMIT` / `OFFSET`
Ordering and filtering
`WHERE`, `ORDER BY`
Update (UPDATE)
Affected rows
MySQLi: `$stmt->affected_rows`
PDO: `$stmt->rowCount()`
Delete (DELETE)
Soft delete vs hard delete
Flag column (e.g., `deleted_at`)
Permanent removal
Query Execution
Direct queries (discouraged for user input)
MySQLi: `$mysqli->query($sql)`
PDO: `$pdo->query($sql)`
Prepared statements (recommended)
Benefits
Prevent SQL injection
Better performance for repeated queries
MySQLi workflow
`prepare()` → `bind_param()` → `execute()` → `get_result()/bind_result()`
PDO workflow
`prepare()` → `bindValue()/execute(array)` → `fetch()/fetchAll()`
Fetching Results
MySQLi
`fetch_assoc()`
`fetch_row()`
`fetch_all(MYSQLI_ASSOC)`
PDO
Fetch modes
`PDO::FETCH_ASSOC`
`PDO::FETCH_OBJ`
`PDO::FETCH_CLASS`
Methods
`fetch()`
`fetchAll()`
Parameter Binding
MySQLi bind types
`i` (integer), `d` (double), `s` (string), `b` (blob)
PDO binding approaches
Positional placeholders: `?`
Named placeholders: `:name`
Binding options
`bindValue()`
`bindParam()` (by reference)
Transactions
Use cases
Multiple dependent writes
Consistency across operations
MySQLi
`begin_transaction()`
`commit()`
`rollback()`
PDO
`beginTransaction()`
`commit()`
`rollBack()`
Error Handling & Debugging
MySQLi
Error properties
`$mysqli->connect_error`
`$mysqli->error`
Reporting
`mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT)`
PDO
Error modes
`PDO::ERRMODE_EXCEPTION` (recommended)
`PDO::ERRMODE_SILENT`
Inspecting errors
`$stmt->errorInfo()`
Logging practices
Log errors server-side
Avoid exposing SQL details to users
Security Best Practices
Prevent SQL injection
Always use prepared statements for user input
Validate and sanitize input
Type checks and allowlists
Least privilege
Separate DB users for read/write if needed
Charset and collation
Use `utf8mb4`
Secrets management
Environment variables
Avoid committing credentials to source control
Performance & Maintainability
Indexing basics
Index frequently filtered/joined columns
Connection management
Reuse connections per request
Persistent connections (use carefully)
Efficient fetching
Select only needed columns
Stream large results when possible
Abstraction patterns
Repository/DAO layer
Centralized DB wrapper and configuration
Common Pitfalls
Mixing APIs
Avoid combining MySQLi and PDO in the same layer
Forgetting to handle failures
Check execute results / catch exceptions
Using emulated prepares (PDO)
Prefer native prepares when available
Not closing resources
Free result sets, close statements when appropriate
Most issues come from unsafe queries, inconsistent API usage, and missing lifecycle handling (errors, resources, prepares).