如何从下拉列表中获取所选值并在查询中使用它而不单击提交？

Question

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> 

Answer 1

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);

?>