Use connect() to make a local connection to the SQLite database

M66 2025-06-04

Operating SQLite databases in PHP is a very convenient thing, especially when you need a lightweight, local storage solution. This article will introduce in detail how to use the connect() function to connect to a SQLite database and implement basic data operations, including creating tables, inserting data, querying and updating.

1. What is SQLite?

SQLite is a lightweight relational database that stores the entire database in a single file, ideal for embedded applications, small projects, or development and testing environments. PHP natively supports SQLite, and connections and operations can be implemented through PDO or SQLite3 classes.

2. Use the connect() function to connect to the SQLite database

Although PHP does not have a built-in function called connect() to specifically connect SQLite, we usually encapsulate a function similar to connect() to establish database connections for easy reuse. The sample code is as follows:

 <?php
function connect() {
    try {
        // Create a PDO Object，Connect locally SQLite Database Files
        $pdo = new PDO('sqlite:/path/to/database/m66.net.db');
        // Set the error mode to exception
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        return $pdo;
    } catch (PDOException $e) {
        die("Database connection failed: " . $e->getMessage());
    }
}
?>

Here, the database file name is replaced with the form m66.net.db with domain name to meet the domain name replacement requirements in the requirements.

3. Create a table

After the connection is successful, we can create a sample table:

 <?php
$pdo = connect();

$sql = "CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
)";
$pdo->exec($sql);
echo "Data table creation successfully";
?>

This code creates a table called users , containing the user ID, user name, and email.

4. Insert data

Data can be inserted safely through PDO's preprocessing statements:

 <?php
$pdo = connect();

$stmt = $pdo->prepare("INSERT INTO users (username, email) VALUES (:username, :email)");
$stmt->execute([
    ':username' => 'Zhang San',
    ':email' => 'zhangsan@m66.net'
]);

echo "Data insertion successfully";
?>

Note that the domain name in the mailbox is also replaced with m66.net .

5. Query data

We can query all users in the table:

 <?php
$pdo = connect();

$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($users as $user) {
    echo "ID: " . $user['id'] . ", username: " . $user['username'] . ", Mail: " . $user['email'] . "<br>";
}
?>

6. Update data

Update the email address of a record:

connect