Maydha Mn Maydha Mn - 6 months ago 55
Javascript Question

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

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

Lets clear first the relation between degree_names and courses table:

Your degree_names table would look like:

 id |          degree_names
----+-----------------------------
 1  |        Computer Science
 2  |   Human Resources Management
 3  |    Business Administration

And courses table:

 course_id | degree_id |          courses
-----------+-----------+-------------------------
     1     |     1     |     Operating System
     2     |     1     |     Assembly Language
     3     |     1     |   System Administration
     4     |     2     |        Psychology
     5     |     3     |       Business Law

Why do you have a single value for each check box? Have the value of these check boxes set to the id of the degree_names table:

$out .= '<input id="cb_' .$cnt. '" class="checkChange" type="checkbox" name="check" value="'.$row['id'].'" id="checky" />' .$row['degree']. '<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 */

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

     $('.checkChange :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 = $con->prepare("SELECT courses 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($courses); /* BIND RESULT TO THIS VARIABLE */
    while($stmt->fetch()){ /* FETCH ALL RESULTS */
        echo '<tr>
                  <td>'.$courses.'</td>
              </tr>';
    }
    $stmt->close(); /* CLOSE PREPARED STATEMENT */

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