Recently, I've been trying to insert visual analytics into a project I've been working on, and I wanted to be able to create graphs from the UI 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 processes the form is 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 to have the user enter the chart type and properties he wants in the html form on the first page, and then have the second page handle the request and print the chart.
I know I posted a lot of code but I've been struggling with this issue for 3 days and any help is welcome!
edit
I added the section defining $_SESSION['LAST_ACTIVITY'] = time(); and $usersid.
The problem is that I can't seem to find a way to get the data from the form and use them to create a chart. When I create
<?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']."],";
}?>
mark inside the drawChart() function and my entire getData.php page turns completely white. I know this is not a good way to explain my problem, but I tried my best. Please ask me for any explanation you need.
Edit 2
After the changes in @Professor Abronsius's answer, I can print the pie chart, unfortunately it looks like this:
When I try to look at the network traffic to find 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" }
This means a pie chart with equal parts should be printed.. Any ideas?
Edit 3
There are no errors in the console when generating the chart, and after adding console.log() on the following lines:
Object.keys( json ).forEach(key=>{
console.log(dataTbl.addRow( [ json[ key ].name, json[ key ].number ] ));
})
The output in the console is as follows (I don't understand if it is 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
Within the layer:wf->0->c && cache, I can see the data that I expect to populate dataTbl, but I have no idea what the rest is...
Thank you in advance!
getData.php PasteBin link to the file
html-form.php PasteBin link to the file
P粉6158866602024-02-18 09:07:48
None of the following has been tested, but may help you find a solution. I recommend not submitting the form the traditional way, but keeping getData.php and HTML/javascript completely separate. getData.php should just run the SQL query and send the data back to the ajax callback, then call drawChart to actually build the chart.
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 slim versions of HTML pages, without external libraries or CSS, for demonstration only:
sssccc
Made minor changes and built a test page, running the application produced: