Updating table based on dropdown list selected value via PHP mysql PDO

Question

I cannot figure this out at all. I am populating my table by getting data from the database and filling up both the dropdown and table. I need to get the dropdown value and once that value is selected, I need to update the table via a different mysql command that gets the data. I am unable to figure out how to get the dropdown list value to change the mysql query and update the table. My code is below.

<?php
session_start();
include '/Header.php';
include '/Footer.php';
include '/Functions.php';
extract($_POST); 

$dbConnection = parse_ini_file("/db_connection.ini");
extract($dbConnection);
$myPdo = new PDO($dsn, $user, $password);
$myPdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

function fill_drp($myPdo) {
    $output = '';
    $query = $myPdo->prepare("SELECT Term, Year FROM Semester");
    $query->execute();

    foreach ($query->fetchall(PDO::FETCH_ASSOC) as $row) {
       $output .= '<option value="' . $row['Year'] . ' ' . $row['Term'] . '">' . $row['Year'] . ' ' . $row['Term'] . '</option>';

        #$year = substr(($row['Year'] . $row['Term']), 2, 3); #need it to be 18W instead to query database
    }
    return $output;
}

function fill_table($myPdo, $selected_term) {
    $output = '';
    $query = $myPdo->prepare("select CourseOffer.CourseCode, 
                            Title, WeeklyHours from CourseOffer join Course 
                            on CourseOffer.CourseCode = Course.CourseCode 
                            where SemesterCode= '" . $selected_term . "'");
    $query->execute();
    foreach ($query->fetchall(PDO::FETCH_ASSOC) as $row) {
        $cc = $row['CourseCode'];
        $output.= "<tr>";
        $output.= "<td>" . $row['CourseCode'] . "</td>";
        $output.= "<td>" . $row['Title'] . "</td>";
        $output.= "<td> " . $row['WeeklyHours'] . "</td>";
        $output.="<td> <input type='checkbox' name='chk[]' value='$cc'" . "</td>";
        $output.= "</tr>";
    }
    return $output;
}
?>


<html>
    <head>
        <title>Online Course Registration</title>
        <meta charset="utf-8" name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
    </head>
    <body>
        <h1 class="text-center">Course Selection</h1>
        <p>Welcome <strong><?php echo $_SESSION['name']; ?></strong> (not you? change user <a href="Login.php">here</a>)</p>
        <p>You have registered <strong>#todo</strong> hours of course(s) for the semester</p>
        <p>You can register <strong>#todo</strong> more hours of course(s) for the semester</p>
        <p>Please note that the courses you have registered will not be displayed in the list</p>


        <form action="CourseSelection.php" method="post">
            <br/>
            <div class="dropdown text-right">
                <select class="dropdown" id="terms" name="terms" >
                    <?php
                    echo fill_drp($myPdo);
                    ?>
                </select> 
            </div> 
            <div id="tableContainer"> 
                <table border="1" class="table" id="table1">
                    <thead class="thead-light">
                        <tr>
                            <th scope="col" >Code</th>
                            <th scope="col"> Course Title</th>
                            <th scope="col">Hours</th>
                            <th  scope="col">Select</th>

                        </tr>
                        <?php
                        if ($_POST["term"] != null) {
                            $_SESSION['term'] = $_POST["term"];#attempt at getting selected dropdown value.
                        }
                        if (isset( $_SESSION['term'])) {
                            $selected_val = substr(($_SESSION['term']), 2, 3);
                            echo fill_table($myPdo, $selected_val); 
                        } else {
                            echo fill_table($myPdo, '17F');#default is 17F but could be 18W 19S etc..
                        }
                        ?>
                </table>
            </div>
            <br/>
            <input type='submit'  class="btn btn-primary"  class='button' name='submit' value='submit'/>
        </form>
    </body>

</html>

Ajax component

I added this script right below my table

   <script>  
        $(document).ready(function(){  
             $('#terms').change(function(){  
                  var term = (($(this).val()).replace(' ','')).substring(2, 5);  
                  //var term = '19W';
                  console.log(term); //shows that it's getting the semesters
                  $.ajax({  
                       url:"RefreshTable.php",  
                       method:"POST",  
                       data:{term:term},  
                       success:function(data){  
                            $('#tableContainer').html(data);  
                       }  
                  });  
             });  
        });  
        </script>

My refreshtable.php has the content below

<?php
$dbConnection = parse_ini_file("/db_connection.ini");
extract($dbConnection);
$myPdo = new PDO($dsn, $user, $password);
$myPdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$output = '';
if (isset($_POST["term"])) {
    if ($_POST["term"] != '') {
        $query = $myPdo->prepare("select CourseOffer.CourseCode, 
                            Title, WeeklyHours from CourseOffer join Course 
                            on CourseOffer.CourseCode = Course.CourseCode 
                            where SemesterCode= '".$_POST["term"]."'");
    } else {
        $query = $myPdo->prepare("select CourseOffer.CourseCode, 
                            Title, WeeklyHours from CourseOffer join Course 
                            on CourseOffer.CourseCode = Course.CourseCode 
                            where SemesterCode= '17F'");
    }
    $query->execute();
    foreach ($query->fetchall(PDO::FETCH_ASSOC) as $row) {
        $cc = $row['CourseCode'];
        $output.= "<tr>";
        $output.= "<td>" . $row['CourseCode'] . "</td>";
        $output.= "<td>" . $row['Title'] . "</td>";
        $output.= "<td> " . $row['WeeklyHours'] . "</td>";
        $output.="<td> <input type='checkbox' name='chk[]' value='" . $row['CourseCode'] . "'" . "</td>";
        #(isset($copies) ? $copies[$i] : '') . "' ></td>";
        $output.= "</tr>";
    }
    echo $output;
}
?>

score 2 · Accepted Answer · answered Nov 25 '18 at 08:18

2

You can do it like they did here, works the same way with an select.
textbox onchange call php function
Call a function with onchange do an Ajax call and call your php script.

answered Nov 25 '18 at 08:18

coud28098

151
6

The problem is that I have no idea how to use ajax. I have spent the last 10hours trying to get it to work with ajax with no avail. My table would not update at all and I'd still get the initial values of the first page load. I will update the code with my ajax attempt. – answerSeeker Nov 25 '18 at 08:22

Joseph_J · Answer 2 · 2018-11-25T08:25:30.390

1

You need to assign a value to your options in the dropdown in the fill_drp() function.

Like so:

$output .= '<option value="' . $row['Year'] . ' ' . $row['Term'] . '">' . $row['Year'] . ' ' . $row['Term'] . '</option>';

edited Nov 25 '18 at 08:25

answered Nov 25 '18 at 08:19

Joseph_J

3,654
2
13
22

updated question but still unable to update table on dropdown change – answerSeeker Nov 25 '18 at 08:33
I see some string manipulation on the `term` variable for the ajax. What do you need the value to be for the query to work properly? – Joseph_J Nov 25 '18 at 08:36
output will be like "2018 Winter" or "2019 Summer" I need them to be 18W or 19S instead which is what the string manipulation does – answerSeeker Nov 25 '18 at 08:37
then I can query the database adding a `where` SemesterCode = 18W etc. – answerSeeker Nov 25 '18 at 08:38
what are you getting back in the console for the success parameter in your ajax? ie `console.log(data);` – Joseph_J Nov 25 '18 at 08:41
When I select a value from the dropdown, I can see the formatted semester code, for example I click on 2017 Summer, It shows on the console 17S – answerSeeker Nov 25 '18 at 08:42
put in a console.log(data) in the success parameter and run again. – Joseph_J Nov 25 '18 at 08:43
I'm not getting anything – answerSeeker Nov 25 '18 at 08:44
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/184207/discussion-between-joseph-j-and-answerseeker). – Joseph_J Nov 25 '18 at 08:45
1

Good luck with the rest of your project. Cheers! – Joseph_J Nov 25 '18 at 09:57

Updating table based on dropdown list selected value via PHP mysql PDO

2 Answers2