Medhat Elmasry: PHP, SQLite, CSV and CanvasJS

In this article, I will import data from a CSV file into SQLite and render a chart using Canvas.JS. The purpose of this post is to familiarize the reader with importing a CSV file into a database and, subsequently, rendering a pie chart with the data.

Source Code: https://github.com/medhatelmasry/php_chart

The following is assumed:

You have PHP installed on your computer
You have the "extension=sqlite3" setting enabled in your php.ini.

Directory Structure

Inside of a working directory named php_chart, create the following directory structure:

The home page

Inside the root folder, add an index.html file with the following content representing a simple menu system:

<p><a href="./create" >Create school database and students table in SQLite.</a></p>

<p><a href="./import/" >Import seed data from csv file.</a></p>

<p><a href="./chart/" >Display chart with Canvas.JS</a></p>

Sample data

Inside the data folder, create a text file named seed-data.csv with the following content:

Id,FirstName,LastName,School
01,Tom,Max,Nursing
02,Ann,Fay,Mining
03,Joe,Sun,Nursing
04,Sue,Fox,Computing
05,Ben,Ray,Mining
06,Zoe,Cox,Business
07,Sam,Ray,Mining
08,Dan,Ash,Medicine
09,Pat,Lee,Computing
10,Kim,Day,Nursing
11,Tim,Rex,Computing
12,Rob,Ram,Business
13,Jan,Fry,Mining
14,Jim,Tex,Nursing
15,Ben,Kid,Business
16,Mia,Chu,Medicine
17,Ted,Tao,Computing
18,Amy,Day,Business
19,Ian,Roy,Nursing
20,Liz,Kit,Nursing
21,Mat,Tan,Medicine
22,Deb,Roy,Medicine
23,Ana,Ray,Mining
24,Lyn,Poe,Computing
25,Amy,Raj,Nursing
26,Kim,Ash,Mining
27,Bec,Kid,Nursing
28,Eva,Fry,Computing
29,Eli,Lap,Business
30,Sam,Yim,Nursing
31,Joe,Hui,Mining
32,Liz,Jin,Nursing
33,Ric,Kuo,Business
34,Pam,Mak,Computing
35,Stu,Day,Business
36,Tom,Gad,Mining
37,Bob,Bee,Business
38,Jim,Ots,Business
39,Tom,Mag,Business
40,Hal,Doe,Mining
41,Roy,Kim,Mining
42,Vis,Cox,Nursing
43,Kay,Aga,Nursing
44,Reo,Hui,Nursing
45,Bob,Roe,Mining

The database file

In the root of your application, create a PHP file named include_db.php containing only one statement, representing the name of the database file:

<?php
$db = new SQLite3($_SERVER['DOCUMENT_ROOT'] . '/school.db');
?>

The above code creates a school.db SQLite database file if it does not already exist.

Creating database file and add Students table

Add an index.php file inside the create folder with the following content:

<?php include("../include_db.php"); ?>

<?php
echo "<hr /><h3>Create Student Table</h3>";
#===============================================
# Create table
#===============================================

$SQL_create_table = "CREATE TABLE IF NOT EXISTS Students (
StudentId VARCHAR(10) NOT NULL,
FirstName VARCHAR(80),
LastName VARCHAR(80),
School VARCHAR(50),
PRIMARY KEY (StudentId)
);";

echo "<p>$SQL_create_table</p>";

$db->exec($SQL_create_table);

$db->close();
?>

<hr /><a href="/" ><< BACK</a>

What does the above code do?

We first include the include_db.php file so that we have a handle to the db object representing our school.db database file.
Next, we create a Students table in the database by executing a "Create Table ..." SQL statement. The columns in the table match the items in our CSV file.
We close the connection to the database.
There is a link at the bottom that returns us to the home page.

Import CSV file into Students table

Add an index.php file inside the import folder with the following content:

<?php include('../include_db.php'); ?>

<?php

$count = $db->querySingle("SELECT count(*) from Students");

// if empty, insert sample data

if ($count == 0) {

$row = 1;

if (($handle = fopen("../data/seed-data.csv", "r")) !== FALSE) {

$data = fgetcsv($handle, 1000, ",");

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

$num = count($data);

echo "<p> $num fields in line $row: <br /></p>\n";

$row++;

$id = SQLite3::escapeString($data[0]);

$firstName = SQLite3::escapeString($data[1]);

$lastName = SQLite3::escapeString($data[2]);

$school = SQLite3::escapeString($data[3]);

$SQLinsert = "INSERT INTO Students (StudentId, FirstName, LastName, School)";

$SQLinsert .= " VALUES ";

$SQLinsert .= " ('$id', '$firstName', '$lastName', '$school')";

$db->exec($SQLinsert);

$changes = $db->changes();

echo "<p>The INSERT statement added $changes rows</p>";

}

$db->close();

What does the above code do?

We check whether or not there is any data in the Students table.
We load data from the CSV file only if the Students table is empty
The PHP fgetcsv() function is used to load CSV data into an array named $data
Every row of data in the CSV file is inserted into the Students table in the database
We close the connection to the database.

List imported students data

Add an index.php file inside the list folder with the following content:

<?php include("../include_db.php"); ?>

<table border="1":>
<?php

echo "<hr /><h3>List of students</h3>";

$res = $db->query('SELECT * FROM Students');

while ($row = $res->fetchArray()) {
echo "<tr>\n";
echo "<td>{$row['StudentId']}</td>";
echo "<td>{$row['FirstName']}</td>";
echo "<td>{$row['LastName']}</td>";
echo "<td>{$row['School']}</td>";
echo "<tr>\n";
}

?>
</table>
<hr /><a href="/" ><< BACK</a>

What does the above code do?

The above code simply lists the contents of the Students in an html table.

Render a pie chart of "student count by school"

Add an index.php file inside the chart folder with the following content:

<?php include('../include_db.php'); ?>

<?php
$dataPoints = [];

$sql = "SELECT School as school, COUNT(*) as count";
$sql .= " FROM Students GROUP BY School";
$res = $db->query($sql);

while ($row = $res->fetchArray()) {
$arrayItem = array("label" => $row['school'], "y" => $row['count']);
array_push($dataPoints, $arrayItem);
}

$db->close();
?>

<script>
window.onload = function() {
var chart = new CanvasJS.Chart("chartContainer", {
animationEnabled: true,
title: {text: "Students by school"},
data: [{
type: "pie",
yValueFormatString: "#,##0.00\"\"",
indexLabel: "{label} ({y})",
dataPoints: <?php echo json_encode($dataPoints, JSON_NUMERIC_CHECK); ?>
}]
});
chart.render();
}
</script>
<div id="chartContainer" style="height: 370px; width: 100%;"></div>
<script src="https://canvasjs.com/assets/script/canvasjs.min.js"></script>

<hr /><a href="/" ><< BACK</a>

What does the above code do?

A "SELECT ... GROUP BY ..." SQL statement is executed that generates a result-set containing count of students by school.
A two-dimensional array is created with key "label" containing school and key "y" containing count
The bottom part of the above code used the CanvasJS JavaScript library
The type property is set to the type of chart you wish to generate. In this case it is pie.
The dataPoints property contains our data from the $dataPoints two-dimensional array converted into JSON objects

Testing our app

We should be good to go. Let us test our very basic PHP application. We first need to start our server. This is accomplished by running the following command in a terminal window in the root of our application:

php -S localhost:8888

The above starts the PHP development server and listens on port 8888.

Point your browser to http://localhost:8888. You should see the following:

Click on the first "Create school database and students table in SQLite." link to create the database and Students table. This displays the following output with the INSERT statement.

If you look into the root folder of your app, you will see that a school.db file was created.

Click on the "BACK" link to return to the home page.

Next click on the "Import seed data from csv file." link to import CSV data into the Students database table. This displays a series of INSERT statements as shown below:

Return to the home page and click on the third "List data" link. You will see that data was imported into the database:

Return to the home page and click on the last "Display chart with Canvas.JS" link to see our pie chart.

I trust that this simple tutorial will help you visualize data with PHP.

Medhat Elmasry

Wednesday, February 9, 2022

PHP, SQLite, CSV and CanvasJS

Directory Structure

The home page

Sample data

The database file

Creating database file and add Students table

What does the above code do?

Import CSV file into Students table

What does the above code do?

List imported students data

What does the above code do?

Render a pie chart of "student count by school"

What does the above code do?

Testing our app

No comments:

Post a Comment

About Me

Blog Archive