Using SQLite with PHP: Comparison with MySQL and Practical Code Examples

SQLite is an embedded relational database engine that is open‑source, zero‑configuration, and lightweight; it stores the entire database in a single file on the host file system, making it ideal for embedded devices, mobile applications, and small‑scale projects.

In contrast, MySQL is a client/server relational database system that runs as an independent server, supports multiple users and threads, and is suited for large‑scale, high‑concurrency enterprise and internet applications.

Advantages of SQLite

Simple to use: no separate server process is required.

Lightweight: small footprint and low resource consumption.

Zero configuration: a single database file is sufficient.

Single‑user: only one process accesses the database at a time, which can simplify consistency in certain scenarios.

Disadvantages of SQLite

Concurrency limits: performance may degrade under high concurrent access.

Storage capacity limits: not ideal for very large datasets.

Feature limitations: lacks some advanced features such as complex queries, stored procedures, and triggers that MySQL provides.

Typical use cases for SQLite

Mobile applications that need a local data store.

Embedded systems such as IoT devices.

Single‑user applications where multi‑user concurrency is not required.

Rapid prototyping and development of small websites.

PHP projects that require a simple, file‑based database.

Below is a step‑by‑step PHP tutorial demonstrating how to work with SQLite.

1. Connect to the SQLite database

<?php<br/>// Connect to SQLite database<br/>$db = new SQLite3('database.db');<br>?>

2. Create a table

<?php<br/>// Create table<br/>$query = 'CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT)';<br/>$db->exec($query);<br?>>

3. Insert a record

<?php<br/>// Insert data<br/>$query = "INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]')";<br/>$db->exec($query);<br?>>

4. Query and display records

<?php<br/>// Query data<br/>$query = "SELECT * FROM users";<br/>$result = $db->query($query);<br/>while ($row = $result->fetchArray()) {<br/>    echo "ID: " . $row['id'] . "<br>";<br/>    echo "Name: " . $row['name'] . "<br>";<br/>    echo "Email: " . $row['email'] . "<br><br>";<br/>}<br?>>

5. Update and delete records

<?php<br/>// Update data<br/>$query = "UPDATE users SET email = '[email protected]' WHERE id = 1";<br/>$db->exec($query);<br/><br/>// Delete data<br/>$query = "DELETE FROM users WHERE id = 2";<br/>$db->exec($query);<br?>>

6. Close the database connection

<?php<br/>// Close database connection<br/>$db->close();<br?>>