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:
Copy after login
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:
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:
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:
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!