mysql checkbox values checked using the php loop not working

Question

I am trying to get the value checked in the checkbox from the mysql. I can able to output the checked values. But i cannot make them check in the checkbox. I tried many methods from internet, but none of them seems to work. Maybe i am missing something here. Help me out.

$sql = "SELECT courses.*, enrollments.* 
        FROM enrollments LEFT JOIN courses 
            ON enrollments.courses=courses.course_id 
        WHERE enrollments.enrollment_id = '$id'";
$sql_c = "SELECT * FROM courses";

// query the result
$result = mysqli_query($con, $sql);
$result_c = mysqli_query($con, $sql_c);

// first loop calling using id
while($row = mysqli_fetch_assoc($result)){
    $courses = $row['courses']; // only the selected values

    // Second loop calling all the courses
    while ($rowc = mysqli_fetch_array($result_c)) {
        $courseName = $rowc['course_name']; // all the course names
        $courseId = $rowc['course_id']; // all the course id

        echo "<input type='checkbox' name='courses[]' value='$courseId' id='$courseId' autocomplete='off'>";
        echo "<label for='$courseName'>$courseName</label><br/>";
    } // loop 2
} // loop 1

Here's the output

echo $courses;
// 26, 24, 21, 20

echo $courseId." ";
// 18 19 20 21 22 23 24 26 

How do i make these values checked.

This is what i am looking for

┌─────────┬─────────────────┐
│ [check] │ English         │
│ [ ]     │ French          │
│ [ ]     │ Sanskrit        │
│ [check] │ Arts and Crafts │
│ [check] │ History         │
│ [ ]     │ Chemistry       │
└─────────┴─────────────────┘

Answer 1

Your inner loop will only work the first time. The second time through the outer loop, you'll have fetched all the results from the second query, so the inner loop will stop immediately.

There's no need for two queries and two loops. The first query returns all the courses. You just need to check whether the columns from the enrollment table are checked. But for that to work properly, you need to move enrollments.enrollment_id into the ON clause (see Return default result for IN value regardless).

You also need to change the order of the LEFT JOIN. Since you want all courses, it should be courses LEFT JOIN enrollments.

<?php
$sql = "SELECT courses.*, enrollments.* 
        FROM courses
        LEFT JOIN enrollments 
        ON enrollments.courses=courses.course_id AND enrollments.enrollment_id = '$id'";
$result = mysqli_query($con, $sql);
while ($row = mysqli_fetch_assoc($result)) {
    $courseName = $row['course_name'];
    $courseId = $row['course_id'];
    $checked = $row['enrollment_id'] == NULL ? "" : "checked";
    echo "<input type='checkbox' name='courses[]' value='$courseId' id='$courseId' $checked>";
    echo "<label for='$courseName'>$courseName</label><br/>";
}

Answer 2

@Patrick Q - I did tried lot of method from the internet. Because the code was messy and i kept on commenting and deleting it, i could not able to get the post it here.

@Magnus Eriksson and @IncredibleHat - Sorry for the confusion, i will take care next time.

@Barmar - MANY THANKS. :)

Here's the final working code. I had two major issues Normalization and the not using FK. Because i was testing quick i created the table without setting the FK. I was able to select a checkbox value for one item using @barmar method. So i know i was close. I delete all the tables and created from scratch.

There are lots of answers in the internet for selecting value for the static dropdown, but related to dynamic generated checkbox from another table and selecting the value, i could not find.

1. Checkbox are generated from another table

while($row = mysqli_fetch_assoc($result)){
    $courseName = $row['course_name'];
    $courseId = $row['course_id'];
    echo "<input type='checkbox' name='courses[]' value='$courseId' id='$courseName' autocomplete='off'>";
    echo "<label for='$courseName'>$courseName</label><br/>";
}

2.Editing the checkbox with value selected

while ($row = mysqli_fetch_assoc($result)) {
    $studentid = $row['studentid'];
    $courseId = $row['courseId'];
    $courseName = $row['courseName'];

    $checked = $studentid == NULL ? "" : "checked='checked'";
    echo "<input type='checkbox' name='courses[]' value='$courseId' id='$courseId' $checked autocomplete='off'>";
    echo "<label for='$courseId'>$courseName</label><br/>";
}

Hope this helps someone who searching for the checkbox selected value from dynamic table.