PHP uses SQLite3 embedded relational database

introduce

SQLite is an embedded relational database management system that is a file-based, server-less database engine compared to other database management systems (e.g., MySQL, PostgreSQL). In PHP, we can use the SQLite3 extension to manipulate SQLite databases.

Installation

The SQLite3 extension is enabled by default. Allows disabling at compile time with --without-sqlite3. Windows users must have php_sqlite3.dll enabled to use the extension. The DLL file for this extension is included in the PHP distribution for Windows.

Note: Additional settings on Windows as of PHP 7.4.0. In order for this extension to take effect, the DLL file must be able to be found in the path indicated by the PATH on the Windows system. For information on how to do this, see the FAQ titled "How to add a PHP directory to PATH in Windows". While copying DLL files from a PHP folder to a Windows system directory is fine, it is not recommended. This extension requires the following files to be :libsqlite3.dll in the PATH path.

use

connect

In PHP, we can use the SQLite3 class to connect to the SQLite3 database. A database connection can be created by instantiating an SQLite3 object and passing in the path of the database file as a parameter.

$dbFile = runtime_path() . DIRECTORY_SEPARATOR . 'tinywan.db';
$database = new SQLite3($dbFile);
var_dump($database);           

In the example above, we are connected to an SQLite3 database called tinywan.db. If the tinywan.db does not exist, SQLite3 will automatically create an empty database file.

printout

class SQLite3#2 (0) {
}           

Create a table

In SQLite3, SQL statements are used to create tables. You can create a table by calling the exec() method and passing in the SQL statement that created the table.

The following code shows how to create a table named resty_user in an SQLite3 database

$database->exec('CREATE TABLE IF NOT EXISTS resty_user (id INTEGER PRIMARY KEY, username TEXT, age INTEGER)');           

In the example above, a resty_user table with id, username, and age fields is created. The id field is set as the primary key and is automatically incremented.

Insert data

In SQLite3, we use the INSERT INTO statement of the SQL statement to insert the data. Data can be inserted by calling the exec() method and passing in the corresponding SQL statement.

The following code shows how to insert a piece of data into a resty_user table:

$database->exec("INSERT INTO resty_user (username, age) VALUES ('Tinywan', 24)");           

In the example above, we inserted a piece of data with the name Tinywan and age 24 into the resty_user table.

Query the data

In SQLite3, we use the SELECT statement of the SQL statement to query the data. You can query the data by calling the query() method and passing in the corresponding SQL statement.

The following code shows how to query all the data from a resty_user table:

$result = $database->query('SELECT * FROM resty_user');

while ($row = $result->fetchArray()) {
    echo 'ID: ' . $row['id'] . ', username: ' .$row['name'] . ', age: ' . $row['age'] . PHP_EOL;
}           

In the example above, all the data is queried from the resty_user table, and the query results are output by looping through the result set.

ID: 1, username: Tinywan, age: 24
ID: 2, username: Tinywan, age: 24
ID: 3, username: Tinywan, age: 24           

Update the data

In SQLite3, we use the UPDATE statement of the SQL statement to update the data. The data can be updated by calling the exec() method and passing in the corresponding SQL statement.

The following code shows how to update the data in a resty_user table:

$database->exec("UPDATE resty_user SET age = 25 WHERE id = 1");           

In the example above, update the age of the data with ID 1 in the resty_user table to 25.

Deletion of data

In SQLite3, we use the DELETE FROM statement of the SQL statement to delete the data. Data can be deleted by calling the exec() method and passing in the corresponding SQL statement.

The following code shows how to remove data with ID 1 from a resty_user table:

$database->exec('DELETE FROM resty_user WHERE id = 1');           

Close the database connection

After using the SQLite3 database, we should close the connection to the database to free up resources.

The following code shows how to close a connection to an SQLite3 database:

$database->close();           

View the data through the IDE database manager

summary

In this tutorial, you will learn how to extend SQLite for database management via PHP. Learned how to create a database, create tables, insert data, query data, update data, and delete data. SQLite is a powerful yet simple database solution for small projects and rapid development. Hope you found this tutorial helpful!