Medhat Elmasry: PHP, SQLite, CSV and CanvasJS
source link: https://blog.medhat.ca/2022/02/php-sqlite-csv-and-canvasjs.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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');?>
Creating database file and add Students table
<?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
<?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?
Render a pie chart of "student count by school"
<?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 they 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
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK