Best Practices for PHP with Databases

Introduction

PHP and databases go together like peanut butter and jelly, except sometimes the peanut butter is expired, and the jelly is SQL injection waiting to happen.

If you’ve ever written PHP code that interacts with a database, you know that things can get messy fast. Queries start sprawling across your code like spaghetti, performance issues sneak up like a ninja, and security holes appear like plot holes in a bad movie.

Fear not! In this guide, we’re going to cover best practices for PHP with databases—how to write secure, maintainable, and optimized database interactions that won’t haunt your future self.

1. Always Use Prepared Statements (No, Seriously, Always)

The Bad Way (Don’t do this!)

1
2
3
4
5
6
7

<?php
$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'user', 'password');
$username = $_GET['username'];
$password = $_GET['password'];
$query = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = $pdo->query($query);
?>

This is bad because if someone enters admin' -- as their username, they bypass authentication.

The Right Way: Use Prepared Statements

1
2
3
4
5
6
7

<?php
$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'user', 'password');
$query = "SELECT * FROM users WHERE username = :username AND password = :password";
$stmt = $pdo->prepare($query);
$stmt->execute(['username' => $_GET['username'], 'password' => $_GET['password']]);
$result = $stmt->fetchAll();
?>

This ensures user input is safely escaped and sanitized, making SQL injection virtually impossible.

2. Use PDO Instead of mysqli_*

mysqli_* functions are like that outdated library in your project—still around, but PDO is just better.

Why Use PDO?

• Supports multiple databases (MySQL, PostgreSQL, SQLite, etc.).
• Uses prepared statements by default.
• Cleaner, more readable code.

3. Optimize Queries (Don’t Be a Performance Sloth)

Index Your Database

Indexes are like bookmarks for databases. Without them, MySQL will have to scan every row, which is painfully slow.

1

CREATE INDEX idx_username ON users(username);

Avoid SELECT * (Be Specific)

Instead of this:

1

SELECT * FROM users;

Do this:

1

SELECT id, username FROM users;

Only query what you need.

4. Keep Database Credentials Secure

Hardcoding credentials is a rookie mistake. Instead, use environment variables.

1
2
3

DB_HOST=localhost
DB_USER=root
DB_PASS=supersecurepassword

Then access them in PHP:

1
2
3
4
5
6

<?php
$dsn = "mysql:host=" . getenv('DB_HOST') . ";dbname=mydb";
$user = getenv('DB_USER');
$pass = getenv('DB_PASS');
$pdo = new PDO($dsn, $user, $pass);
?>

This keeps credentials out of your codebase.

5. Consider an ORM (But Don’t Overdo It)

ORMs (like Doctrine, Eloquent) abstract away SQL and let you work with objects.

Instead of writing:

1

SELECT * FROM users WHERE id = 1;

You do:

1

$user = User::find(1);

Great for maintainability, but remember ORMS can be slow. Sometimes, writing raw SQL is better.

6. Use Database Transactions for Multiple Queries

When performing multiple dependent queries, use transactions to prevent partial updates.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

<?php
$pdo->beginTransaction();
try {
    $pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    $pdo->exec("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}
?>

If something fails, ROLLBACK ensures no money disappears into the void.

Conclusion

Working with databases in PHP doesn’t have to be a nightmare.

Follow these best practices to ensure your queries are secure, optimized, and maintainable. Future-you will thank you!

Key Ideas

References

• PHP PDO Documentation
• SQL Injection Prevention
• MySQL Indexing