Storing Data in a PHP Project

Over the last year I have been reevaluating how and were I store the data for my projects. Traditionally I have added a new database in MySQL for each but I thought that was a bit overkill for some of the smaller, single user, projects that I was working on.

There are several options for storing your data, but each comes with its own trade-offs in setup time, performance, and scalability. Here I look at the ones I have used in my prejects — flat files, SQLite, and MySQL — and see where each one shines (and where it doesn’t).

Flat Files with Serialised Data

Sometimes, you just want to save a bit of structured data — user preferences, settings, or a few records — without the overhead of spinning up a database. In that case, the humble flat file is your friend.

In PHP, this usually means writing an array or object to disk using serialize() or json_encode() and reading it back later with unserialize() or json_decode().

// Save
$data = ['name' => 'Neil', 'team' => 'Williams'];
file_put_contents('data.txt', serialize($data));

// Read
$data = unserialize(file_get_contents('data.txt'));

Pros

• Zero setup: No database server to install or configure. Works anywhere PHP does.
• Portable: Just copy the file to another machine and you’re good to go.
• Perfect for small datasets: Ideal for storing config, logs, or lightweight caching.

Cons

• Not concurrent: If two processes write at once, corruption can occur. You’ll need file locking (flock()) or your own queuing logic.
• Performance issues at scale: As the file grows, read/write operations slow down.
• No querying: You can’t do “SELECT * WHERE name = ‘Neil’” — it’s all or nothing.

When to use it

For small utilities, prototypes, or simple admin tools where only one process writes at a time. Think: “quick and dirty,” not “production-grade.”

Where I used it

When did I last? was the first project where I rejected MySQL and looked for some other way to store the data and decided on serialised data in a file. As it happens, I will probably migrate this to SQLite next year as part of a plan to convert it to an mobile app.

Local SQLite Database

SQLite is a brilliant middle ground between flat files and full-blown database servers. It’s a self-contained SQL database that lives in a single file, so you get the structure and query power of SQL without needing a server running.

PHP has built-in support for SQLite through PDO or the sqlite3 extension:

$db = new PDO('sqlite:data.db');
$db->exec("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)");
$db->exec("INSERT INTO users (name) VALUES ('Neil')");
foreach ($db->query('SELECT * FROM users') as $row) {
    echo $row['name'] . "\n";
}

Pros

• SQL support: Full relational database with real querying, joins, and indexing.
• No external server: Everything is self-contained in a file. Great for local dev or embedded apps.
• Fast for reads: Excellent performance for read-heavy workloads.

Cons

• Write locking: SQLite locks the whole database file on writes, so high concurrency can be a problem.
• Limited scalability: Fine for one app or user; not so much for multi-user web apps with simultaneous writes.
• File-based backup quirks: Copying while in use can cause corruption unless handled carefully.

When to use it

Perfect for small to medium apps, local development, or tools that need SQL structure but not a full database server. It’s also great for caching and testing before deploying to MySQL.

Where I used it

By the time that I released bookshelf I realised that the ability to query a database was an important consideration and so for this and other projects I switched from a flat file to SQLite. I like the flexibility of SQLite and that you can easily transport and backup the file.

MySQL Database

When you’re building something that’s going to live on the web and have more than a handful of users, MySQL (or MariaDB) is still the go-to choice for PHP. It’s robust, widely supported, and integrates beautifully with PHP’s PDO interface.

$pdo = new PDO('mysql:host=localhost;dbname=app', 'user', 'pass');
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = ?");
$stmt->execute(['Neil']);
$user = $stmt->fetch();

Pros

• Scalable: Handles large datasets and many concurrent users.
• Feature-rich: Transactions, indexing, foreign keys, views, and stored procedures.
• Widely supported: Almost every hosting platform and framework assumes MySQL is available.
• Good tools: phpMyAdmin, Adminer, and MySQL Workbench make management easy.

Cons

• Setup overhead: You need a running server and credentials. That’s overkill for small scripts.
• Configuration management: Permissions, ports, and backups need looking after.
• More moving parts: Adds complexity, especially if you just want to store a few records.

When to use it

When your app needs to handle real traffic, complex queries, or multiple users writing at once. If your project might grow, MySQL (or MariaDB) gives you room to expand.

Where I used it

I have a couple of high-load projects which use MySQL. A good example is The Weather Station which aggregates large amounts of data from sensors around our house along with energy usage and weather data into one platform. This needed somewhere robust to store this information and so MySQL was the obvious choices here.

So Which Should You Choose?

If you’re hacking together a one-off script or storing configuration, flat files are quick and simple.
If you want SQL but don’t fancy running a database server, SQLite is a sweet spot — ideal for standalone or low-traffic apps. And if you’re building something serious with users, transactions, or reporting, MySQL (or MariaDB) is the way to go.

As ever, the right tool depends on the job. Start small, and upgrade your storage when your app — or your ambitions — outgrow it.

If you found this useful, share it with someone who’s still using data.txt as a database and if you’ve got your own PHP storage horror stories — or an oddball setup that works — I’d love to hear about it.