Connecting to MySQL with PDO
Create a PDO connection with DSN, user, and password.
Connecting to MySQL with PDO is a free PHP Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PHP Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What is PDO?
PDO (PHP Data Objects) is a database-access abstraction layer. One API works with MySQL, PostgreSQL, SQLite, and more — just change the DSN.
The DSN
A DSN (Data Source Name) string identifies the driver, host, port, and database name.
<?php
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=myapp;charset=utf8mb4";Creating a PDO Instance
Pass the DSN, username, and password to the PDO constructor. Wrap in try/catch to handle connection errors.
<?php
try {
$pdo = new PDO(
"mysql:host=127.0.0.1;dbname=myapp;charset=utf8mb4",
"db_user",
"db_pass"
);
} catch (PDOException $e) {
die("Connection failed: ".$e->getMessage());
}Error Mode
Set the error mode to throw exceptions so errors are never silently ignored.
<?php
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);Default Fetch Mode
Configure the default fetch style — associative arrays are most convenient.
<?php
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);Full Connection Setup
Best practice: create PDO once with all attributes, then share the instance.
<?php
function createPDO(): PDO {
return new PDO(
"mysql:host=127.0.0.1;dbname=myapp;charset=utf8mb4",
"root", "secret",
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]
);
}Emulated Prepares
Setting ATTR_EMULATE_PREPARES to false uses true server-side prepared statements — safer and more efficient.
charset=utf8mb4
Always specify charset=utf8mb4 in the DSN. This supports the full Unicode range including emoji. Never use the old utf8 alias.
Running a Simple Query
Use query() only for statements with no user input.
<?php
$stmt = $pdo->query("SELECT COUNT(*) as total FROM users");
$row = $stmt->fetch();
echo $row["total"];exec() for Non-SELECT
exec() runs a non-returning statement and returns the number of affected rows.
<?php
$count = $pdo->exec("DELETE FROM sessions WHERE expires_at < NOW()");
echo "Deleted $count expired sessions";Persistent Connections
PDO supports persistent connections via PDO::ATTR_PERSISTENT => true. In PHP-FPM environments a connection pool is usually preferred.
Summary
Create PDO with a DSN, set ERRMODE_EXCEPTION and FETCH_ASSOC, disable emulated prepares. Never call query() with user-supplied data — use prepared statements.
Quick Check
Which attribute makes PDO throw exceptions on errors?
Frequently asked questions
Is the “Connecting to MySQL with PDO” lesson free?
Yes — the full text of “Connecting to MySQL with PDO” is free to read here on the web, and the PHP Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PHP Academy course, upgrade to CoddyKit PRO.
What will I learn in “Connecting to MySQL with PDO”?
Create a PDO connection with DSN, user, and password. You practise PHP Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start PHP Academy?
No prior experience is required. PHP Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Connecting to MySQL with PDO” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this PHP Academy lesson?
Yes. Every PHP Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Connecting to MySQL with PDO
- Prepared Statements and Binding
- Fetching Results with PDO
- Transactions with PDO