How to create a simple database using PHP and SQLite

How to create a simple database using PHP and SQLite

SQLite is a lightweight database engine. Compared with other relational database engines, it does not require a separate server process. Instead, the database Embed into the application. PHP is a popular server-side scripting language that can be integrated with SQLite. Through PHP's SQLite extension module, we can easily use PHP to create, operate and manage SQLite databases.

This article will introduce how to create a simple database using PHP and SQLite. First, we need to ensure that the PHP environment is configured and the SQLite extension module is installed.

Step 1: Create a database

First, we need to use the SQLite API to create a new database. In PHP, we can use the SQLite function sqlite_open() to open a database connection. If the database does not exist, it will automatically create a new database.

<?php
    $db = sqlite_open('mydatabase.db');
    if (!$db) {
        die('无法创建数据库');
    } else {
        echo '数据库创建成功';
    }
    sqlite_close($db);
?>

In the above code example, we create a database named mydatabase.db and output "Database creation successful" when successful.

Step 2: Create the table

Once we have created the database, we can use the SQLite function sqlite_query() to execute SQL statements. We can use the CREATE TABLE statement to create a table.

<?php
    $db = sqlite_open('mydatabase.db');
    if (!$db) {
        die('无法创建数据库');
    } else {
        $query = sqlite_query($db, 'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)');
        if ($query) {
            echo '表格创建成功';
        } else {
            echo '创建表格失败';
        }
    }
    sqlite_close($db);
?>

In the above code example, we created a table named users and defined three columns: id, name and email. When the execution is successful, "Form created successfully" is output.

Step 3: Insert data

Once we create the table, we can use the INSERT statement to insert data into the table. We can use the SQLite function sqlite_query() to execute the INSERT statement.

<?php
    $db = sqlite_open('mydatabase.db');
    if (!$db) {
        die('无法创建数据库');
    } else {
        $query = sqlite_query($db, "INSERT INTO users (name, email) VALUES ('John', 'john@example.com')");
        if ($query) {
            echo '数据插入成功';
        } else {
            echo '数据插入失败';
        }
    }
    sqlite_close($db);
?>

In the above code example, we inserted a piece of data into the users table. When the execution is successful, "data insertion successful" is output.

Step 4: Query the data

Once we have inserted the data, we can use the SELECT statement to query the data. We can use the SQLite function sqlite_query() to execute the SELECT statement and the sqlite_fetch_array() function to get data from the result set.

<?php
    $db = sqlite_open('mydatabase.db');
    if (!$db) {
        die('无法创建数据库');
    } else {
        $query = sqlite_query($db, 'SELECT * FROM users');
        if ($query) {
            while ($row = sqlite_fetch_array($query)) {
                echo 'ID：' . $row['id'] . '，姓名：' . $row['name'] . '，邮箱：' . $row['email'] . '<br>';
            }
        } else {
            echo '查询数据失败';
        }
    }
    sqlite_close($db);
?>

In the above code example, we use the SELECT statement to query all the data in the users table and display the results row by row.

Summary

Through the above steps, we learned how to use PHP and SQLite to create, operate and query a simple database. SQLite provides a convenient, fast and easy-to-integrate database solution ideal for small projects or embedded applications.

Note: In actual development, for security and performance considerations, we should use prepared statements to execute SQL statements to prevent SQL injection attacks and improve execution efficiency. This article is only for demonstration and introductory use, please use it with caution in actual projects.

The above is the detailed content of How to create a simple database using PHP and SQLite. For more information, please follow other related articles on the PHP Chinese website!