如何使用复选框选择从MYSQL子表中获取数据？

Question

I am having some trouble trying to retrieve data from MYSQL using the options checked off in a checklist.

I have 2 tables in MYSQL, 1st for degree_names - which are outputted automatically as a checklist, and 2nd for courses related to each distinct degree name. Both these tables are relational, i.e. they are connected such that "courses" is the child table of "degree_names".

So my question is... What can I do to change my code so that the options(2 or more) that I check off in the checklist connect to my "degree_names" table and then fetch all the courses related to those degrees from the "courses" table?

Here is my code so far which outputs a checklist of all the degrees directly from the degree_name table

        <?php
                $username = "root";
                $password = "";
                $hostname = "localhost";

                $dbname = "major_degrees";
                $str='';

                // Create connection
                $conn = new mysqli($hostname, $username, $password, $dbname);

                // Check connection
                if ($conn->connect_error) {
                    die("Connection failed: " . $conn->connect_error);
                } 

                $sql = "SELECT degree FROM degree_names";
                $result = $conn->query($sql);

                $out = '';
                $cnt = 0;
                if ($result->num_rows > 0) {

                    // output data of each row from degree_names database as a checklist
                    while($row = $result->fetch_assoc()) {
                        $cnt++;
                        $out .= '<input id="cb_' .$cnt. '" class="checkChange" type="checkbox" name="check" value="ch" id="checky" />' .$row['degree']. '<br/>';

                    }
                    echo $out;  

                } 

        ?>
    </b>
    </br>
    <input class="btn-checkout" type="submit" value="Submit" id="submitBtn" disabled="disabled"/>
    </div>
    </div>


        </form>
        </body>
        <script src="https://code.jquery.com/jquery-2.2.4.min.js"></script>
        <script>
            $('.checkChange').change(function() {
                var count = 0;
                var len = $("[name='check']:checked").length;               

                // keep a counter for how many boxes have been checked
                $('#checkboxes input:checked').each(function() {
                    count++;
                });

                // if 2 boxes are checked off then disable the rest
                if (count == 2) {
                    $('#checkboxes input:not(:checked)').each(function() {
                        $(this).attr("disabled", true);

                    }); 

                } 
                // else keep other options enabled
                else {
                    $('#checkboxes input:not(:checked)').each(function() {
                        $(this).removeAttr('disabled');
                    });                                     
                }   

                //if exactly 2 boxes are checked off then enable the submit button, or else keep is disabled
                if ($(this).is(":checked") && count == 2) {
                        $("#submitBtn").removeAttr("disabled");
                } else {
                        $("#submitBtn").attr("disabled", "disabled");
                }

            });

        </script>

    </html>

Answer 1

Lets clear first the relation between majors and courses table:

Your majors table would look like:

 degree_id | TotalCredits |    degree_name
-----------+--------------+--------------------
     1     |      8       |     Computer Science
     2     |     8.5      |      Mathematics
     3     |      8       |    Music and Culture

And courses table:

 id | course_id | degree_id |            course_name               | credits | pre-requisite | last-offered | status |
----+-----------+-----------+--------------------------------------+---------+---------------+--------------+--------+
 1  |   CSCA08  |     1     | Introduction to Computer Science I   |   0.5   |     CSCA48    |   Fall 2015  |        |
 2  |   CSCA48  |     1     | Introduction to Computer Science II  |   0.5   |     CSCB07    |  Winter 2015 |        |
 3  |   MATA23  |     2     |         Linear Algebra I             |   0.5   |     MATB24    |   Fall 2015  |        |

Why do you have a single value for each check box? Have the value of these check boxes set to the degree_id of the majors table. Change first your query to:

$sql = "SELECT degree_id, degree_name FROM majors";

Then set the value of your check boxes to:

$out .= '<input id="cb_' .$cnt. '" class="checkChange" type="checkbox" name="check" value="'.$row['degree_id'].'" id="checky" />' .$row['degree_name']. '<br/>';

Then lets have an empty table where you want to display the data:

<table id="course_table">
</table>

Then use Ajax to call for the display:

$(document).on("change", ".checkChange", function(){ /* WHEN A CHECK BOX HAS BEEN TICKED */

     var counter = 0;

     /* COUNT ALL THE CHECKED CHECK BOXES */
     $(".checkChange").each(function(){
         if(this.checked){
             ++counter;
         }
     });

     if(counter == 2){ /* IF CHECKED CHECK BOXES REACHED TWO */
         /* DISABLE OTHER UNCHECKED CHECK BOXES */
         $(".checkChange").each(function(){
             if(!this.checked){
                 $(this).prop("disabled", true);
             }
         });
     } else {
         /* ENABLE ALL CHECK BOXES */
         $(".checkChange").prop("disabled", false);
     }

     $("#course_table").empty(); /* EMPTY THE TABLE */

     $('#checkboxes :checked').each(function() { /* CHECK EACH CHECKED CHECK BOXES */

         var degid = $(this).val(); /* GET THE VALUE OF THE CHECKED CHECK BOX */

         $.ajax({ /* CALL AJAX */
             type: 'POST', /* METHOD TO USE TO PASS THE DATA */
             url: 'get.php', /* FILE WHERE TO PROCESS THE DATA */
             data: { 'degid' : degid }, /* DATA TO BE PASSED */
             success: function(result){ /* GET THE RESULT FROM get.php */
                 $("#course_table").append(result); /* ADD THE COURSES RESULT TO THE HTML TABLE */
             }
         }); /* END OF AJAX */

     }); /* END OF CHECKING EACH CHECKED CHECK BOXES */

}); /* END OF IF A CHECK BOX HAS BEEN TICKED */

You might notice that we will process the value in get.php, so lets create this file. Lets use prepared statement:

// INCLUDE YOUR DATABASE CONNECTION
$conn = new mysqli('localhost', 'root', '', 'major_degrees');

// CHECK CONNECTION
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

if(!empty($_POST["degid"])){

    $stmt = $conn->prepare("SELECT course_name FROM courses WHERE degree_id = ?"); /* PREPARE YOUR QUERY */
    $stmt->bind_param("i", $_POST["degid"]); /* BIND THE SUBMITTED DATA TO YOUR QUERY; i STANDS FOR INTEGER */
    $stmt->execute(); /* EXECUTE QUERY */
    $stmt->bind_result($coursename); /* BIND RESULT TO THIS VARIABLE */
    while($stmt->fetch()){ /* FETCH ALL RESULTS */
        echo '<tr>
                  <td>'.$coursename.'</td>
              </tr>';
    }
    $stmt->close(); /* CLOSE PREPARED STATEMENT */

}
/* WHAT YOU ECHO/DISPLAY HERE WILL BE RETURNED TO degree.php */