Working with Data
Tutorials for importing, processing, querying and presenting fictional data with PHP, MySQL, JavaScript and charts.
Importing JSON into MySQL with PHP
Create a MySQL structure, import fictional JSON records with PHP and query the stored data.
Creating a Security Events Table
In this tutorial, you will create a MySQL table to store security event data imported from a JSON...
Importing JSON into MySQL with PHP
This tutorial imports a fictional JSON dataset into MySQL using PHP, PDO, validation and a prepar...
Querying Imported Data with SQL
In this tutorial, you will use SQL queries to analyse the imported data. View All Events Open php...
Building a Security Dashboard Using PHP and MySQL
This standalone example queries fictional security-event data with PDO and presents useful summar...
Data Visualisation with Google Charts
Present processed SQL data as pie, bar and line charts using Google Charts.
Creating Charts with Google Charts
Google Charts is a free JavaScript library that can create professional graphs and charts using y...
Pie Charts from SQL Data
In this tutorial, the chart data will come directly from a MySQL database. The example below uses...
Bar Charts from SQL Data
Bar charts are useful for comparing values between categories. In this tutorial, the number of se...
Line Charts from SQL Data
Line charts are used to display trends and changes over time. In this tutorial, the number of sec...
Working with APIs
Create PHP JSON APIs and retrieve database records through focused endpoints.
Creating a Simple JSON API with PHP
In this tutorial, you will create a simple API using PHP. The API will return JSON data when visi...
Reading API Data with JavaScript
The API returns JSON data when visited in a browser. In this tutorial, you will use JavaScript to...
Creating a Database-Driven API with PHP and MySQL
This tutorial creates a read-only JSON endpoint using PHP, PDO and fictional database records. An...
Creating API Endpoints for Specific Data
Focused endpoints return the smallest useful result for a particular user need. This tutorial add...
Working with JSON Data in JavaScript
Load, interpret and present nested fictional JSON data with JavaScript.
Loading JSON Data with JavaScript
In this tutorial, you will load data from a JSON file and display it on a webpage using JavaScrip...
Displaying JSON Data in a Table
In this tutorial, you will display the device data in an HTML table using JavaScript. Current Pro...
Displaying Nested Event Data from JSON
In this tutorial, you will work with nested JSON data by displaying the security events associate...
Creating a Security Dashboard from JSON Data
In this tutorial, you will create a simple dashboard that summarises the data and highlights impo...
Importing CSV into MySQL with PHP
Build a secure administrator-only workflow that validates fictional CSV data and stores acceptabl...
Understanding CSV Data
CSV means comma-separated values. Each line is a record and each position represents a field. mat...
Designing a MySQL Table from a CSV
Design the destination table from the meaning of the data, not merely its appearance in a spreads...
Creating an Admin-Only CSV Upload Form
A CSV upload changes stored data and should be restricted to administrators. Apply session and ro...
Validating an Uploaded CSV File
Validate the upload before reading its rows. $file = $_FILES["dataset"] ?? null; $errors = []; if...
Reading CSV Rows with fgetcsv
Use PHP’s CSV parser so quoted commas and escaped values are handled correctly. $handle = fopen($...
Validating CSV Rows
Validate every row before storing it. if (count($row) !== 4) { $rowErrors[] = "Row $rowNumber...
Inserting Imported Rows Safely
Prepare the insert once, then execute it for each valid row. $insert = $pdo->prepare( "INSERT...
Reporting CSV Import Results
An administrator needs evidence of what happened, not just “upload complete”. Report useful total...
Building a Complete Admin CSV Import
A complete import combines access control, upload checks, parsing, row validation, prepared state...
Testing an Admin CSV Import
Testing must cover permissions, validation, database effects and feedback. Test Condition Expecte...
Processing and Presenting Imported Data
Query, process and present stored data as meaningful outputs that respond to user needs and succe...
Querying Data for a User Need
A query is meaningful when it answers a user question. Begin with the need, then choose fields, f...
Building a Leaderboard
A leaderboard converts stored results into a ranked, understandable output. SELECT team_name, ...
Calculating Summary Statistics
Statistics reduce a dataset to information users can interpret. SELECT COUNT(*) AS result_count...
Creating Match and Player Views
A detail view lets a user move from a summary to the record needed for a task. Receive a validate...
Creating Charts from Processed SQL Data
A chart is useful only when it makes a comparison or pattern easier to understand. JavaScript is ...
Exporting Processed Data to CSV
CSV export can support download and reuse of a filtered or processed result. It is optional when ...