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
Sample data
Id,FirstName,LastName,School01,Tom,Max,Nursing02,Ann,Fay,Mining03,Joe,Sun,Nursing04,Sue,Fox,Computing05,Ben,Ray,Mining06,Zoe,Cox,Business07,Sam,Ray,Mining08,Dan,Ash,Medicine09,Pat,Lee,Computing10,Kim,Day,Nursing11,Tim,Rex,Computing12,Rob,Ram,Business13,Jan,Fry,Mining14,Jim,Tex,Nursing15,Ben,Kid,Business16,Mia,Chu,Medicine17,Ted,Tao,Computing18,Amy,Day,Business19,Ian,Roy,Nursing20,Liz,Kit,Nursing21,Mat,Tan,Medicine22,Deb,Roy,Medicine23,Ana,Ray,Mining24,Lyn,Poe,Computing25,Amy,Raj,Nursing26,Kim,Ash,Mining27,Bec,Kid,Nursing28,Eva,Fry,Computing29,Eli,Lap,Business30,Sam,Yim,Nursing31,Joe,Hui,Mining32,Liz,Jin,Nursing33,Ric,Kuo,Business34,Pam,Mak,Computing35,Stu,Day,Business36,Tom,Gad,Mining37,Bob,Bee,Business38,Jim,Ots,Business39,Tom,Mag,Business40,Hal,Doe,Mining41,Roy,Kim,Mining42,Vis,Cox,Nursing43,Kay,Aga,Nursing44,Reo,Hui,Nursing45,Bob,Roe,Mining
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"); ?><?phpecho "<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
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":><?phpecho "<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:
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.
No comments:
Post a Comment