Discover why switching from mysql_* to PDO in PHP improves security and enhances database interaction flexibility.
You upgrade a server, run your app on PHP 7, and half of it goes dark. The culprit is almost always the same: old mysql_* calls that stopped existing years ago. PHP deprecated that extension in 5.5 and removed it entirely in 7.0, so any code still leaning on mysql_connect() or mysql_query() simply fails on a modern runtime.
The fix is to move to PDO (PHP Data Objects). It runs on current PHP, it gives you prepared statements to shut down SQL injection, and it talks to more than just MySQL. Here’s why the switch matters and how to make it, step by step.
Table of Contents
- Why Should You Migrate from mysql_*?
- What is PDO?
- Advantages of PDO Over mysql_* Functions
- How to Migrate from mysql_* to PDO
- Best Practices for Using PDO
- Conclusion
Why Should You Migrate from mysql_*?
If your code still calls mysql_* functions, you’re carrying two problems at once. The extension has been deprecated since PHP 5.5 and gone entirely since PHP 7.0, so the code won’t run on anything current. And even where it does run on an old install, it leaves you exposed.
Three reasons to make the move:
- Security: The mysql_* functions have no support for prepared statements, and prepared statements are how you keep user input from being run as SQL. Without them, you’re one bad query away from an injection hole.
- Compatibility: Modern PHP doesn’t ship these functions at all. Stay on them and you’re stuck on an unsupported PHP version, which is its own security liability.
- Flexibility: mysql_* only speaks to MySQL. PDO works across MySQL, PostgreSQL, SQLite, and more, so switching databases later doesn’t mean rewriting your data layer.
Migrate and you get the whole package: safer queries, code that runs on the PHP you actually have, and room to change databases without tearing everything out.
What is PDO?
PDO (PHP Data Objects) is a database abstraction layer. It gives you one consistent set of methods for talking to different databases, so moving from MySQL to PostgreSQL or SQLite is mostly a matter of changing the connection string, not the code around it.
It also does what the old functions never could. PDO supports prepared statements, transactions, and exception-based error handling out of the box. Those aren’t extras; they’re the features that make your database code both safer and easier to reason about.
Advantages of PDO Over mysql_* Functions
Here’s what you gain by leaving mysql_* behind:
- Multiple databases: One API for MySQL, PostgreSQL, SQLite, and others. Swap the driver, keep your logic.
- Prepared statements: User input is bound as data, never executed as SQL. This is your main defense against injection.
- Transactions: Group several queries so they either all commit or all roll back. No half-finished writes.
- Exception-based errors: PDO can throw on failure instead of failing quietly, which makes bugs surface where you can see them.
- Object-oriented interface: Working with statement and connection objects keeps the code cleaner and easier to maintain.
How to Migrate from mysql_* to PDO
The migration is mechanical once you’ve seen the pattern. Let’s walk a basic connect-and-query flow from mysql_* to PDO, one piece at a time.
Step 1: Create a PDO Connection
Start with the connection. Here’s the old mysql_connect() approach you’re replacing:
Example of old mysql_* connection
<?php
// Old mysql_* connection
$connection = mysql_connect("localhost", "username", "password");
mysql_select_db("database", $connection);With PDO, you build a connection object instead:
Example of PDO connection
<?php
// New PDO connection
$dsn = 'mysql:host=localhost;dbname=database';
$username = 'username';
$password = 'password';
try {
$conn = new PDO($dsn, $username, $password);
// Set error mode to exceptions
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}Notice the connection and its database go in one string, and a failed connect throws an exception you can catch. That’s error handling you get for free.
Step 2: Execute Queries with PDO
Next, swap mysql_query() for PDO’s query() and prepare() methods. A plain SELECT under the old functions looked like this:
Example of mysql_query()
<?php
$result = mysql_query("SELECT * FROM users WHERE id = 1");The PDO version:
Example of PDO query()
<?php
// Execute query with PDO
$query = $conn->query("SELECT * FROM users WHERE id = 1");
$result = $query->fetchAll(PDO::FETCH_ASSOC);Here fetchAll() hands you the rows as an associative array. Use query() like this only for fixed SQL with no user input in it.
Step 3: Use Prepared Statements for User Input
The moment user input touches a query, you switch to prepared statements. This is the whole reason to migrate. Look at what mysql_query() tempts you into:
Example of insecure mysql_query()
<?php
$user_id = $_GET['id'];
$result = mysql_query("SELECT * FROM users WHERE id = $user_id");That dropped a raw request value straight into SQL. Anyone can rewrite the query through the URL. Here’s the same thing done safely with PDO:
Example of PDO prepared statements
<?php
$user_id = $_GET['id'];
// Prepare the SQL statement
$stmt = $conn->prepare("SELECT * FROM users WHERE id = :id");
// Bind the parameter to the query
$stmt->bindParam(':id', $user_id, PDO::PARAM_INT);
// Execute the statement
$stmt->execute();
// Fetch the results
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);The input is bound as a parameter, so the database treats it as a value and never as code. That single change is what closes the injection door.
Best Practices for Using PDO
A few habits keep PDO code safe and easy to live with:
- Always use prepared statements: Never concatenate user input into SQL. Bind it as a parameter every time, no exceptions.
- Turn on exception errors: Set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION so failures throw instead of passing silently. You’ll catch problems while you’re still looking.
- Use transactions for multi-step writes: When several inserts or updates need to succeed together, wrap them in a transaction so a partial failure rolls back cleanly.
- Fetch what you need: Reach for fetch() or fetchAll() depending on whether you want one row or all of them, and name a fetch mode like PDO::FETCH_ASSOC to control the shape of the result.
- Release connections when it counts: PDO closes the connection at the end of the script anyway, but in a long-running process, setting the handle to null when you’re done frees it sooner.
Conclusion
Moving off mysql_* isn’t optional busywork. The functions are gone from every supported PHP version, and the code that replaces them is safer by design. PDO gives you prepared statements, transactions, real error handling, and one interface across databases.
Make the switch and you get two wins in one pass: your app runs on current PHP again, and the injection risk that came bundled with the old functions goes with them. It’s a contained change with an outsized payoff.