Topic: Getting dropdown value into php for an sql query using chart-js
I am trying to pass the month number from a select statement to a mysql query called by a javascript to redraw a chartjs graph, using the current month as the default graph. The graph charts monthly income payments for each stock.The following code indexchartjs-a.php uses the select statement to select the month which needs to be passed to processchartjs-a.php script and used in WHERE clause of the SQL queryindexchartjs-a.php
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<meta http-equiv="x-ua-compatible" content="ie=edge">
<title>Material Design Bootstrap</title>
<!-- Font Awesome -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css">
<!-- Bootstrap core CSS -->
<link href="css/bootstrap.min.css" rel="stylesheet">
<!-- Material Design Bootstrap -->
<link href="css/mdb.min.css" rel="stylesheet">
<!-- Your custom styles (optional) -->
<link href="css/style.css" rel="stylesheet">
</head>
<body>
<!-- Start your project here-->
<div class="container">
<div class="row">
<!--Jumbotron-->
<div class="jumbotron jumbotron-fluid">
<div class="container">
<h1 class="h1-reponsive mb-4 mt-2 blue-text font-bold">Chart JS Dropdown Dynamic Update</h1>
<p class="lead">update a chartjs chart dynamically using a select statement</p>
</div>
</div>
<!--Jumbotron-->
</div>
<div class="row">
<div class="col">
<p>Select Month</p>
<select class="mdb-select colorful-select dropdown-warning mt-2 hidden-md-down" id="monthSelect" name="month" onchange="createChart(this.value)">
<option value="" disabled>Month</option>
<option value="01" selected>January</option>
<option value="02">February</option>
<option value="03">March</option>
<option value="04">April</option>
<option value="05">May</option>
<option value="06">June</option>
<option value="07">July</option>
<option value="08">August</option>
<option value="09">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>
</select>
</div>
<div class="col">
<div class="chart"><canvas id="mycanvas"></canvas></div>
</div>
</div>
</div>
<!-- /Start your project here-->
<!-- SCRIPTS -->
<!-- JQuery -->
<script type="text/javascript" src="js/jquery-3.2.1.min.js"></script>
<!-- Bootstrap tooltips -->
<script type="text/javascript" src="js/popper.min.js"></script>
<!-- Bootstrap core JavaScript -->
<script type="text/javascript" src="js/bootstrap.min.js"></script>
<!-- MDB core JavaScript -->
<script type="text/javascript" src="js/mdb.min.js"></script>
<!-- CHART.JS -->
<script type="text/javascript" src="js/Chart.min.js"></script>
</body>
<script>
// Material Select Initialization
$(document).ready(function () {
$('.mdb-select').material_select();
});
</script>
<script>
$(document).ready(createChart(12));
function createChart(month){
$.ajax({
method: "GET",
url: "http://localhost/MDBPro/dmv10.20/processchartjs-a.php",
data: month,
success: function(data) {
console.log(data);
var code = [];
var payment = [];
var cumulativepay = [];
var month = [];
for(var i in data) {
code.push("" + data[i].code);
payment.push(data[i].payment);
cumulativepay.push(data[i].cumulativepay);
month.push(data[i].month);
}
var chartdata = {
labels: code,
datasets : [ {
label: 'Dividend Payment',
fillColor: 'rgba(136, 206, 250, 1)',
backgroundColor: 'white',
borderColor: 'rgba(255, 99, 132)',
hoverBackgroundColor: 'rgba(136, 206, 250, 1)',
hoverBorderColor: 'rgba(200, 200, 200, 1)',
data: payment
},
{
label: 'Cumulative Amount',
fillColor: 'rgba(136, 206, 250, 1)',
backgroundColor: 'red',
borderColor: 'rgba(255, 99, 132)',
hoverBackgroundColor: 'rgba(136, 206, 250, 1)',
hoverBorderColor: 'rgba(200, 200, 200, 1)',
data: cumulativepay
},
{
label: 'Month',
fillColor: 'rgba(136, 100, 250, 1)',
backgroundColor: 'pink',
borderColor: 'rgba(255, 99, 132)',
hoverBackgroundColor: 'rgba(136, 206, 250, 1)',
hoverBorderColor: 'rgba(200, 200, 200, 1)',
data: month
}
]
}
var ctx = $("#mycanvas");
var barGraph = new Chart(ctx, {
type: 'bar',
data: chartdata,
options: {
}
});
},
error: function(data) {
console.log(data);
}
});
};
</script>
</html>
<?php
//setting header to json
header('Content-Type: application/json');
include('include/config.php');
if(isset($_POST['month']) || empty($_POST['month'])) {
$month = 'MONTH(NOW())';
} else
{
$month = $_POST(['month']);
}
// query to get data from table
$query = sprintf("
SELECT
CONCAT(DAY(dout.pdate),'-',tout.code) AS code,
CEILING(tout.quantity * dout.dividend / 100)
AS payment,
(SELECT CEILING(SUM(din.dividend * tin.quantity)/100)
FROM
transaction tin
INNER JOIN dividend din ON (tin.code = din.code)
INNER JOIN portfolio pin ON (tin.portfolio_id = pin.id)
INNER JOIN account ain ON (pin.account_id = ain.id)
WHERE
CONCAT(din.pdate,'.',din.id) <= CONCAT(dout.pdate,'.',dout.id)
AND
MONTH(din.pdate) = MONTH(dout.pdate)
AND
din.pdate >= NOW()
AND
din.pdate <= dout.pdate
ORDER BY
MONTH(din.pdate), DAY(din.pdate)
)
AS cumulativepay
FROM transaction tout
INNER JOIN dividend dout ON
(tout.code = dout.code)
INNER JOIN company cout ON
(tout.code = cout.code)
WHERE
dout.pdate >= NOW()
AND
MONTH(dout.pdate) = $month
ORDER BY
MONTH(dout.pdate), DAY(dout.pdate)
");
$statement = $db_con->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
// loop through the returned data
$data = array();
foreach ($result as $row) {
$data[] = $row;
}
// now print the data
print json_encode($data);
?>
Note : I don't want to plot the month value, I am only adding it to the chart as a means of passing the data to the processchartjs-a.php script.
Any help in solving this would be much appreciated
Many Thanks Colin
FREE CONSULTATION
Hire our experts to build a dedicated project. We'll analyze your business requirements, for free.
Open
- User: Free
- Premium support: No
- Technology: General Bootstrap questions
- MDB Version: -
- Device: -
- Browser: -
- OS: -
- Provided sample code: No
- Provided link: No