How to use php to extend SQLite for lightweight database management

How to use PHP to extend SQLite for lightweight database management

Introduction:
SQLite is a lightweight embedded database engine that supports creating and managing databases locally or in memory. It does not require any server and is very convenient to use. In PHP, we can use SQLite extensions to operate SQLite databases. This article will introduce how to use PHP to extend SQLite for lightweight database management and provide some code examples.

Part One: Install SQLite Extension and SQLite Database

Before we begin, we need to make sure that PHP's SQLite extension and SQLite database have been installed. In most PHP installations, the SQLite extension is already installed by default. If you are using an older version of PHP, you can enable the SQLite extension via compile options. The process of installing a SQLite database is similar to installing other software packages. You can download the latest stable version from the SQLite official website (https://www.sqlite.org/).

Part 2: Connect to the SQLite database

Before using the SQLite database, we need to connect to the database first. The following is a sample code to connect to the database:

<?php
$db = new SQLite3('database.db');

The above code will create a database file named database.db in the current directory and connect it. If the database file already exists, the code will connect to the existing database. Otherwise, it automatically creates a new, empty database.

Part 3: Create tables and insert data

After connecting to the database, we can create tables and insert data. The following is a sample code:

<?php
$db->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)');
$db->exec("INSERT INTO users (name, email) VALUES ('John', 'john@example.com')");
$db->exec("INSERT INTO users (name, email) VALUES ('Jane', 'jane@example.com')");

The above code creates a table named users and inserts two pieces of data into the table.

Part 4: Querying Data

After inserting data, we can use various query statements to retrieve data from the database. The following are some commonly used query sample codes:

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

// 查询特定条件的数据
$result = $db->query("SELECT * FROM users WHERE name = 'John'");
while ($row = $result->fetchArray()) {
    echo "ID: {$row['id']}, Name: {$row['name']}, Email: {$row['email']}
";
}

In the above code, we use the SELECT statement to retrieve data from the users table. The first example code retrieves all the data, the second example code retrieves only the data with the name "John".

Part 5: Update and delete data

In addition to querying data, we can also update and delete data in the database. The following are some commonly used update and delete sample codes:

<?php
// 更新数据
$db->exec("UPDATE users SET email = 'new_email@example.com' WHERE id = 2");

// 删除数据
$db->exec("DELETE FROM users WHERE id = 1");

In the above code, we use the UPDATE statement to update the email of the user with id 2, and use the DELETE statement to delete the user with id 1.

Conclusion:
This article introduces how to use PHP to extend SQLite for lightweight database management. We learned the basic operations of connecting to a database, creating tables and inserting data, querying data, and updating and deleting data. I hope these sample codes will be helpful for you to learn SQLite database management.

The above is the detailed content of How to use php to extend SQLite for lightweight database management. For more information, please follow other related articles on the PHP Chinese website!