PHP and SQLite: The basics of querying data from a database

PHP and SQLite: The basics of querying data from a database

Introduction:
When developing web applications, you often need to use a database to store and retrieve data. As a popular server-side scripting language, PHP's integration with SQLite database is widely used because it is a lightweight database and does not require a separate server to run.

This article will introduce the basic knowledge of how to use PHP and SQLite to perform database queries. We'll cover some common use cases from connecting to a database, executing a query, getting a result set, and processing the results.

1. Connect to the database:
   To use the SQLite database, we first need to connect to it through PHP code.

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

The above code will create a database file named database.db in the current directory and save the database connection through the $db variable.

2. Execute query:
   Once connected to the database, we can execute SQL query statements to retrieve data.

<?php
$result = $db->query('SELECT * FROM users');
?>

The above code will execute a simple select statement to retrieve all rows and columns from the data table named users. The results of the query will be stored in the $result variable.

3. Get the result set:
   After executing the query, we need to use some methods to get the data from the result set.

<?php
while ($row = $result->fetchArray()) {
    // 处理行数据
}
?>

The above code will iterate through the result set in $result through a while loop. Each time through the loop, the $row variable will contain an associative array or an indexed array of data for one row, depending on the parameters of the fetchArray() method.

4. Processing results:
   For each row of data, we can access specific field data based on its column name or index value.

<?php
while ($row = $result->fetchArray()) {
    echo $row['username']; // 根据列名访问
    echo $row[0]; // 根据索引访问
}
?>

The above code example demonstrates how to access the username field data in the $row variable. Through the column name or index value, we can get the value of a specific field.

5. Full Example:
   Below is a complete example that shows how to connect to the database, execute a query, and output the results as an HTML table.

<?php
$db = new SQLite3('database.db');
$result = $db->query('SELECT * FROM users');
?>

<table>
    <tr>
        <th>Username</th>
        <th>Email</th>
    </tr>
    <?php while ($row = $result->fetchArray()) { ?>
        <tr>
            <td><?php echo $row['username']; ?></td>
            <td><?php echo $row['email']; ?></td>
        </tr>
    <?php } ?>
</table>

The above example will retrieve the user's username and email from the data table named users and output it in the form of an HTML table.

Conclusion:
This article introduces the basics of database query using PHP and SQLite. By connecting to the database, executing queries, obtaining result sets, and processing the results, we can efficiently retrieve and manipulate stored data. Proficient in these basic knowledge will help us better utilize the powerful functions of PHP and SQLite in development.

The above is the detailed content of PHP and SQLite: The basics of querying data from a database. For more information, please follow other related articles on the PHP Chinese website!