How to chain dropdown boxes using PHP and MYSQL?

Question

This is my db:

          [tblset]              |     [tblyear]    |    [tbl_coursetype]
ID, setname, setyear, setcours  |   ID, YearName   | course_no, course_desc   
------------------------------  | ---------------- | -----------------------
1      A        1         1     |   1   1st Year   |     1          BSIT
2      B        3         2     |   2   2nd Year   |     2          BSED
                                |   3   3rd Year   |
                                |   4   4th Year   |
                                |   5   5th Year   |

And this is my code:

    <form role="form" action="save_stud.php" method="post">
        <div class="form-group">
        <label>First Name</label>
        <input type="text" name="studfname" class="form-control" required>
        </div>
        <div class="form-group">
        <label>Last Name</label>
        <input type="text" name="studlname" class="form-control" required>
        </div>


        <div class="form-group">
        <label>Student Course</label> 
        <select name="studcourse" class="form-control">
                  <?php

                        // Five steps to PHP database connections:

                        // 1. Create a database connection
                        //      (Use your own servername, username and password if they are different.)
                        //      $connection allows us to keep refering to this connection after it is established
                        $connection = mysql_connect("localhost","root",""); 
                        if (!$connection) {
                            die("Database connection failed: " . mysql_error());
                        }

                        // 2. Select a database to use 
                        $db_select = mysql_select_db("studsystem",$connection);
                        if (!$db_select) {
                            die("Database selection failed: " . mysql_error());
                        }

                    ?>

            <?php
                $result = mysql_query("Select setcours, course_desc 
                    from tblset, tbl_coursetype 
                    where tbl_coursetype.course_no=tblset.setcours 
                    group by setcours", $connection);

                    if (!$result) {
                    die("Database query failed: " . mysql_error());
                    }


                  // 4. Use returned data
                        while ($row = mysql_fetch_array($result)) {  
                            echo "<option value=\"{$row[0]}\">{$row[1]}</option>";  

                        }

            ?>

        </select> 
        </div>


        <div class="form-group">
        <label>Student Year</label> 
        <select name="studyear" class="form-control">

                  <?php
                        // Five steps to PHP database connections:

                        // 1. Create a database connection
                        //      (Use your own servername, username and password if they are different.)
                        //      $connection allows us to keep refering to this connection after it is established
                        $connection = mysql_connect("localhost","root",""); 
                        if (!$connection) {
                            die("Database connection failed: " . mysql_error());
                        }

                        // 2. Select a database to use 
                        $db_select = mysql_select_db("studsystem",$connection);
                        if (!$db_select) {
                            die("Database selection failed: " . mysql_error());
                        }

                    ?>

                <?php
                $result = mysql_query("Select setyear, YearName 
                    from tblset, tblyear
                    where tblyear.ID=tblset.setyear 
                    group by setyear", $connection);

                    if (!$result) {
                    die("Database query failed: " . mysql_error());
                }
                  // 4. Use returned data
                        while ($row = mysql_fetch_array($result)) {  
                            echo "<option value=\"{$row[0]}\">{$row[1]}</option>";  

                        }

            ?>


        </select> 
        </div>

        <div class="form-group">
        <label>Section</label> 
        <select name="studset" class="form-control">

                  <?php
                        // Five steps to PHP database connections:

                        // 1. Create a database connection
                        //      (Use your own servername, username and password if they are different.)
                        //      $connection allows us to keep refering to this connection after it is established
                        $connection = mysql_connect("localhost","root",""); 
                        if (!$connection) {
                            die("Database connection failed: " . mysql_error());
                        }

                        // 2. Select a database to use 
                        $db_select = mysql_select_db("studsystem",$connection);
                        if (!$db_select) {
                            die("Database selection failed: " . mysql_error());
                        }

                    ?>

            <?php
                $result = mysql_query("SELECT * FROM tblset t LIMIT 0,1000", $connection);
                    if (!$result) {
                    die("Database query failed: " . mysql_error());
                }

                  // 4. Use returned data
                        while ($row = mysql_fetch_array($result)) {  
                            echo "<option value=\"{$row[0]}\">{$row[1]}</option>";  

                        }

            ?>


        </select> 
        </div>

-Here are some screenshots.

Now, I am confused because BSIT has only 1st year but when everytime I choose BSIT, the second dropdown will also appear 3rd year, which infact, 3rd year is only intended for BSED.

In short, I want that when everytime I choose a course, it will automatically change the second dropdown menu with its corresponding year level/s.

I am new at php and mysql, and I don't know how to fix this issue. I've been trying to search any code over the internet for almost 1 week but no one's working, maybe I don't know how so I can't trace why it does so.

Can someone help me? Any help will be appreciated. Thanks!

You need to use AJAX for that. Look into [jQuery Ajax()](http://api.jquery.com/jquery.ajax/) method. You need to bind the ajax call that queries db for second dropdown contents to the first dropdown `onChange()` event. — B-and-P, Oct 13 '15 at 07:35
Possible duplicate of [How to pass a php variable from a dropdown to another dropdown using php?](http://stackoverflow.com/questions/33079923/how-to-pass-a-php-variable-from-a-dropdown-to-another-dropdown-using-php) — DocRattie, Oct 13 '15 at 07:35
your last question, http://stackoverflow.com/questions/33079923/how-to-pass-a-php-variable-from-a-dropdown-to-another-dropdown-using-php — Niranjan N Raju, Oct 13 '15 at 07:36
@Qirel Sorry for this. But I posted this again trying to seek for an answer to solve this issue. I did try the codes given yesterday but its not working maybe because, just like I say, I am new at php and mysql, even ajax. — RoyalRyt, Oct 13 '15 at 07:45
@NiranjanNRaju Sorry for this. But I posted this again trying to seek for an answer to solve this issue. I did try the codes given yesterday but its not working maybe because, just like I say, I am new at php and mysql, even ajax. — RoyalRyt, Oct 13 '15 at 07:46
If the answer you accepted for this question yesterday is not working for you... why is it the accepted answer? — Kuya, Oct 13 '15 at 07:59
@RoyalRyt Posting a question twice isn't really going to help you. Take a look at [w3schools'](http://www.w3schools.com/php/php_ajax_database.asp) example. If you can't figure it out from there, then you probably should learn basic PHP and MySQL first. — Qirel, Oct 13 '15 at 08:01
I will. Thanks for the suggestion and sorry for asking this twice. @Qirel — RoyalRyt, Oct 13 '15 at 08:06
Do yourself a favor - **stop using mysql_**. `mysql_` has been [deprecated](http://us3.php.net/manual/en/intro.mysql.php) since PHP 5.5 and removed in PHP 7. Using `mysql_` leaves you wide open to [sql injection attacks](https://www.owasp.org/index.php/SQL_Injection). Soon your code will stop functioning completely and you'll be back asking a [question like this](http://stackoverflow.com/questions/13944956/). Skip `mysqli_` and go straight to learning [pdo_mysql](http://php.net/manual/en/ref.pdo-mysql.php). When your host upgrades to PHP 7, you will be so happy you did. Happy Coding ! — Kuya, Oct 14 '15 at 07:10

score 0 · Answer 1 · answered Oct 13 '15 at 07:40

Take a look into AJAX (asynchronous JavaScript and XML). It will help you solve the automatically change the second dropdown menu with its corresponding year level/s problem you have.

AJAX is a group of Web development techniques used on the client side to create asynchronous Web applications. In short you will be able send and retrieve data from a server asynchronously, in other words you can only reload (and therefore change the content of) one (or more) dropdowns based on a previous user input (without reloading the whole page). Which, as far as I understand, is what you want.

Otherwise you can also implement this without using AJAX, but it will require the whole page to reload.

score 0 · Answer 2 · answered Oct 13 '15 at 07:41

You have to use Javascript to change the contents of the page after the DOM has loaded. You could refresh the page whenever the select has changed, but if I were you, i'd either

Load the combinations into a JS object
Use ajax to fetch them asynchronously.

Your case is fairly simple, so in my humble opinion, the latter would cause a pretty unnecessary request overhead; if you don't have 50+ courses with a lot of year combinations, I would approach it in the first way. Here is a VERY simple version of what I'm talking about, using jQuery for simplicity: http://jsfiddle.net/eng2e9fu/

var courses = {
    1:{years:2},
    2:{ years:3},
    };

$("#course").change(function(){
    var course = $(this).val();
    var options = [];
    for(var i = 1; i <= courses[course].years; i++)
    {
        options.push($("<option/>").attr("value",i).html("Year "+i));
    }

    $("#year").html(options);
});

You wanna focus on generating the `courses` object. You're asking a lot of things here, and you won't get a ready-to-copy-paste-answer here at stackoverflow. The hint is: in the above case, try focusing on generating the "courses" object. The general idea is to manipulate your data into a PHP array and then use [`json_encode`](http://php.net/manual/en/function.json-encode.php) to get it output to valid JSON (which javascript understands). `var courses = =json_encode($array)?>;` — rdiz, Oct 13 '15 at 08:03

Nana Partykar · Answer 3 · 2015-10-14T10:17:40.527

First take a backup of your code. And, use my code. I'm 100% sure, it will work. But, keep patient. And, use this code in very calm manner. Understand each and every line.

dbconnect.php (include this file in each and every page where Database Connection is needed. Why to write each time code for Database Connection)

<?php
$connection = mysql_connect("localhost","root",""); 
if (!$connection) {
    die("Database connection failed: " . mysql_error());
}

$db_select = mysql_select_db("studsystem",$connection);
if (!$db_select) {
    die("Database selection failed: " . mysql_error());
}
?>

Your Modal Page

<?include('dbconnect.php');?>
.
//Your rest of the code
.
<form role="form" action="save_stud.php" method="post">
    <div class="form-group">
        <label>First Name</label>
        <input type="text" name="studfname" class="form-control" required>
    </div>
    <div class="form-group">
        <label>Last Name</label>
        <input type="text" name="studlname" class="form-control" required>
    </div>
    <div class="form-group">
        <label>Student Course</label> 
        <select name="studcourse" class="form-control Courses">
            <option value="">Select Courses</option>
            <?
                $QueryCourses = mysql_query("SELECT tblset.setcours, tbl_coursetype.course_desc 
                FROM tblset, tbl_coursetype WHERE tbl_coursetype.course_no=tblset.setcours", $connection);
                while ($RowCourses = mysql_fetch_array($result))
                {?>
                <option value="<?echo $RowCourses[0];?>"><?echo $RowCourses[1];?></option>
                <?}?>
        </select>
    </div>
    <div class="form-group StudentYearDiv">
        <label>Student Year</label> 
        <select name="studyear" class="form-control">
            <option value="">Select Year</option>
        </select>
    </div>
    <div class="form-group">
        <label>Section</label> 
        <select name="studset" class="form-control">
            <option value="Select Section"></option>
            <?
            $QuerySection=mysql_query("SELECT * FROM tblset LIMIT 0,1000", $connection);
            while($RowQS=mysql_fetch_array($QuerySection))
            {?>
                <option value="<?echo $RowQS[0];?>"><?echo $RowQS[1];?></option>
            <?}?>
        </select>
    </div>
    .
    .
    .
    //Your rest of the code
</form>

Add this code either in your footer. If, after adding it didn't worked, then keep this code in your modal page only.

<script>
    $('.Courses').change(function(){
        var CourseNo=$('.Courses').val();
        $.ajax({url:"Ajax-ShowStudentYear.php?CourseNo="+CourseNo,cache:false,success:function(result){
            $('.StudentYearDiv').html(result);
        }});
    });
</script>

Create one Ajax-ShowStudentYear.php page. (Remember, if you want to change this page name. Then, change in your <script></script> tag too. Both are related)

<?
include('dbconnect.php');
extract($_GET);

$QueryYearNo=mysql_query("SELECT setyear FROM tblset WHERE setcours='$CourseNo'",$connection);
while($RowYN=mysql_fetch_array($QueryYearNo))
{
    $YearNo=$RowYN['setyear'];
}
?>

<label>Student Year</label> 
<select name="studyear" class="form-control">
    <?
    $QueryYear=mysql_query("SELECT ID, YearName FROM tblyear WHERE ID='$YearNo'",$connection);
    while($RowQY=mysql_fetch_array($QueryYear))
    {?>
        <option value="<?echo $RowQY['ID'];?>"><?echo $RowQY['YearName'];?></option>
    <?}?>
</select>

Cheers! Enjoy Coding :) :)

Thank you for helping me sir. I think this will really work but one thing, is there any glitch over the `$QueryCourses` query? I got nothing on the first dropdown box. — RoyalRyt, Oct 14 '15 at 01:11
Hi @RoyalRyt. I didn't changed your query. But, what i observed is your query was wrong. Since 2 columns of different tables you were fetching.. you have to use my way. I've edited my answer. Check it. — Nana Partykar, Oct 14 '15 at 10:18

score 0 · Answer 4 · edited Oct 29 '21 at 16:10

Using the database structure you provided in your question, you can achieve the result by using AJAX.

Start by querying to get your courses...
prepare($sql); $query->execute(); $results = $query->fetchAll(); $totalRows = $query->rowCount(); ?>
Include the function in your <head> tags
function getYears(val) { $.ajax({ type: "POST", url: "get-year.php", data:'id='+val, success: function(data){ $("#year-list").html(data); } }); }
The form
Student Course
Select Course "> Student Year
Select Year
The sql to pull available years... get-year.php
prepare($sql); $query->bindValue(':id', $id, PDO::PARAM_INT); $query->execute(); $results = $query->fetchAll(); $totalRows = $query->rowCount(); ?>
```
 <option value="">Select Year</option>
```
```
 <option value="<?php echo $row["ID"]; ?>"><?php echo $row["YearName"]; ?></option>
```

Of course you can change your connection include line to match what you use & $conn-> to whatever you currently use.

How to chain dropdown boxes using PHP and MYSQL?

4 Answers4