How to Create a Pivot Table in PHP and MySQL
6 min readAug 11, 2023
A pivot table, also known as a cross-tabulation table, is a powerful data summarization tool that helps in analyzing and presenting complex data sets in a more organized and comprehensible format. It allows you to transform rows of data into columns, providing valuable insights by summarizing and aggregating data. In this tutorial, we will explore how to create a pivot table using PHP and MySQL, step by step.
Prerequisites
Before we begin, make sure you have the following prerequisites in place:
- PHP Environment: Ensure you have PHP installed on your system or web server.
- MySQL Database: Set up a MySQL database and have the necessary credentials.
- Dashboard Builder: download and install the dashboard builder from Download Dashboard Builder
Installation
- Place the dashboard builder file in a directory on the web server. e.g.
…/www/yoursite/dashbboardbuilder-v3-FREE/ - Unzip the file dashboard.php using Extract Here option to the root folder of “dashboardbuilder”
Step 1: Database Setup
Assuming you have a MySQL database already set up, let’s consider an example scenario where you have a table named sales containing sales data with the following structure:
Step 2: Connecting to the Database
Launch dashboard settings after establishing a database connection. And select the chart table. In this article we have used the following query to retrieve the data.
SELECT salesrecords.Region, salesrecords.`Item Type`, salesrecords.`Units Sold` FROM salesrecords Where salesrecords.`Units Sold`>9000
Step 3: Insert Pivot Table Query
On the right side, go to the “Fields” tab. A new tab will appear. You’ll see a panel to configure your pivot table fields.
- Row: Select the fields from the drop down list you want to categorize your data by. In our example, select the “Region” fields here.
- Column: Select the fields you want to use as column headers. In this case, we’ll add the “Item Type” field.
- Value: This section is for the numerical data you want to analyze. Select the “Units Sold” field here.
- Calculations: By default, it will display the sum of ‘Unit Sold’ you can select your desired formula that you want to apply. In this case, we’ll select the “Sum”
- Now click the Apply button
SELECT `Region`, SUM(CASE WHEN (`Item Type`='Baby Food') THEN `Units Sold` ELSE 0 END) AS `Baby Food`,SUM(CASE WHEN (`Item Type`='Beverages') THEN `Units Sold` ELSE 0 END) AS `Beverages`,SUM(CASE WHEN (`Item Type`='Cereal') THEN `Units Sold` ELSE 0 END) AS `Cereal`,SUM(CASE WHEN (`Item Type`='Clothes') THEN `Units Sold` ELSE 0 END) AS `Clothes`,SUM(CASE WHEN (`Item Type`='Cosmetics') THEN `Units Sold` ELSE 0 END) AS `Cosmetics`,SUM(CASE WHEN (`Item Type`='Fruits') THEN `Units Sold` ELSE 0 END) AS `Fruits`,SUM(CASE WHEN (`Item Type`='Household') THEN `Units Sold` ELSE 0 END) AS `Household`,SUM(CASE WHEN (`Item Type`='Meat') THEN `Units Sold` ELSE 0 END) AS `Meat`,SUM(CASE WHEN (`Item Type`='Office Supplies') THEN `Units Sold` ELSE 0 END) AS `Office Supplies`,SUM(CASE WHEN (`Item Type`='Personal Care') THEN `Units Sold` ELSE 0 END) AS `Personal Care`,SUM(CASE WHEN (`Item Type`='Snacks') THEN `Units Sold` ELSE 0 END) AS `Snacks`,SUM(CASE WHEN (`Item Type`='Vegetables') THEN `Units Sold` ELSE 0 END) AS `Vegetables` FROM salesrecords GROUP BY `Region`;Step 4: Displaying the Pivot Table
Now that we have the pivot table data, let’s display it in an organized format on a web page:
<?php
ob_start();
/**
* DashboardBuilder
*
* @author Diginix Technologies www.diginixtech.com
* Support <support@dashboardbuider.net> - https://www.dashboardbuilder.net
* @copyright (C) 2016-2023 Dashboardbuilder.net
* @version 6.3
* @license: This code is under MIT license, you can find the complete information about the license here: https://dashboardbuilder.net/code-license
*/
$_SESSION["DF"]="";
$_SESSION["NF0"]="";
$_SESSION["NF"]="";
include("../inc/dashboard_dist.php"); // copy this file to inc folder
?>
<!DOCTYPE html>
<html lang="en-us" dir="ltr">
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<script src="https://cdn.jsdelivr.net/gh/DashboardBuilder/cdn@master/v55/dashboard.min.js"></script> <!-- copy this file to assets/js folder -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/DashboardBuilder/cdn@master/v55/bootstrap.min.css"> <!-- Bootstrap 5 CSS file, change the path accordingly -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/DashboardBuilder/cdn@master/v55/font-awesome.min.css"> <!-- Font Awesome CSS file, change the path accordingly -->
<style>
@media screen and (min-width: 960px) {
.id0 {position:absolute; top:26px;}
}
.card-header {line-height:0.7em;}
#number {font-size:32px; font-weight:bold;text-align:center;margin-top:-10px;}
#number_legand {font-size:11px; text-align:center;}
.panel-body { box-shadow: 5px 5px 35px #e0e0e0;color:#787b80;}
.page-header {margin-top:-30px;}.dropdown-toggle{font-size:12px;line-height:12px;}
.selectoption {font-size:12px !important;}
.bs-searchbox > input {
font-size: 12px;
height:26px;
}
</style>
</head>
<body>
<?php
// for chart #1
$data = new dashboardbuilder();
$data->type[0]= "charttable";
$data->legacy = "";
$data->source = "Database";
$data->rdbms = "mysql";
$data->dbname = "salesreport";
$data->toImage = "Download graph";
$data->zoomin = "Zoom in";
$data->zoomout = "Zoom out";
$data->autoscale = "Reset";
$data->filterlabel = "Filter";
$data->forecastlabel = "Forecast";
$data->filter = "false";
$data->xaxisSQL[0]= "SELECT * FROM salesrecords LIMIT 10;";
$data->xaxisCol[0]= "Region";
$data->xsort[0]= "";
$data->xmodel[0]= "";
$data->forecast[0]= "";
$data->yaxisSQL[0]= "SELECT * FROM salesrecords LIMIT 10;";
$data->yaxisCol[0]= "Region";
$data->ysort[0]= "";
$data->ymodel[0]= "";
$data->sql[0] = "SELECT `Region`, SUM(CASE WHEN (`Item Type`='Baby Food') THEN `Units Sold` ELSE 0 END) AS `Baby Food`,SUM(CASE WHEN (`Item Type`='Beverages') THEN `Units Sold` ELSE 0 END) AS `Beverages`,SUM(CASE WHEN (`Item Type`='Cereal') THEN `Units Sold` ELSE 0 END) AS `Cereal`,SUM(CASE WHEN (`Item Type`='Clothes') THEN `Units Sold` ELSE 0 END) AS `Clothes`,SUM(CASE WHEN (`Item Type`='Cosmetics') THEN `Units Sold` ELSE 0 END) AS `Cosmetics`,SUM(CASE WHEN (`Item Type`='Fruits') THEN `Units Sold` ELSE 0 END) AS `Fruits`,SUM(CASE WHEN (`Item Type`='Household') THEN `Units Sold` ELSE 0 END) AS `Household`,SUM(CASE WHEN (`Item Type`='Meat') THEN `Units Sold` ELSE 0 END) AS `Meat`,SUM(CASE WHEN (`Item Type`='Office Supplies') THEN `Units Sold` ELSE 0 END) AS `Office Supplies`,SUM(CASE WHEN (`Item Type`='Personal Care') THEN `Units Sold` ELSE 0 END) AS `Personal Care`,SUM(CASE WHEN (`Item Type`='Snacks') THEN `Units Sold` ELSE 0 END) AS `Snacks`,SUM(CASE WHEN (`Item Type`='Vegetables') THEN `Units Sold` ELSE 0 END) AS `Vegetables` FROM salesrecords GROUP BY `Region`; ";
$data->name = "0";
$data->title = "Charttable Chart";
$data->orientation = "v";
$data->dropdown = "false";
$data->side = "left";
$data->toImage = "Download graph";
$data->zoomin = "Zoom in";
$data->zoomout = "Zoom out";
$data->autoscale = "Reset";
$data->filter = "false";
$data->forecastlabel = "Forecast";
$data->legposition = "";
$data->xaxistitle = "";
$data->yaxistitle = "";
$data->datalabel = "false";
$data->showgrid = "true";
$data->showline = "true";
$data->tablefontsize = "12";
$data->height = "380";
$data->width = "0";
$data->col = "0";
$data->plot = "dynamic";
$data->font_color = "";
$data->bg_color = "";
$data->color[0]= "#8080ff";
$result[0] = $data->result();?>
<div class="container-fluid main-container position-relative">
<div class="col col-md-12 col-lg-12 col-xs-12">
<div class="row my-4">
<div class="col-md-8 col-xs-12 offset-md-2 id0">
<div class="card-default shadow">
<div class="card-body bgcolor">
<span class="d-flex justify-content-start mx-2 font-color">Charttable Chart</span>
<?php echo $result[0];?>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
Conclusion
Creating a pivot table in PHP and MySQL involves retrieving, summarizing, and organizing data to gain insights into various aspects of your dataset. By following the steps outlined in this tutorial, you can build a basic pivot table that can be further customized and enhanced to suit your specific needs. Remember to handle potential security concerns such as SQL injection and ensure proper error handling to make your application robust and secure.
For more details and download the software please visit https://dashboardbuilder.net/pivot-table
