I'm making a chart that will show its result based from the year that was selected from the dropdown. Is it possible to get the variable from the dropdown and use it in query without clicking submit button? I tried this code but didn't work out:
<?php
require '../includes/dbheader.php';
$query = "SELECT DISTINCT DATE_FORMAT(orderdate, '%Y') AS year
FROM prodsoldmonthly
";
$result = mysqli_query($conn, $query);
echo "<select id='selectyear[]' name='selectyear' class='cd-select filter-input'>";
echo "<option class='dropdown' value='' selected>Choose Year</option>";
while($row = mysqli_fetch_assoc($result)) {
echo "<option class='dropdown' value='{$row['year']}'>".htmlspecialchars($row["year"])."</option>";
}
echo "</select>";
?>
<script>
$yearselected = $("#selectyear option:selected").text();
</script>
<!-- Products Sold per Category -- YEAR -->
<?php
include('../includes/dbheader.php');
$query = "SELECT categoryName, qty, DATE_FORMAT(orderdate, '%Y') AS year
FROM prodsoldmonthly WHERE year = '$yearselected'
";
$result = mysqli_query($conn, $query);
?>
<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart()
{
var data = google.visualization.arrayToDataTable([
['categoryName', 'qty'],
<?php
while($row = mysqli_fetch_array($result))
{
echo "['".$row["categoryName"]."', ".$row["qty"]."],";
}
?>
]);
var options = {
title: 'Products Sold Per Category by Year',
is3D:true,
pieHole: 0.4
};
var chart = new google.visualization.PieChart(document.getElementById('piechartyear'));
chart.draw(data, options);
}
</script>
You can use AJAX to launch the request with the year without having to submit the whole page:
function queryProducts(yearselected) {
var xmlhttp = new XMLHttpRequest();
xmlhttp.onreadystatechange = function() {
if (this.readyState == 4 && this.status == 200) {
// this line will be called when the query finishes successfully
document.getElementById("products").innerHTML = this.responseText;
}
};
// set url with parameter
xmlhttp.open("GET", "products.php?yearselected=" + selectedyear, true);
xmlhttp.send();
}
Add an 'onchange' listener to your select to call the function while passing the selected year:
<select id='selectyear[]' name='selectyear' onchange="queryProducts(this.value)" class='cd-select filter-input'>";
Lastly, add a placeholder to display the result of the query:
<div id="products"></div>
EDIT:
For your select statement, put the code in another page like products.php for example and call that page in the AJAX function:
<?php
// products.php
include('../includes/dbheader.php');
// get url parameter
$yearselected = intval($_GET['yearselected']);
$query = "SELECT categoryName, qty, DATE_FORMAT(orderdate, '%Y') AS year
FROM prodsoldmonthly WHERE year = '".$yearselected."'";
$result = mysqli_query($conn, $query);
echo "<table>
<tr>
<th>Category</th>
<th>Qty</th>
<th>Order date</th>
</tr>";
while($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['categoryName'] . "</td>";
echo "<td>" . $row['qty'] . "</td>";
echo "<td>" . $row['orderdate'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysqli_close($conn);
?>