PHP MySQL Database
PHP MySQL Database: Connecting and Running Safe CRUD Operations
PHP works reliably with MySQL—the open-source relational database powering most dynamic web applications—through the PDO (PHP Data Objects) and MySQLi extensions. Using parameterized PDO queries lets your PHP backend create, read, update, and delete records safely while blocking SQL injection attacks.
How PHP Communicates with MySQL
Every database-backed PHP request follows a clean four-step flow between your code and the MySQL server.
flowchart LR
A["PHP Script"] -->|"1. Connect via PDO DSN"| B["MySQL Server"]
A -->|"2. Prepare SQL Template"| B
A -->|"3. Execute Bound Parameters"| B
B -->|"4. Return Rows or Insert ID"| A
Instead of concatenating raw user input into SQL strings, PDO sends the SQL template first and transmits user values separately. Even if a form contains quotes or SQL keywords, MySQL treats them strictly as plain text values.
Complete Safe PDO Example with Sample Tables
The example below connects to a sample bookstore_demo database, inserts a row into a books table, and reads back matching records using named prepared parameters.
<?php
declare(strict_types=1);
$dsn = 'mysql:host=127.0.0.1;dbname=bookstore_demo;charset=utf8mb4';
$pdo = new PDO($dsn, 'app_user', 'secret_pass', [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]);
// 1. Safe parameterized INSERT into sample `books` table
$insertStmt = $pdo->prepare(
'INSERT INTO books (isbn, title, price) VALUES (:isbn, :title, :price)'
);
$insertStmt->execute([
':isbn' => '978-0134685991',
':title' => 'Clean Backend Patterns',
':price' => 34.99,
]);
// 2. Safe parameterized SELECT from sample `books` table
$selectStmt = $pdo->prepare(
'SELECT id, isbn, title, price FROM books WHERE price <= :max_price ORDER BY id DESC LIMIT 10'
);
$selectStmt->execute([':max_price' => 50.00]);
$books = $selectStmt->fetchAll();
print_r($books);
Common Mistakes to Avoid
- Using removed
mysql_*functions: Legacymysql_connect()andmysql_query()were removed completely in PHP 7. Always usePDOor object-orientedmysqliprepared statements. - Mixing variables directly into SQL strings: Writing
"WHERE id = $id"exposes your database to SQL injection. Bind every value through:placeholdersor?. - Displaying raw
$e->getMessage()in production: Log PDO exceptions on the server and show a clean, generic message to users.
Frequently Asked Questions
Which driver should beginners learn first: PDO or MySQLi?
Learn PDO first. Its named placeholders (:email) are easier to read, its fetchAll() API is cleaner, and switching between MySQL, SQLite, and PostgreSQL requires minimal code changes.
How do I keep multiple related SQL queries consistent?
Wrap related INSERT and UPDATE queries inside $pdo->beginTransaction(), $pdo->commit(), and $pdo->rollBack() on exceptions.
Why is utf8mb4 required in the MySQL PDO DSN?
utf8mb4 supports full 4-byte Unicode characters including emojis and international scripts, preventing silent text truncation.
Unlock Your
Full Potential.
Sign in to track your learning journey, earn industry-recognized certificates, and join our elite developer community.
Quick Access With
Try it Yourself
Experiment with the code from this lesson in our interactive playground.
Test Your Knowledge
You've completed this section! Take a quick 5-question quiz to check your understanding.