Hi I'm currently working on a google chart with on change drop down that will update the table and pie chart, The setup is we have a list of department and the goal is to sort the data by department. The table works fine but the pie chart only update once. (I use mysql for the database)
//pieChart_Holder.php
<html>
<body onload = "searchq();">
<select name="option" >
<option selected="selected">All</option>
<option>external</option>
<option>internal</option>
</select>
<select id="myselect">
<option value="finance">Finance</option>
<option value="prod_management">Product Management</option>
<option value="HR">Human Resources</option>
<option value="tsi">TSI</option>
<option value="tso">TSO</option>
<option value="corp_plan">Corporate Planning</option>
<option value="sca">SCA</option>
<option value="com_management">Commercial Management</option>
<option value="d_a">Design & Architecture</option>
<option value="sd_fm">SD-FM</option>
<option value="sd_pm">SD-PM</option>
<option value="sale">Sales</option>
<option value="scm">SCM</option>
</select>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type = "text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<script type = "text/javascript">
var drop_down,test;
<?php
session_start();
$connect = mysqli_connect("localhost", "root", "", "trial");
if(isset($_POST['dropVal1_self'])){
$holder1 = $_POST['dropVal1_self'];
if($holder1=="finance")
{
$dept_type='Finance';
$_SESSION["dropdown_survey_query"] = $dept_type;
}
elseif ($holder1=="prod_management"){
$dept_type='Product Management';
$_SESSION["dropdown_survey_query"] = $dept_type;
}
elseif ($holder1=="HR"){
$dept_type='Human Resources';
$_SESSION["dropdown_survey_query"] = $dept_type;
}
elseif ($holder1=="tsi"){
$dept_type='TSI';
$_SESSION["dropdown_survey_query"] = $dept_type;
}
elseif ($holder1=="tso"){
$dept_type='TSO';
$_SESSION["dropdown_survey_query"] = $dept_type;
}
elseif ($holder1=="corp_plan"){
$dept_type='Corporate Planning';
$_SESSION["dropdown_survey_query"] = $dept_type;
}
elseif ($holder1=="sca"){
$dept_type='SCA';
$_SESSION["dropdown_survey_query"] = $dept_type;
}
elseif ($holder1=="com_management"){
$dept_type='Commercial Management';
$_SESSION["dropdown_survey_query"] = $dept_type;
}
elseif ($holder1=="d_a"){
$dept_type='Design & Architecture';
$_SESSION["dropdown_survey_query"] = $dept_type;
}
elseif ($holder1=="sd_fm"){
$dept_type='SD-FM';
$_SESSION["dropdown_survey_query"] = $dept_type;
}
elseif ($holder1=="sd_pm"){
$dept_type='SD-PM';
$_SESSION["dropdown_survey_query"] = $dept_type;
}
elseif ($holder1=="sale"){
$dept_type='Sales';
$_SESSION["dropdown_survey_query"] = $dept_type;
}
elseif ($holder1=="scm"){
$dept_type='SCM';
$_SESSION["dropdown_survey_query"] = $dept_type;
}
}
if (isset($_SESSION["dropdown_survey_query"])){
$dept_type = $_SESSION["dropdown_survey_query"];
$query_Chart = "SELECT rate_desc, count(*) as number FROM internal WHERE date_extract IN (SELECT max(date_extract) FROM internal WHERE survey_dept = '$dept_type') GROUP BY rate_desc";
$result_chart = mysqli_query($connect, $query_Chart);
}
else {
$dept_type = 'Finance';
$query_Chart = "SELECT rate_desc, count(*) as number FROM internal WHERE date_extract IN (SELECT max(date_extract) FROM internal WHERE survey_dept = '$dept_type') GROUP BY rate_desc";
$result_chart = mysqli_query($connect, $query_Chart);
}
?>
//Chart
google.charts.load('current', {'packages':['corechart']});
function drawChart()
{
var data = google.visualization.arrayToDataTable([
['rate_desc', 'Number'],
<?php
while($row = mysqli_fetch_array($result_chart))
{
echo "['".$row["rate_desc"]."', ".$row["number"]."],";
}
?>
]);
var options = {
title: 'chart',
is3D:true,
slices: {
0: { color: 'red' },
1: { color: 'yellow' },
2: { color: 'green' },
}
//pieHole: 0.4,
};
var chart = new google.visualization.PieChart(document.getElementById('piechart'));
chart.draw(data, options);
}
function searchq(){
var searchTxt = $("input[name='search']").val();
$.post("s5_filter.php",{searchVal:searchTxt}, function(output) {
$("#output").html(output)
});
}
$( "select" )
.change(function (drop_down,test) {
drop_down = $("select option:selected").val();
$.post("s5_filter.php",{dropVal:drop_down}, function(output) {
$("#output").html(output)
// drawChart();
});
test = $( "#myselect" ).val();
$.post("s5_filter.php",{dropVal1:test}, function(output){
$("#output").html(output)
drawChart();
});
var test1 = $( "#myselect" ).val();
$.post("pieChart_Holder.php",{dropVal1_self:test1}, function(piechart){
});
})
.change();
</script>
<form action="" method = "POST">
<input type="text" name="search" onkeyup= "searchq();" />
</form>
<div id = "output">
</div>
<div style="width:900px;">
<br />
<div id="piechart" style="width: 900px; height: 500px;"></div>
</div>
</head>
</body>
</html>
s5_filter.php this is where it passes it values
//s5_filter.php
<style type="text/css">
.highlight { background-color: yellow; }
</style>
<?php
session_start();
function testing_sql($conn,$output) {
if(isset($_POST['searchVal'])){
$searchq = $_POST['searchVal']or die("");
$searchq = preg_replace("#[^0-9a-z]#i","",$searchq);
}
else{
$searchq='';
}
if(isset($_SESSION["dropdown_query1"])){
$holder = $_SESSION["dropdown_query1"]or die("");
}
else{
$holder ='';
}
if(isset($_SESSION["dropdown_survey_query"])){
$dept_holder = $_SESSION["dropdown_survey_query"];
}
else{
$dept_holder ='Finance';
}
echo "$holder";
if ($holder=='internal'){
if ($searchq==''){
$query= "SELECT * FROM internal WHERE survey_dept = '$dept_holder' AND survey_type = '$holder'";
}
else {
echo "internal";
$query= "SELECT * FROM internal WHERE survey_type = '$holder' AND survey_dept ='$dept_holder' AND (RespondID LIKE '%$searchq%' OR CollectorID LIKE '%$searchq%' OR startDate LIKE '%$searchq%' OR endDate LIKE '%$searchq%'OR IPAddress LIKE '%$searchq%' OR email LIKE '%$searchq%'OR firstName LIKE '%$searchq%' OR lastName LIKE '%$searchq%'OR customData LIKE '%$searchq%' OR recentTrans LIKE '%$searchq%'OR others LIKE '%$searchq%' OR rate LIKE '%$searchq%'OR explanation LIKE '%$searchq%' OR suggestions LIKE '%$searchq%'OR empName LIKE '%$searchq%' OR empId LIKE '%$searchq%')";
}
}
else if ($holder=='external'){
if ($searchq==''){
$query= "SELECT * FROM internal WHERE survey_dept = '$dept_holder' AND survey_type = '$holder'";
}
else {
echo "external";
$query= "SELECT * FROM internal WHERE survey_type = '$holder' AND survey_dept ='$dept_holder' AND (RespondID LIKE '%$searchq%' OR CollectorID LIKE '%$searchq%' OR startDate LIKE '%$searchq%' OR endDate LIKE '%$searchq%'OR IPAddress LIKE '%$searchq%' OR email LIKE '%$searchq%'OR firstName LIKE '%$searchq%' OR lastName LIKE '%$searchq%'OR customData LIKE '%$searchq%' OR recentTrans LIKE '%$searchq%'OR others LIKE '%$searchq%' OR rate LIKE '%$searchq%'OR explanation LIKE '%$searchq%' OR suggestions LIKE '%$searchq%'OR empName LIKE '%$searchq%' OR empId LIKE '%$searchq%')";
}
}
else{
if ($searchq==''){
echo"working else";
$query= "SELECT * FROM internal WHERE survey_dept = '$dept_holder'";
}
else{
echo"working else";
$query= "SELECT * FROM internal WHERE survey_dept = '$dept_holder' AND (RespondID LIKE '%$searchq%' OR CollectorID LIKE '%$searchq%' OR startDate LIKE '%$searchq%' OR endDate LIKE '%$searchq%'OR IPAddress LIKE '%$searchq%' OR email LIKE '%$searchq%'OR firstName LIKE '%$searchq%' OR lastName LIKE '%$searchq%'OR customData LIKE '%$searchq%' OR recentTrans LIKE '%$searchq%'OR others LIKE '%$searchq%' OR rate LIKE '%$searchq%'OR explanation LIKE '%$searchq%' OR suggestions LIKE '%$searchq%'OR empName LIKE '%$searchq%' OR empId LIKE '%$searchq%' OR survey_type LIKE '%$searchq%')";
}
}
//$query= "SELECT * FROM internal WHERE RespondID LIKE '%$searchq%' OR CollectorID LIKE '%$searchq%' OR startDate LIKE '%$searchq%' OR endDate LIKE '%$searchq%'OR IPAddress LIKE '%$searchq%' OR email LIKE '%$searchq%'OR firstName LIKE '%$searchq%' OR lastName LIKE '%$searchq%'OR customData LIKE '%$searchq%' OR recentTrans LIKE '%$searchq%'OR others LIKE '%$searchq%' OR rate LIKE '%$searchq%'OR explanation LIKE '%$searchq%' OR suggestions LIKE '%$searchq%'OR empName LIKE '%$searchq%' OR empId LIKE '%$searchq%' OR survey_dept LIKE '%$searchq%'OR survey_type LIKE '%$searchq%'";
//$query= "SELECT * FROM internal WHERE RespondID LIKE '%$searchq%' OR CollectorID LIKE '%$searchq%' OR startDate LIKE '%$searchq%' OR endDate LIKE '%$searchq%'OR IPAddress LIKE '%$searchq%' OR email LIKE '%$searchq%'OR firstName LIKE '%$searchq%' OR lastName LIKE '%$searchq%'OR customData LIKE '%$searchq%' OR recentTrans LIKE '%$searchq%'OR others LIKE '%$searchq%' OR rate LIKE '%$searchq%'OR explanation LIKE '%$searchq%' OR suggestions LIKE '%$searchq%'OR empName LIKE '%$searchq%' OR empId LIKE '%$searchq%' OR survey_dept LIKE '%$searchq%'OR survey_type LIKE '%$searchq%'";
$result = mysqli_query($conn, $query)or die ("database error");
$count = mysqli_num_rows($result);
//----------------------Search with results---------------------------------------------------------------------
if($count == 0){
$output = 'no results';
}else {
echo "<table border = 1>
<tr>
<th>RespondID</th>
<th>CollectorID</th>
<th>startDate</th>
<th>endDate</th>
<th>IPAddress</th>
<th>email</th>
<th>firstName</th>
<th>lastName</th>
<th>customData</th>
<th>recentTrans</th>
<th>others</th>
<th>rate</th>
<th>rate_desc</th>
<th>explanation</th>
<th>suggestions</th>
<th>empName</th>
<th>empId</th>
<th>survey_type</th>
<th>survey_dept</th>
</tr>";
// output data of each row
while($row = mysqli_fetch_array ($result)) {
echo "<tr>";
echo "<td>" . str_replace($searchq, "<span class=\"highlight\">$searchq</span>",$row["RespondID"]) . "</td>";
echo "<td>" . str_replace($searchq, "<span class=\"highlight\">$searchq</span>",$row["CollectorID"]) . "</td>";
echo "<td>" . str_replace($searchq, "<span class=\"highlight\">$searchq</span>",$row["startDate"]) . "</td>";
echo "<td>" . str_replace($searchq, "<span class=\"highlight\">$searchq</span>",$row["endDate"]) . "</td>";
echo "<td>" . str_replace($searchq, "<span class=\"highlight\">$searchq</span>",$row["IPAddress"]) . "</td>";
echo "<td>" . str_replace($searchq, "<span class=\"highlight\">$searchq</span>",$row["email"]) . "</td>";
echo "<td>" . str_replace($searchq, "<span class=\"highlight\">$searchq</span>",$row["firstName"]) . "</td>";
echo "<td>" . str_replace($searchq, "<span class=\"highlight\">$searchq</span>",$row["lastName"]) . "</td>";
echo "<td>" . str_replace($searchq, "<span class=\"highlight\">$searchq</span>",$row["customData"]) . "</td>";
echo "<td>" . str_replace($searchq, "<span class=\"highlight\">$searchq</span>",$row["recentTrans"]) . "</td>";
echo "<td>" . str_replace($searchq, "<span class=\"highlight\">$searchq</span>",$row["others"]) . "</td>";
echo "<td>" . str_replace($searchq, "<span class=\"highlight\">$searchq</span>",$row["rate"]) . "</td>";
echo "<td>" . str_replace($searchq, "<span class=\"highlight\">$searchq</span>",$row["rate_desc"]) . "</td>";
echo "<td>" . str_replace($searchq, "<span class=\"highlight\">$searchq</span>",$row["explanation"]) . "</td>";
echo "<td>" . str_replace($searchq, "<span class=\"highlight\">$searchq</span>",$row["suggestions"]) . "</td>";
echo "<td>" . str_replace($searchq, "<span class=\"highlight\">$searchq</span>",$row["empName"]) . "</td>";
echo "<td>" . str_replace($searchq, "<span class=\"highlight\">$searchq</span>",$row["empId"]) . "</td>";
echo "<td>" . str_replace($searchq, "<span class=\"highlight\">$searchq</span>",$row["survey_type"]) . "</td>";
echo "<td>" . str_replace($searchq, "<span class=\"highlight\">$searchq</span>",$row["survey_dept"]) . "</td>";
echo "</tr>";
}
echo "</table>";
}
print("$output");
}
$conn = mysqli_connect("127.0.0.1", "root", "", "trial") or die ("WALA");
$output = "";
if(isset($_POST['dropVal1'])){
$holder1 = $_POST['dropVal1'];
if($holder1=="finance")
{
echo("Finance");
$dept_type='Finance';
$_SESSION["dropdown_survey_query"] = $dept_type;
testing_sql($conn,$output);
}
elseif ($holder1=="prod_management"){
echo("Product Management");
$dept_type='Product Management';
$_SESSION["dropdown_survey_query"] = $dept_type;
testing_sql($conn,$output);
}
elseif ($holder1=="HR"){
$dept_type='Human Resources';
$_SESSION["dropdown_survey_query"] = $dept_type;
testing_sql($conn,$output);
}
elseif ($holder1=="tsi"){
$dept_type='TSI';
$_SESSION["dropdown_survey_query"] = $dept_type;
testing_sql($conn,$output);
}
elseif ($holder1=="tso"){
$dept_type='TSO';
$_SESSION["dropdown_survey_query"] = $dept_type;
testing_sql($conn,$output);
}
elseif ($holder1=="corp_plan"){
$dept_type='Corporate Planning';
$_SESSION["dropdown_survey_query"] = $dept_type;
testing_sql($conn,$output);
}
elseif ($holder1=="sca"){
$dept_type='SCA';
$_SESSION["dropdown_survey_query"] = $dept_type;
testing_sql($conn,$output);
}
elseif ($holder1=="com_management"){
$dept_type='Commercial Management';
$_SESSION["dropdown_survey_query"] = $dept_type;
testing_sql($conn,$output);
}
elseif ($holder1=="d_a"){
$dept_type='Design & Architecture';
$_SESSION["dropdown_survey_query"] = $dept_type;
testing_sql($conn,$output);
}
elseif ($holder1=="sd_fm"){
$dept_type='SD-FM';
$_SESSION["dropdown_survey_query"] = $dept_type;
testing_sql($conn,$output);
}
elseif ($holder1=="sd_pm"){
$dept_type='SD-PM';
$_SESSION["dropdown_survey_query"] = $dept_type
testing_sql($conn,$output);
}
elseif ($holder1=="sale"){
$dept_type='Sales';
$_SESSION["dropdown_survey_query"] = $dept_type;
testing_sql($conn,$output);
}
elseif ($holder1=="scm"){
$dept_type='SCM';
$_SESSION["dropdown_survey_query"] = $dept_type;
testing_sql($conn,$output);
}
}
if(isset($_POST['dropVal'])){
///SEARCH POST VALUE
$dropdown = $_POST['dropVal'];
if ($dropdown == 'internal'){
$dropdown_query = 'internal';
$_SESSION["dropdown_query1"] = $dropdown_query;
testing_sql($conn,$output);
}
else if ($dropdown == 'external'){
$dropdown_query = 'external';
$_SESSION["dropdown_query1"] = $dropdown_query;
testing_sql($conn,$output);
}
else{
$dropdown_query = 'All';
$_SESSION["dropdown_query1"] = $dropdown_query;
testing_sql($conn,$output);
}
}
//search---------------------------------------------------------------------------------------------------------------------
if(isset($_POST['searchVal'])){
testing_sql($conn,$output);
}
?>