phpmysqlajaxgoogle-visualization
Using Google Charts with an HTML <form> to create a graph
Lately i am trying to insert Visual Analytics on a project ive been working on, and i would like to have the ability to create a graph from a UI that is in an HTML form. The code of my HTML page is as follows:
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title>Charts</title>
<!-- Bootstrap CSS CDN -->
<!-- Our Custom CSS -->
<!-- Font Awesome JS -->
<!--Load the AJAX -->
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
</head>
<body>
<div class="wrapper">
<!-- Sidebar -->
<nav id="sidebar">
// standard sidebar stuff
</nav>
<!-- Page Content -->
<div id="content">
<form action="getData.php" method="POST" id="form">
<table class="" id="chart-searching">
<tr id="type_of_chart_row">
<th>Type of Chart</th>
<td colspan="3" class="">
<select id="type_of_chart" name="charts">
<option value="Pie_chart">Pie chart</option>
<option value="Bar_chart">Bar chart</option>
<option value="Col_chart">Column chart</option>
<option value="Area_chart">Area</option>
<option value="Line_chart">Line chart</option>
</select>
</td>
<th>Select an Attribute</th>
<td colspan="3" id="attribute_row">
<select id="attributes" name="attributes">
<option value="Patient_name">Name</option>
<option value="Patient_id">ID</option>
<option value="Sex">Sex</option>
<option value="Race">Race</option>
<option value="Age">Age</option>
<option value="Comorbidities">Comorbidities</option>
<option value="Email">Email</option>
<option value="eddsscore">EDSS Score</option>
<option value="Phonenum">Phone Number</option>
<option value="onsetsymptoms">Onset Symptoms</option>
<option value="Onsetlocalisation">Onset Localisation</option>
<option value="smoker">Smoker</option>
<option value="Pregnant">Pregnant</option>
<option value="MRIenhancing">MRI Enhanced Lesions</option>
<option value="MRInum">MRI Enhanced Lesions Number</option>
<option value="MRIonsetlocalisation">MRI Onset Localisation</option>
</select>
</td>
</tr>
</table>
<button type="submit" name="makeGraph" value="Create Graph" id="test" onclick="">Create Graph</button>
</form>
<div id="chart_div"></div>
</div>
</div>
<!-- Popper.JS -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.0/umd/popper.min.js" integrity="sha384-cs/chFZiN24E4KMATLdqdvsezGxaGsi4hLGOzlXwp5UZB1LY//20VyM2taTB4QvJ" crossorigin="anonymous"></script>
<!-- Bootstrap JS -->
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.0/js/bootstrap.min.js" integrity="sha384-uefMccjFJAIv6A+rW+L4AHf99KvxDjWSu1z9VI8SKNVmz4sk7buKt/6v9KI65qnm" crossorigin="anonymous"></script>
<script type="text/javascript">//sidebarCollapse
$(document).ready(function() {
$('#sidebarCollapse').on('click', function() {
$('#sidebar').toggleClass('active');
});
});
</script>
</body>
</html>
The file that handles the form is the getData.php
<?php
session_start();
error_reporting(0);
if (isset($_SESSION['LAST_ACTIVITY']) && (time() - $_SESSION['LAST_ACTIVITY'] > 18000)) {
// last request was more than 30 minutes ago
session_unset(); // unset $_SESSION variable for the run-time
session_destroy(); // destroy session data in storage
$scripttimedout = file_get_contents('timeout.js');
echo "<script>" . $scripttimedout . "</script>";
}
$_SESSION['LAST_ACTIVITY'] = time(); // update last activity time stamp?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title>charts</title>
<!-- Bootstrap CSS CDN -->
<!-- Our Custom CSS -->
<!-- Font Awesome JS -->
<!--Load AJAX-->
<script src="/MSR/application/jquery.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">google.load('visualization', '1.0', {'packages':['corechart']});</script>
<script type="text/javascript">
// Load the Visualization API and the corechart package.
// google.charts.load('current', {'packages':['corechart']});
// Set a callback to run when the Google Visualization API is loaded.
google.charts.setOnLoadCallback(drawChart);
// Callback that creates and populates a data table,
// instantiates the pie chart, passes in the data and
// draws it.
function drawChart() {
// Create the data table.
var data = new google.visualization.arrayToDataTable([
['Patient_name','number'],
<?php
$sql = "SELECT Patient_name, count(*) as number FROM patients JOIN MSR ON patients.Patient_id = MSR.NDSnum WHERE Doctor_ID = '$usersid' GROUP BY Patient_name";
$result = $pdo->query($sql);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
while($row = $result->fetch()){
// $array[] = $row;
echo "['".$row['Patient_name']."',".$row['number']."],";
}
?>
]);
// data.addColumn('string', 'Topping');
// data.addColumn('number', 'Slices');
// data.addRows([
// ['Mushrooms', 3],
// ['Onions', 1],
// ['Olives', 1],
// ['Zucchini', 1],
// ['Pepperoni', 2]
// ]);
// Set chart options
var options = {'title':'How Much Pizza I Ate Last Night',
'width':400,
'height':300};
// Instantiate and draw our chart, passing in some options.
var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
</script>
</head>
<body>
<div class="wrapper">
<!-- Sidebar -->
<nav id="sidebar">
</nav>
<!-- Page Content -->
<div id="content">
<div class="collapse navbar-collapse" id="navbarSupportedContent">
<ul class="nav navbar-nav ml-auto">
<li class="navbar-nav">
<a class="nav-link" id="">
<i class="fas fa-user"></i>
Doctor: <u><?php $user_name = $_SESSION['user'];
echo $user_name.$usersid; ?></u>
</a>
<a href="logout.php" onclick="return confirm('Are you sure to logout?');">
<button type="button" id="logoutBtn" class="navbar-btn btn btn-info">
<!-- <i class="fa fa-sign-out"></i> -->
<span>Logout</span>
</button>
</a>
</li>
</ul>
</div>
<div id="chart_div"></div>
<footer>
</footer>
</div>
</div>
<!-- Popper.JS -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.0/umd/popper.min.js" integrity="sha384-cs/chFZiN24E4KMATLdqdvsezGxaGsi4hLGOzlXwp5UZB1LY//20VyM2taTB4QvJ" crossorigin="anonymous"></script>
<!-- Bootstrap JS -->
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.0/js/bootstrap.min.js" integrity="sha384-uefMccjFJAIv6A+rW+L4AHf99KvxDjWSu1z9VI8SKNVmz4sk7buKt/6v9KI65qnm" crossorigin="anonymous"></script>
<script type="text/javascript">//sidebarCollapse
$(document).ready(function() {
$('#sidebarCollapse').on('click', function() {
$('#sidebar').toggleClass('active');
});
});
</script>
</body>
</html>
<?php
//database connection info (this part works fine)
$pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// get data from the form
$createGraph = $_POST['makeGraph'];
$attributes = $_POST['attributes'];
$charts = $_POST['charts'];
if (isset($_POST['makeGraph'])) {
try {
$array = array();
if ($charts == 'Pie_chart'){
if($attributes == 'Patient_name'){
$sql = "SELECT Patient_name, count(*) as number
FROM patients JOIN MSR ON patients.Patient_id = MSR.NDSnum
WHERE Doctor_ID = '$usersid' GROUP BY Patient_name";
$result = $pdo->query($sql);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
while($row = $result->fetch()){
$array[] = $row;
}
$jsonArray = json_encode($array,JSON_PRETTY_PRINT);
echo $jsonArray;
}
// hoping to make something similar in the if statements with the rest of the attributes (email-sex-Race etc...)
} catch (PDOException $e) {
echo"<div class='error'>";
echo $statement . "<br>" . $e->getMessage();
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
echo "</div>";
}
}
?>
My goal is for the user to enter the type of chart and the attribute that he wants in the html form of the first page and then have the second page process the request and print the chart.
I know i posted a lot of code but ive been battling with this issue for 3 days now, any help is welcomed!
EDIT
I added the parts that define the $_SESSION['LAST_ACTIVITY'] = time(); and the $usersid.
The problem is that i cant seem to find a way to get the data from the form and use them to create the graph. When i create the
<?php
$sql = "SELECT Patient_name, count(*) as number FROM patients JOIN MSR ON patients.Patient_id = MSR.NDSnum WHERE Doctor_ID = '$usersid' GROUP BY Patient_name";
$result = $pdo->query($sql);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
while($row = $result->fetch()){
// $array[] = $row;
echo "['".$row['Patient_name']."',".$row['number']."],";
}?>
tag inside of the drawChart() function my whole getData.php page goes completely white. I know this is not a good way of explaining my problem, but im trying my best. Please ask me for any explanation you need.
EDIT No.2
After the changes from @Professor Abronsius answer, i am able to print a pie chart, unfortunately it looks like this:
When i try to look at the network trafic to figure out what the json file looks like, the file looks fine:
0 Object { name: "Name1", number: "1" }
1 Object { name: "Name2", number: "1" }
2 Object { name: "Name3", number: "1" }
3 Object { name: "Name4", number: "1" }
4 Object { name: "Name5", number: "1" }
5 Object { name: "name6", number: "1" }
6 Object { name: "Name7", number: "1" }
7 Object { name: "Name8", number: "1" }
Which means that there should be printed a pieChart with equal parts of the pie.. any ideas?
Edit 3
There are no errors in the console when the chart gets generated, and after adding the console.log() at the lines:
Object.keys( json ).forEach(key=>{
console.log(dataTbl.addRow( [ json[ key ].name, json[ key ].number ] ));
})
the output in the console is the following (which i dont understand if its right or wrong):
Object { cq: null, bf: (2) […], Wf: (1) […], Br: null, cache: [], version: "0.6" }
Br: null
Wf: Array(8) [ {…}, {…}, {…}, … ]
0: Object { c: (2) […] }
c: Array [ {…}, {…} ]
0: Object { v: "Athanasia Moutlia" }
1: Object { v: "1" }
length: 2
<prototype>: Array []
<prototype>: Object { … }
1: Object { c: (2) […] }
2: Object { c: (2) […] }
3: Object { c: (2) […] }
4: Object { c: (2) […] }
5: Object { c: (2) […] }
6: Object { c: (2) […] }
7: Object { c: (2) […] }
length: 8
<prototype>: Array []
bf: Array [ {…}, {…} ]
cache: Array(8) [ (2) […], (2) […], (2) […], … ]
cq: null
version: "0.6"
<prototype>: Object { constructor: gvjs_M(a, b), ca: ca(), "$": $()
, … }
visual_analytics_google.php:203:13
Object { cq: null, bf: (2) […], Wf: (2) […], Br: null, cache: [], version: "0.6" }
visual_analytics_google.php:203:13
Object { cq: null, bf: (2) […], Wf: (3) […], Br: null, cache: [], version: "0.6" }
visual_analytics_google.php:203:13
Object { cq: null, bf: (2) […], Wf: (4) […], Br: null, cache: [], version: "0.6" }
visual_analytics_google.php:203:13
Object { cq: null, bf: (2) […], Wf: (5) […], Br: null, cache: [], version: "0.6" }
visual_analytics_google.php:203:13
Object { cq: null, bf: (2) […], Wf: (6) […], Br: null, cache: [], version: "0.6" }
visual_analytics_google.php:203:13
Object { cq: null, bf: (2) […], Wf: (7) […], Br: null, cache: [], version: "0.6" }
visual_analytics_google.php:203:13
Object { cq: null, bf: (2) […], Wf: (8) […], Br: null, cache: [], version: "0.6" }
visual_analytics_google.php:203:13
inside the layers : wf->0->c && cache i can see the data that i am expecting to populate the dataTbl, but i have no idea what is the rest of it...
Thank you in advance!
PasteBin link for getData.php file
PasteBin link for html-form.php file
Solution
None of what follows is tested at all but might help you come to a solution. Rather than submit the form in a conventional manner I'd suggest separating the getData.php and your HTML/javascript completely. getData.php should just run SQL queries and send data back to the ajax callback which, in turn, calls drawChart to actually build the chart.
getData.php
<?php
session_start();
error_reporting( E_ALL );
if( !isset( $_SESSION['LAST_ACTIVITY'] ) or ( isset( $_SESSION['LAST_ACTIVITY'] ) && ( time() - $_SESSION['LAST_ACTIVITY'] > 18000 ) ) ) {
session_unset();
session_destroy();
exit( header('Location: ?timeout=true') );
}
$_SESSION['LAST_ACTIVITY'] = time();
if( $_SERVER['REQUEST_METHOD']=='POST' && isset(
$_POST['attributes'],
$_POST['charts']
)){
$pdo = new PDO( "mysql:host=$servername;dbname=$dbname", $username, $password );
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$args=array(); # default, empty array if there are no placeholders
$attributes = $_POST['attributes'];
$charts = $_POST['charts'];
/*
create suitable SQL statement and $args array
for each $attribute that you wish to plot on a chart.
Use a Prepared statement to mitigate SQL injection attacks.
*/
switch( $attributes ){
case 'Patient_name':
$sql='SELECT Patient_name, count(*) as number
FROM patients JOIN MSR ON patients.Patient_id = MSR.NDSnum
WHERE Doctor_ID = :userid
GROUP BY Patient_name';
$args=array(
':userid' => $userid
);
break;
/*
test cases...
*/
case 'maps':
$sql='select `county` as `name`, count(*) as `number` from `vwmaps`
group by `county`';
break;
case 'fish':
$sql='select `family` as `name`, count(*) as `number` from `vwfishspecies`
group by `species_order_id`';
break;
/*
etc etc
*/
}
if( isset( $sql, $args ) ){
$stmt=$pdo->prepare( $sql );
if( $stmt ) {
$stmt->execute( $args );
http_response_code( 200 );
exit( json_encode( $stmt->fetchAll( PDO::FETCH_OBJ ) ) );
}
exit( http_response_code( 400 ) );
}
# no sql to run...
http_response_code( 400 );
}
# only allow POST requests
http_response_code( 404 );
?>
And the slim version of the HTML page with no external libraries or css for demo only:
<!DOCTYPE html>
<html lang='en'>
<head>
<meta charset='utf-8' />
<title></title>
<style>
#chart_div h3 span{color:red}
</style>
<script src="https://www.google.com/jsapi"></script>
<script>
google.load('visualization', '1.0', { 'packages':['corechart'] });
</script>
</head>
<body>
<form name='chart-options' method='POST'>
<table>
<tr>
<th>Type of Chart</th>
<td colspan='3'>
<select name='charts'>
<option selected hidden disabled>Please select
<option value='Pie_chart'>Pie chart
<option value='Bar_chart'>Bar chart
<option value='Col_chart'>Column chart
<option value='Area_chart'>Area
<option value='Line_chart'>Line chart
<option value='Scatter'>Scatter Chart
<option value='Histogram'>Histogram Chart
</select>
</td>
<th>Select an Attribute</th>
<td colspan='3'>
<select name='attributes'>
<option selected hidden disabled>Please select
<!-- test values -->
<optgroup label='Development Tests'>
<option value='maps'>Map
<option value='fish'>Fish Species
</optgroup>
<optgroup label='Basic patient details'>
<option value='Patient_name'>Name
<option value='Patient_id'>ID
<option value='Sex'>Sex
<option value='Race'>Race
<option value='Age'>Age
<option value='Email'>Email
<option value='Phonenum'>Phone Number
<option value='smoker'>Smoker
<option value='Pregnant'>Pregnant
</optgroup>
<optgroup label='Extended details'>
<option value='Comorbidities'>Comorbidities
<option value='eddsscore'>EDSS Score
<option value='onsetsymptoms'>Onset Symptoms
<option value='Onsetlocalisation'>Onset Localisation
<option value='MRIenhancing'>MRI Enhanced Lesions
<option value='MRInum'>MRI Enhanced Lesions Number
<option value='MRIonsetlocalisation'>MRI Onset Localisation
</optgroup>
</select>
</td>
</tr>
</table>
<button name='create' type='button' value='Create Graph'>Create Graph</button>
</form>
<div id="chart_div"></div>
<script>
const q=(e,n=document)=>n.querySelector(e);
const getchart=(type)=>{
let container=q('#chart_div');
switch( type ){
case 'Pie_chart':return new google.visualization.PieChart( container );
case 'Bar_chart':return new google.visualization.BarChart( container );
case 'Col_chart':return new google.visualization.ColumnChart( container );
case 'Area_chart':return new google.visualization.AreaChart( container );
case 'Line_chart':return new google.visualization.LineChart( container );
case 'Scatter':return new google.visualization.ScatterChart( container );
case 'Histogram':return new google.visualization.Histogram( container );
default: return false;
}
};
const drawChart=( json, type, attr )=>{
let dataTbl=new google.visualization.DataTable();
dataTbl.addColumn( 'string', 'Name' );
dataTbl.addColumn( 'number', 'Quantity' );
Object.keys( json ).forEach( key => {
/*
Now as long as the json data has 2 columns
you no longer need name the sql values as `name` & `number`
in the query as this will find & use whatever name (within reason)
has been assigned.
*/
let obj=json[ key ];
let [ param, value ]=Object.keys( obj );
dataTbl.addRow( [ obj[ param ], obj[ value ] ] );
})
let args={
'title':attr,
'width':800,
'height':600,
is3D:true,
curveType:'function'
};
let chart=getchart( type );
if( chart ) chart.draw( dataTbl, args );
else alert('Error loading Chart');
}
q('button[name="create"]').addEventListener('click',e=>{
let fd=new FormData( q('form[name="chart-options"]') );
let type=q('select[name="charts"]').value;
let attr=q('select[name="attributes"]').options[q('select[name="attributes"]').options.selectedIndex].text;
fetch( 'getData.php', { method:'post', body:fd } )
.then( r=>r.json() )
.then( json=>{
drawChart( json, type, attr);
})
.catch( err=>{
q('#chart_div').innerHTML=`<h3>Error: Failed to return data for <span>${attr}</span></h3>`
})
})
</script>
</body>
</html>
With minor changes made and test page built, running the app yielded:
