How to get data from MYSQL child table using checkbox selection?

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>

Tried ajax? And why not table instead of database? And what is the structure of degree_names and courses? — Logan Wayne
– Logan Wayne, Commented May 30, 2016 at 0:59
My bad, the degree_names and courses are actually tables! Sorry for the confusion. As for the structure, "degree_names" just consists of 2 columns with degree names and the number of credit per degree, and "courses" consists of multiple columns like names of all the courses, course id, number of credits, etc. @Logan Wayne — Maydha Mn
– Maydha Mn, Commented May 30, 2016 at 1:05
And they are related through...? degree_names.degree = courses.? — Logan Wayne
– Logan Wayne, Commented May 30, 2016 at 1:06
@Logan Wayne Yes they are related such that degree_names.degree returns the entire list of courses one needs to take for that degree. I'm very new to these programming languages, could you maybe give an example to do this with Ajax? — Maydha Mn
– Maydha Mn, Commented May 30, 2016 at 1:09
Where do you want to display the data after selecting from the check boxes? — Logan Wayne
– Logan Wayne, Commented May 30, 2016 at 1:18

Logan Wayne · Accepted Answer · 2016-05-30 09:13:16Z

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 */

This line is giving me an error: $out .= '<input id="cb_' .$cnt. '" class="checkChange" type="checkbox" name="check" value="'.$row['id'].'" id="checky" />' .$row['degree']. '<br/>'; This is the error I'm getting: Undefined index: degree_id in C:\xampp\htdocs\mysql_login\degree.php on line 83. "degree_id" is the what the column for ids is called in both the tables.
@MaydhaMn - What is the name of your id column for degree_names table? Then after that, change your query to SELECT degree_id, degree FROM degree_names

Collectives™ on Stack Overflow

How to get data from MYSQL child table using checkbox selection?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related