Use PHP and SQLite to implement data statistics and reporting

Use PHP and SQLite to implement data statistics and reporting

Introduction:
In modern society, data analysis and reporting have become an important basis for decision-making by a business or organization and individuals. As a common web development language, PHP provides a wealth of tools and libraries to process data. As a small embedded database management system, SQLite performs well in data storage and query. This article will introduce how to use PHP and SQLite to implement data statistics and reporting functions, and attach corresponding code examples.

1. Preparation

1. Installing PHP
   If you have not installed PHP, you can download it from the PHP official website (https://windows.php.net/download/) Download and install the version appropriate for your operating system.
2. Installing SQLite
   The SQLite service is already integrated in PHP, so you don't need to install it separately. When you install PHP, SQLite is automatically installed.
3. Create SQLite database
   In the PHP file directory, create a SQLite database named data.db, which can be achieved through the following code:

<?php
    $db = new SQLite3('data.db');
    $db->exec('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)');
?>

2. Implement data statistics Function

1. Insert data
   Insert some data in the database for subsequent statistics and reports. You can use the following code:

<?php
    $db = new SQLite3('data.db');
    $db->exec("INSERT INTO users (name, age) VALUES ('John', 25)");
    $db->exec("INSERT INTO users (name, age) VALUES ('Jane', 30)");
    $db->exec("INSERT INTO users (name, age) VALUES ('Mark', 35)");
    $db->exec("INSERT INTO users (name, age) VALUES ('Alice', 40)");
?>

2. Statistics
   Statistics of the number of users and average age in the database. You can use the following code:

<?php
    $db = new SQLite3('data.db');
    $result = $db->query("SELECT COUNT(*) AS total_users, AVG(age) AS avg_age FROM users");
    $row = $result->fetchArray();
    
    echo '总用户数：' . $row['total_users'];
    echo '平均年龄：' . $row['avg_age'];
?>

3. Implement the report function

1. Generate report
   Generate a user report showing the user's name and age. You can use the following code:

<?php
    $db = new SQLite3('data.db');
    $result = $db->query("SELECT * FROM users");
    
    echo '<h1 id="用户报告">用户报告</h1>';
    while ($row = $result->fetchArray()) {
        echo '<p>姓名：' . $row['name'] . '，年龄：' . $row['age'] . '</p>';
    }
?>

Note: For better display, you can place the above code in a file named report.php and open the file in the browser to view Report.

2. Export report
   If you want to save the report as a file, you can use the following code:

<?php
    $db = new SQLite3('data.db');
    $result = $db->query("SELECT * FROM users");
    
    $filename = 'report.txt';
    $file = fopen($filename, 'w');
    
    fwrite($file, "用户报告
");
    while ($row = $result->fetchArray()) {
        fwrite($file, "姓名：" . $row['name'] . "，年龄：" . $row['age'] . "
");
    }
    
    fclose($file);
?>

The above code will save the report as a file named report .txt text file.

Conclusion:
Through the above sample code, it is very simple to use PHP and SQLite to implement data statistics and reporting functions. You can further expand and optimize the code according to your own needs. I hope this article has helped you understand how to use PHP and SQLite to process data, statistics and generate reports.

The above is the detailed content of Use PHP and SQLite to implement data statistics and reporting. For more information, please follow other related articles on the PHP Chinese website!