Dynamic Student Selection: Populating Student Names Based on Class and Arms in a Non-Editable Session/Term Environment

[Olumide]

From the image attached, I have Session/Term not editable, it is the current session/term which is automatic. I have 5 classes and each class have like three or more arms such as 7A, 7B, 7C, 8A, 8B, 8C, etc In my html form for the Student class, I have Class 7, 8, etc which means 7A, 7B, 7C are under 7.

I don't know how to go do it such that when Class is selected, it will populate the Student Name with the students in both arms of Class 7 (i.e all the students in 7A, 7B, 7C, etc).

What I was using is to fetch all the names from the students table.

Here is the html form:

<label for="customer" class="form-label">Year
            <select class="form-control rounded-0" name="year" id="year" required="required">
                <option value=''>- select year -</option>
                <option value='7'>Year 7</option>
                <option value='8'>Year 8</option>
                <option value='9'>Year 9</option>
                <option value='10'>Year 10</option>
                <option value='11'>Year 11</option>
                <option value='12'>Year 12</option>
            </select></label>
</select>


<label>Student
            <select class="form-control rounded-0" name="student" id="student" required="required">
                <!-- student name options for selected year -->
            </select></label>

 

Here are the tables

-- Table structure for table `class`
--

CREATE TABLE `class` (
  `id` int(11) NOT NULL,
  `classname` varchar(45) DEFAULT NULL,
  `levelid` int(11) NOT NULL,
  `year` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Table structure for table `student_class`
--

CREATE TABLE `student_class` (
  `id` bigint(20) NOT NULL,
  `studentid` int(11) DEFAULT NULL,
  `semesterid` int(11) DEFAULT NULL,
  `classid` int(11) DEFAULT NULL,
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- Table structure for table `student`
--

CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `firstName` varchar(50) NOT NULL,
  `lastName` varchar(50) NOT NULL,
  `otherName` varchar(50) NOT NULL,
  `matricNo` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `levelId` int(11) DEFAULT NULL,
  `DOB` date NOT NULL,
  `Phone` varchar(15) NOT NULL,
  `Email` varchar(100) NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `dateCreated` date NOT NULL,
  `leavingdate` date DEFAULT NULL,
  `login_disabled` tinyint(4) NOT NULL DEFAULT 0,
  `last_login` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

 

[Barand]

Sample data would have been useful.

The student_class table is telling you which students arein each class for each term. From the first two selects you know the term and class, so

SELECT s.id
     , s.firstname
     , s.lastname
FROM student_class sc
     JOIN student s ON sc.studentid = s.id
WHERE sc.semesterid = ?
      AND sc.classid = ?

 

[Olumide]

11 minutes ago, Barand said:

Sample data would have been useful.

The student_class table is telling you which students arein each class for each term. From the first two selects you know the term and class, so

SELECT s.id
     , s.firstname
     , s.lastname
FROM student_class sc
     JOIN student s ON sc.studentid = s.id
WHERE sc.semesterid = ?
      AND sc.classid = ?

 

Thank you @Barand Something like this attached image. When the year is selected, it fetch the students in that class.

[Barand]

Sorry, missed the bit about the second select being year and not class. Try

SELECT s.id
     , s.firstname
     , s.lastname
     , c.classname
FROM class c 
     JOIN student_class sc ON sc.classid = c.id 
                           AND semesterid = ?
     JOIN student s ON sc.studentid = s.id
WHERE c.year = ?
ORDER BY lastname;

 

[Olumide]

5 minutes ago, Barand said:

Sorry, missed the bit about the second select being year and not class. Try

SELECT s.id
     , s.firstname
     , s.lastname
     , c.classname
FROM class c 
     JOIN student_class sc ON sc.classid = c.id 
                           AND semesterid = ?
     JOIN student s ON sc.studentid = s.id
WHERE c.year = ?
ORDER BY lastname;

 

Wow! It run successfully on my workbench. You are good sir. Let me incorporate it into my script while I feed you back.

[Olumide]

Thanks @Barand That solved the sql problem, but when I incorporated it, it is like I am having json issue, it is not being populated.

Here is my class_year.php to populate the list...

<?php
include 'database.php';

$year = $_GET['year'] ?? null;

if ($year === null) {
    http_response_code(400);
    echo json_encode(['error' => 'Missing required parameter: year']);
    exit();
}

$sql = "SELECT s.id, s.firstname, s.lastname, c.classname
        FROM class c
        JOIN student_class sc ON sc.classid = c.id AND sc.semesterid = 1
        JOIN student s ON sc.studentid = s.id
        WHERE c.year = ?
        ORDER BY s.lastname";

$stmt = getDatabaseConnection()->prepare($sql);
$stmt->bind_param("i", $year);
$stmt->execute();
$result = $stmt->get_result();

if (!$result) {
    http_response_code(500); // Internal Server Error
    echo json_encode(['error' => 'Database error']);
    exit();
}

$students = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
getDatabaseConnection()->close();

// Set response headers to indicate JSON content
header('Content-Type: application/json');

// Output JSON response
echo json_encode($students);
?>

 

And here is my json

<script>
function updateStudentNames() {
    var selectedYear = document.getElementById("year").value;
    console.log("Selected Year:", selectedYear);

    var studentSelect = document.getElementById("student");

    // Clear previous options
    studentSelect.innerHTML = "";

    // Fetch and add new options based on the selected year
    if (selectedYear !== "") {
        fetch(`class_year.php?year=${selectedYear}`)
            .then(response => {
                if (!response.ok) {
                    throw new Error(`HTTP error! Status: ${response.status}`);
                }
                return response.json();
            })
            .then(data => {
                console.log("Fetched Data:", data);

                if (Array.isArray(data)) {
                    data.forEach(student => {
                        var option = document.createElement("option");
                        option.value = student.id;
                        option.text = `${student.firstName} ${student.lastName}`;
                        studentSelect.add(option);
                    });
                } else {
                    console.error("Invalid data format. Expected an array.");
                }
            })
            .catch(error => {
                console.error('Error fetching students:', error);
            });
    }
}


</script>

 

Html form

 <label for="semester" class="form-label">Current Session/Term</label>
    <input type="text" class="form-control rounded-0" name="semester" id="semester" required="required" value="<?= $current_term ?>" readonly>
</div>
<!-- test -->
<label for="year" class="form-label">Year
    <select class="form-control rounded-0" name="year" id="year" required="required" onchange="updateStudentNames()">
        <option value=''>- select year -</option>
        <option value='7'>Year 7</option>
        <option value='8'>Year 8</option>
        <option value='9'>Year 9</option>
        <option value='10'>Year 10</option>
        <option value='11'>Year 11</option>
        <option value='12'>Year 12</option>
    </select>
</label>

<label for="student" class="form-label">Student
    <select class="form-control rounded-0" name="student" id="student" required="required">
        <!-- student name options for selected year will be dynamically populated here -->
    </select>
</label>

 

[Barand]

1. Why is the required semesterid hard-coded and not passed in the fetch request?
   
2. Do you have data for semesterid = 1 and the selected year?

[Olumide]

1 hour ago, Barand said:

1. Why is the required semesterid hard-coded and not passed in the fetch request?
   
2. Do you have data for semesterid = 1 and the selected year?

Oh! thanks, that was a mistake, I have corrected it and also modified the javascript but still getting this json error:

Selected Year: 7
index.php:105 Current Semester: 2nd Term 2023/2024
index.php:136 Error fetching students: SyntaxError: Unexpected token '<', "
<br />
<b>"... is not valid JSON

Here is my modified javascript:

<script>
function updateStudentNames() {
    var selectedYear = document.getElementById("year").value;
    var currentSemester = document.getElementById("semester").value; // Get the current semester value
    console.log("Selected Year:", selectedYear);
    console.log("Current Semester:", currentSemester);

    var studentSelect = document.getElementById("student");

    // Clear previous options
    studentSelect.innerHTML = "";

    // Fetch and add new options based on the selected year and current semester
    if (selectedYear !== "") {
        fetch(`class_year.php?year=${selectedYear}&semester=${currentSemester}`)
            .then(response => {
                if (!response.ok) {
                    throw new Error(`HTTP error! Status: ${response.status}`);
                }
                return response.json();
            })
            .then(data => {
                console.log("Fetched Data:", data);

                if (Array.isArray(data)) {
                    data.forEach(student => {
                        var option = document.createElement("option");
                        option.value = student.id;
                        option.text = `${student.firstName} ${student.lastName}`;
                        studentSelect.add(option);
                    });
                } else {
                    console.error("Invalid data format. Expected an array.");
                }
            })
            .catch(error => {
                console.error('Error fetching students:', error);
            });
    }
}



</script>

 

[Barand]

The student data is returned correctly in the response but the firstname and lastname are undefined by the time they get into the select. See  page source...

    <select class="form-control rounded-0" name="student" id="student" required="required">
        <option value="718">undefined undefined</option>
        <option value="701">undefined undefined</option>
        <option value="55">undefined undefined</option>
        <option value="43">undefined undefined</option>
        <option value="696">undefined undefined</option>
    </select>

I suspect it's your use of uppercase "N" in firstName and lastName

[Olumide]

2 hours ago, Barand said:

The student data is returned correctly in the response but the firstname and lastname are undefined by the time they get into the select. See  page source...

    <select class="form-control rounded-0" name="student" id="student" required="required">
        <option value="718">undefined undefined</option>
        <option value="701">undefined undefined</option>
        <option value="55">undefined undefined</option>
        <option value="43">undefined undefined</option>
        <option value="696">undefined undefined</option>
    </select>

I suspect it's your use of uppercase "N" in firstName and lastName

I checked but couldn't figured it out and I concatenate the firstname and lastname as full_name and modify it from the javascript but still not working.

[Barand]

Try the jquery version (never could get my head around fetch syntax)

    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script type='text/javascript'>
      
        function updateStudentNames() {
            let selectedYear = $("#year").val()
            let currentSemester = $("#semester").val()
            $("#student").html("")

            // Fetch and add new options based on the selected year and current semester
            if (selectedYear != "" && currentSemester != "") {
                $.get(
                    "class_year.php",
                    {"year":selectedYear, "semester":currentSemester},
                    function(resp) {
                        $.each(resp, function(k, v) {
                            let name = `${v.lastname}, ${v.firstname}`
                            let opt = $("<option>", {value:v.id, text:name})
                            $("#student").append(opt)
                        })
                    },
                    "JSON"
                )
            }
        }
    </script>

 

[Olumide]

8 hours ago, Barand said:

Try the jquery version (never could get my head around fetch syntax)

    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script type='text/javascript'>
      
        function updateStudentNames() {
            let selectedYear = $("#year").val()
            let currentSemester = $("#semester").val()
            $("#student").html("")

            // Fetch and add new options based on the selected year and current semester
            if (selectedYear != "" && currentSemester != "") {
                $.get(
                    "class_year.php",
                    {"year":selectedYear, "semester":currentSemester},
                    function(resp) {
                        $.each(resp, function(k, v) {
                            let name = `${v.lastname}, ${v.firstname}`
                            let opt = $("<option>", {value:v.id, text:name})
                            $("#student").append(opt)
                        })
                    },
                    "JSON"
                )
            }
        }
    </script>

 

Thank you Boss @Barand I will try this and get back. You are always appreciated.