Populate multiple sql queries based on Drop down selection

I'm new to PHP and SQL and would like to know what is the best solution to my issue.

I currently have a PHP page (profile.php) that contains the following three select queries:

$academic = "SELECT * FROM t12019 WHERE `Student ID` = {$id}";
$resultb = mysqli_query($conn,$academic); 

$stoplight = "SELECT * FROM t1stoplight19 WHERE `Student ID` = {$id}";
$resultc = mysqli_query($conn,$stoplight); 

$attendance = "SELECT * FROM t1attendance2019 WHERE `Student ID` = {$id}";
$resultd = mysqli_query($conn,$attendance); 

$id comes from a $_POST selection from the previous page.

the tables for each query is coded in, however, I would like them to be a variable that can be updated from a dropdown selection on the page.

What I imagine, is a selection box on the profiles.php where a user can select one of 'Term 1','Term 2', 'Term 3', 'Term 4'. The tables for each query will then be updated accordingly for the selection.

Any help appreciated.

1 answer

  • answered 2019-04-15 07:38 Sangita Kendre

    On dropdown selection, by javascript submit form and update the respective code. Please refer below sample code.

    HTML code:

    <form action="update.php" id="updateFrm" method="post">
        <input type="hidden" name="id" value="<?php echo $id; ?>">
        <select name="term" id="term" onchange="updateTable()">
            <option value="term_1">Term 1</option>
            <option value="term_2">Term 2</option>
            <option value="term_3">Term 3</option>
            <option value="term_4">Term 4</option>
        </select>
    </form>
    

    Jquery Code:

    <script type="text/javascript">
      function updateTable() {
          document.getElementById("updateFrm").submit();
    
      }
    
    </script>
    

    PHP Code:

    <?php
    
    $id = $_POST['id'];
    $term = $_POST['term'];
    
    if($term == 'term_1'){
      $sql = "UPDATE t12019 ". "SET field1 = $term ".
        "WHERE `Student ID` = $id" ;
      $retval = mysqli_query( $conn, $sql );
    
      if(! $retval ) {
        die('Could not update data: ' . mysqli_error($conn));
      }
      echo "Updated data successfully\n";
    }else if($term == 'term_2'){
      $sql = "UPDATE t1stoplight19 ". "SET field1 = $term ".
        "WHERE `Student ID` = $id" ;
      $retval = mysqli_query( $conn, $sql );
    
      if(! $retval ) {
        die('Could not update data: ' . mysqli_error($conn));
      }
      echo "Updated data successfully\n";
    }.....and so on.
    
    
    ?>