CODEWITHSUNDEEP
phpmysqlpdo
answerSeeker
answerSeeker

Reputation: 2772

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

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;
}
?>

Upvotes: 1

Views: 2047

Answers (2)

Joseph_J
Joseph_J

Reputation: 3669

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

Upvotes: 1

coud28098
coud28098

Reputation: 161

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.

Upvotes: 2

Related Questions