Complete Guide to Data Statistics and Report Generation Using PHP and SQLite

Introduction

In the modern world, data analysis and reporting have become key factors in decision-making for businesses and individuals. PHP, as a widely used web development language, provides powerful data processing capabilities, while SQLite, a lightweight embedded database system, is ideal for small-scale applications. This article will introduce how to use PHP and SQLite together to implement data statistics and report generation, helping developers efficiently manage and present data.

1. Getting Started

1. Installing PHP: First, ensure that PHP is installed on your computer. If not, you can download and install it from the official PHP website.
2. Installing SQLite: SQLite is already integrated within PHP, so there's no need to install it separately. When you install PHP, SQLite will be automatically enabled.
3. Creating SQLite Database: In your PHP directory, create an SQLite database called "data.db". Below is the code to create the database:

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

2. Implementing Data Statistics Functionality

1. Inserting Data: You need to insert some sample data into the database for subsequent statistics and report generation. Here’s the code to insert data:

<?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: You can query the database to get the total number of users and the average age. Here’s how you can do it:

<?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 'Total Users: ' . $row['total_users'];
    echo 'Average Age: ' . $row['avg_age'];
?>

3. Implementing Report Generation

1. Generating Reports: You can generate a simple user report displaying each user’s name and age. Below is the code for generating the report:

<?php
    $db = new SQLite3('data.db');
    $result = $db->query("SELECT * FROM users");
    echo '<h1>User Report</h1>';
    while ($row = $result->fetchArray()) {
        echo '<p>Name: ' . $row['name'] . ', Age: ' . $row['age'] . '</p>';
    }
?>

This code generates an HTML report containing all users' names and ages.

2. Exporting Report: If you want to save the report to a file, you can use the following code to export the report as a .txt file:

<?php
    $db = new SQLite3('data.db');
    $result = $db->query("SELECT * FROM users");
    $filename = 'report.txt';
    $file = fopen($filename, 'w');
    fwrite($file, "User Report\n");
    while ($row = $result->fetchArray()) {
        fwrite($file, "Name: " . $row['name'] . ", Age: " . $row['age'] . "\n");
    }
    fclose($file);
?>

This code will create a text file called report.txt containing all users' names and ages.

Conclusion

Through the code examples provided, you’ve learned how to implement data statistics and report generation with PHP and SQLite. This provides a simple yet powerful tool for managing and presenting data in web development. We hope this article helps you better understand the use of PHP and SQLite together and enhances your development efficiency.