PHP and MySQL Connectivity
Dynamic web applications require persistent storage. MySQL is the most common database paired with PHP. Three extensions connect PHP to MySQL: the deprecated mysql_*, the improved mysqli, and the modern PDO (PHP Data Objects). PDO is generally preferred for new code.
Connecting
A PDO connection uses a DSN string: $pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8mb4", $user, $pass, $opts);. Setting PDO::ATTR_ERRMODE to ERRMODE_EXCEPTION makes PDO throw exceptions on errors, simplifying error handling.
Executing Queries
Simple queries use $pdo->query($sql); parameterized queries use prepare() and execute(). Prepared statements dramatically improve security and performance and should be the default for any query accepting user input.
Prepared Statements
Prepared statements separate SQL from data: placeholders (? or named :name) mark where user input goes. The database parses the template once and inserts values safely. This prevents SQL injection attacks that devastate applications concatenating user input into queries.
CRUD Operations
Create: INSERT INTO users (name, email) VALUES (?, ?) followed by lastInsertId(). Read: SELECT with fetch() (one row) or fetchAll() (all rows). Update: UPDATE with a WHERE clause. Delete: DELETE with a WHERE clause. Fetch modes like PDO::FETCH_ASSOC and PDO::FETCH_OBJ shape the result style.
Transactions
For multi-statement operations that must succeed or fail together, use beginTransaction(), commit(), and rollBack(). Transactions preserve consistency in banking, inventory, and any multi-table updates.
Error Handling
PDO exceptions carry detailed messages useful in development; production should catch exceptions and display user-friendly errors while logging the details. Avoid exposing database internals to end users.
SQL Injection Prevention
Never concatenate user input into SQL. Always use prepared statements with bound parameters. Table or column names cannot be bound; validate them against a whitelist if they must come from user input. Escaping with quote() is a last resort.
Database Abstraction and ORMs
Real projects often use query builders like Doctrine DBAL or ORMs (Object-Relational Mappers) like Eloquent (Laravel) or Doctrine. They map tables to classes and rows to objects, cutting repetitive SQL and enforcing consistency.
Connection Pooling and Performance
PHP-FPM creates a fresh connection per request by default. Persistent connections, proper indexing, caching with Redis or Memcached, and limiting SELECT * are common optimizations for high-traffic sites.
Summary
PDO with prepared statements is the modern PHP idiom for working with MySQL. CRUD operations, transactions, and careful error handling cover most application logic. Combined with proper schema design, this enables robust and secure web applications.