How to Use MySQL Database with PHP

PHP and MySQL are practically siblings — I’ve been pairing the two since my early freelancing days building small business websites, and I still use this combo today for a good chunk of my backend work. WordPress, Laravel, and countless custom apps run on this exact stack. In this guide I’ll walk through everything from a first connection to production-grade transaction handling and security, the way I actually build it.

Why PHP and MySQL Are a Natural Fit

PHP was practically built with MySQL in mind — the original mysql_* functions (long deprecated and removed) shipped with PHP for years before being replaced by safer alternatives. Today I exclusively use one of two extensions:

• MySQLi (MySQL Improved) — procedural or object-oriented, supports prepared statements
• PDO (PHP Data Objects) — database-agnostic, supports MySQL, Postgres, SQLite, etc. with the same API

I default to PDO in almost every new project because it gives me flexibility if I ever need to support another database, and its prepared statement handling is clean.

MySQL Architecture Overview

graph TD
    A[PHP Script / Framework] --> B[PDO or MySQLi Driver]
    B --> C[MySQL Connection Handler]
    C --> D[SQL Parser & Optimizer]
    D --> E[InnoDB Storage Engine]
    E --> F[(Buffer Pool - RAM)]
    E --> G[(Tablespace - Disk)]

Every query from PHP travels through the driver, into MySQL’s connection handler, gets parsed and optimized, then executed against the storage engine. Knowing this pipeline matters because a huge share of “PHP MySQL is slow” complaints I’ve debugged over the years were actually query-plan problems, not PHP problems at all.

Setting Up the Connection with PDO

<?php
$host = 'localhost';
$db   = 'shop_db';
$user = 'shop_app';
$pass = 'StrongPassword123!';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Connected successfully";
} catch (PDOException $e) {
    error_log("Connection failed: " . $e->getMessage());
    die("Database connection error");
}

A few choices I always make here on purpose:

• PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION — I want failures to throw exceptions, not silently fail or need manual error checking after every call.
• PDO::ATTR_EMULATE_PREPARES => false — this forces PDO to use MySQL’s real native prepared statements instead of emulating them client-side, which is both safer and usually faster.
• charset=utf8mb4 — the full 4-byte UTF-8 charset, which supports emojis and the complete Unicode range. I never use plain utf8 (MySQL’s legacy 3-byte variant) anymore.

CRUD Operations with Prepared Statements

Insert

$stmt = $pdo->prepare(
    "INSERT INTO products (name, price, stock) VALUES (:name, :price, :stock)"
);
$stmt->execute([
    ':name'  => 'USB-C Hub',
    ':price' => 34.99,
    ':stock' => 75
]);
echo "Inserted ID: " . $pdo->lastInsertId();

Select

$stmt = $pdo->prepare("SELECT id, name, price FROM products WHERE stock > :min_stock ORDER BY price DESC LIMIT 10");
$stmt->execute([':min_stock' => 0]);
$products = $stmt->fetchAll();

foreach ($products as $product) {
    echo "{$product['name']} - \${$product['price']}\n";
}

Update

$stmt = $pdo->prepare("UPDATE products SET stock = stock - :qty WHERE id = :id");
$stmt->execute([':qty' => 1, ':id' => 12]);
echo "Rows affected: " . $stmt->rowCount();

Delete

$stmt = $pdo->prepare("DELETE FROM products WHERE id = :id");
$stmt->execute([':id' => 12]);

I use named placeholders (:name) over positional (?) whenever a query has more than two or three parameters — it makes the code far more readable and less error-prone when parameter order shifts during refactoring.

Why Prepared Statements Matter So Much in PHP

PHP applications historically got hit hard by SQL injection because early code concatenated $_GET/$_POST values directly into queries. I treat prepared statements as completely non-negotiable:

// NEVER do this
$unsafe = "SELECT * FROM users WHERE email = '" . $_POST['email'] . "'";

// ALWAYS do this
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute([':email' => $_POST['email']]);

The first version lets an attacker submit ' OR '1'='1 and dump every row in the table. The second treats the input strictly as data, never as executable SQL.

Connection Pooling in PHP — What’s Actually Different

Here’s something I explain to every developer moving from Node.js or Python to PHP: PHP (in its traditional PHP-FPM model) doesn’t maintain long-lived, in-process connection pools the same way. Each request typically gets its own PHP process/thread that starts and ends per HTTP request, so:

• Persistent connections (PDO::ATTR_PERSISTENT => true) let PHP-FPM reuse a MySQL connection across requests handled by the same worker process, reducing connection overhead.

$options = [
    PDO::ATTR_PERSISTENT => true,
    PDO::ATTR_ERRMODE    => PDO::ERRMODE_EXCEPTION,
];
$pdo = new PDO($dsn, $user, $pass, $options);

I use persistent connections carefully — they can lead to “stuck” transaction state or leaked locks if a script errors mid-transaction, so I only enable them after load testing confirms they help, and I make sure every transaction always ends in either commit() or rollBack().

• If I’m running PHP in a long-running process model (like Swoole or RoadRunner), true connection pooling becomes possible and behaves much more like the Node.js/Python examples.

sequenceDiagram
    participant Client
    participant PHPFPM as PHP-FPM Worker
    participant MySQL

    Client->>PHPFPM: HTTP Request
    PHPFPM->>MySQL: Reuse persistent connection (if enabled)
    MySQL-->>PHPFPM: Query result
    PHPFPM-->>Client: HTTP Response
    Note over PHPFPM,MySQL: Connection may persist across requests on the same worker

Transactions in PHP

Here’s a stock-transfer example, the same pattern I use in Node and Python, adapted to PDO:

function transferStock(PDO $pdo, int $productId, int $fromWarehouse, int $toWarehouse, int $qty): bool
{
    try {
        $pdo->beginTransaction();

        $stmt = $pdo->prepare(
            "SELECT quantity FROM warehouse_stock WHERE product_id = :pid AND warehouse_id = :wid FOR UPDATE"
        );
        $stmt->execute([':pid' => $productId, ':wid' => $fromWarehouse]);
        $current = $stmt->fetchColumn();

        if ($current < $qty) {
            throw new Exception("Not enough stock to transfer");
        }

        $pdo->prepare(
            "UPDATE warehouse_stock SET quantity = quantity - :qty WHERE product_id = :pid AND warehouse_id = :wid"
        )->execute([':qty' => $qty, ':pid' => $productId, ':wid' => $fromWarehouse]);

        $pdo->prepare(
            "UPDATE warehouse_stock SET quantity = quantity + :qty WHERE product_id = :pid AND warehouse_id = :wid"
        )->execute([':qty' => $qty, ':pid' => $productId, ':wid' => $toWarehouse]);

        $pdo->commit();
        return true;
    } catch (Exception $e) {
        $pdo->rollBack();
        error_log("Transfer failed: " . $e->getMessage());
        return false;
    }
}

Same principle as before: FOR UPDATE locks the row so no concurrent transfer can read stale stock numbers.

Indexing and Query Tuning From PHP’s Side

I regularly run EXPLAIN directly through PDO while developing:

$stmt = $pdo->query("EXPLAIN SELECT id, name FROM products WHERE stock > 0 ORDER BY price DESC");
foreach ($stmt->fetchAll() as $row) {
    print_r($row);
}

If I see type: ALL (a full table scan), I add an index that matches my actual query pattern:

CREATE INDEX idx_stock_price ON products (stock, price);

Security Best Practices

• Prepared statements everywhere, no exceptions.
• Least-privilege MySQL users — a PHP app account should almost never have DROP or ALTER privileges.
• Store credentials in environment variables, not committed config files. I use .env with vlucas/phpdotenv in non-framework projects, or the framework’s built-in config system (Laravel’s .env, for example).
• Disable detailed error output in production — I set display_errors = Off in php.ini and log errors instead, so stack traces with schema details never reach the browser.
• Escape output, not just input — I use htmlspecialchars() when rendering DB content back into HTML to prevent stored XSS, which is a separate concern from SQL injection but often shows up in the same forms.

Performance Optimization Tips

1. Use prepared statement reuse in loops instead of re-preparing:

$stmt = $pdo->prepare("INSERT INTO logs (message, level) VALUES (:msg, :level)");
foreach ($logEntries as $entry) {
    $stmt->execute([':msg' => $entry['message'], ':level' => $entry['level']]);
}

2. Fetch only needed columns, avoid SELECT * in hot paths.
3. Use LIMIT for pagination, and prefer keyset pagination over large OFFSET on big tables.
4. Cache expensive read queries with Redis or Memcached when the data doesn’t change every request.
5. Enable OPcache in PHP itself — while not a MySQL setting, a huge amount of PHP request time is often PHP compilation overhead, not the database call.
6. Watch innodb_buffer_pool_size on the server side — for a dedicated MySQL server I usually configure it around 60–70% of available RAM.

Real-World Scenario: A Laravel E-Commerce Backend

On one Laravel project, our checkout endpoint was timing out under load. The root cause wasn’t PHP at all — it was a missing composite index on the orders table combined with SELECT * queries pulling large TEXT columns we didn’t need for that endpoint. Adding a targeted index and trimming the column list dropped average response time from ~800ms to under 90ms. It’s a great reminder that in a PHP + MySQL stack, database tuning usually matters more than PHP-level micro-optimization.

Troubleshooting Common Issues

Frequently Asked Questions

Should I use MySQLi or PDO? I recommend PDO for almost everything — it’s cleaner and database-agnostic. MySQLi is fine if you specifically need MySQL-only features PDO doesn’t expose, which is rare in typical application code.

Are persistent connections always a good idea? No. I only enable them after testing — they can cause subtle bugs with leftover session state (like an uncommitted transaction) if not handled carefully.

Is an ORM like Eloquent (Laravel) safe from SQL injection? Yes, when used properly — Eloquent uses parameter binding under the hood. Raw query methods (DB::raw()) still require the same discipline as manual PDO code.

How do I handle large file uploads or BLOBs with MySQL and PHP? I generally avoid storing large binary files directly in MySQL and instead store them in object storage (like S3) and keep only the reference/path in the database — it keeps the database lean and backups fast.

Interview Questions on This Topic

1. What’s the difference between MySQLi and PDO, and why might you choose one over the other?
2. Why is PDO::ATTR_EMULATE_PREPARES => false recommended for security and performance?
3. Explain the risk of using persistent MySQL connections in PHP-FPM without careful transaction handling.
4. How do prepared statements prevent SQL injection at a technical level?
5. Why might a PHP endpoint be slow even when the PHP code itself is efficient?

Key Takeaways

• Use PDO with real prepared statements (EMULATE_PREPARES => false) for security and clean code.
• Understand that PHP’s per-request model changes how “connection pooling” works compared to Node.js or Python.
• Wrap multi-step writes in transactions with row-level locks for consistency under concurrency.
• Most PHP + MySQL performance problems trace back to missing indexes or over-fetching columns, not PHP itself.
• Never expose raw database errors to end users; log them and fail gracefully.

References

• MySQL 8.0 Reference Manual: https://dev.mysql.com/doc/refman/8.0/en/
• PHP PDO Documentation: https://www.php.net/manual/en/book.pdo.php
• PHP MySQLi Documentation: https://www.php.net/manual/en/book.mysqli.php