So to sum up my problem and what I'm trying to do:
I'm trying to code an Attendance feature in my PHP Website where the admin first chooses the Class he wants to mark the Attendance for, and then he's taken to a new page with a table that ONLY displays students from THAT class. He enter the date/status of each student and presses Submit, and multiple rows are inserted into the "attendance" table.
Now, this was working perfectly before. The only new addition to this feature was the "Class" feature, now each student has a class (you might call it grade in your region). Before, they didn't.
So naturally, the table's SELECT query went from
$rows2 = $conn->query("SELECT * FROM students")->fetchAll(PDO::FETCH_ASSOC);
to:
$rows2 = $conn->query("SELECT * FROM students WHERE grade_id = $grade")->fetchAll(PDO::FETCH_ASSOC);
This little addition has completely messed up my INSERT query, which works fine when I exclude the "WHERE" clause. Below, I'm attaching code for two pages - one is ChooseClass.php where admin picks the Class he wants to add Attendance for. This page basically sends the chosen class ID to the 2nd page, which is AddAttendance.php.
ChooseClass.php:
<?php include('../db.php');
session_start();
$email = $_SESSION["email"];
$user = $conn->query("SELECT * from admin where email = '$email' ")->fetchAll(PDO::FETCH_ASSOC);
$userID = $user[0]['admin_id'];
$someotherRow = $conn->query("SELECT * FROM grade")->fetchAll(PDO::FETCH_ASSOC);
?>
<!DOCTYPE html>
<html>
<?php include('adminheader.php'); ?>
<body style="background-color: #5e72e4 !important;">
<!-- Sidenav -->
<?php include('adminsidebar.php'); ?>
<div class="main-content" id="panel">
<!-- Topnav -->
<?php include('admintopbar.php'); ?>
<!-- Header -->
<!-- Header -->
<div class="header bg-primary pb-6" style="background-color: #5e72e4 !important;">
<div class="container-fluid">
<div class="header-body">
<div class="row align-items-center py-4">
<div class="col-lg-6 col-7">
<h6 class="h2 text-white d-inline-block mb-0">Attendance Managament</h6>
</div>
</div>
<!-- Card stats -->
<div class="row">
<div class="col-lg-12">
<div class="card">
<!-- Card body -->
<div class="card-header">Choose a Class</div>
<div class="card-body">
<div class="table-responsive" style="overflow-y: scroll; height: 600px;">
<table class="table align-items-center table-dark table-flush">
<thead class="thead-dark">
<tr>
<th scope="col" class="sort" data-sort="name">Class Name</th>
<th scope="col" class="sort" data-sort="status">Action</th>
</tr>
</thead>
<tbody class="list">
<?php
foreach ($someotherRow as $row) { ?>
<tr>
<th scope="row">
<div class="media align-items-center">
<div class="media-body">
<span class="name mb-0 text-sm"><?php echo $row['grade_name']; ?></span>
</div>
</div>
</th>
<td>
<form action="" method="POST">
<a type="submit" href="adminmarkattendance.php?gradeid=<?php echo $row['grade_id']; ?> " class="btn btn-primary">Select</a>
</form>
</td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<!-- Page content -->
<div class="container-fluid mt--6">
<!-- background-color: #5e72e4 !important; height: 63.9vh; -->
</div>
</div>
</body>
</html>
AddAttendance.php:
<?php include('../db.php');
session_start();
$email = $_SESSION["email"];
$user = $conn->query("SELECT * from admin where email = '$email' ")->fetchAll(PDO::FETCH_ASSOC);
$userID = $user[0]['admin_id'];
$grade = 0;
if (isset($_GET['gradeid'])) {
$grade = $_GET['gradeid'];
}
$rows2 = $conn->query("SELECT * FROM students WHERE grade_id = $grade")->fetchAll(PDO::FETCH_ASSOC);
$count = count($rows2);
if (isset($_POST["submit"])) {
for ($i = 0; $i < $count; $i++) {
$student_id = $_POST["id"][$i];
$status = $_POST['options'][$i];
$grade_id = $_POST['grade_id'];
$date = $_POST['attendancedate'];
$date = date('Y-m-d', strtotime($date));
$queryInsert = $conn->prepare("INSERT
into attendance
(
student_id,
grade_id,
date,
status
)
values
(
$student_id,
$grade_id,
'$date',
'$status'
)
");
$queryInsert->execute();
}
echo "<script> location.replace('chooseclass.php'); </script>";
}
?>
<!DOCTYPE html>
<html>
<?php include('adminheader.php'); ?>
<body style="background-color: #5e72e4 !important;">
<!-- Sidenav -->
<?php include('adminsidebar.php'); ?>
<div class="main-content" id="panel">
<!-- Topnav -->
<?php include('admintopbar.php'); ?>
<!-- Header -->
<!-- Header -->
<div class="header bg-primary pb-6" style="background-color: #5e72e4 !important;">
<div class="container-fluid">
<div class="header-body">
<div class="row align-items-center py-4">
<div class="col-lg-6 col-7">
<h6 class="h2 text-white d-inline-block mb-0">Mark Attendance</h6>
</div>
</div>
<!-- Card stats -->
<div class="row">
<div class="col-lg-12">
<form action="adminmarkattendance.php" method="post">
<div class="row">
<div class="col">
<div class="card bg-default shadow">
<div class="card-header bg-transparent border-0">
<div class="form-inline">
<div class="col-lg-6">
<h3 class="text-white mb-0">Registered Students</h3>
</div>
<div class="col-lg-6">
<div class="form-group">
<input style="width: 100%;" class="form-control" name="attendancedate" type="date" required>
</div>
</div>
</div>
</div>
<div class="table-responsive" style="overflow-y: scroll; height: 600px;">
<table class="table align-items-center table-dark table-flush">
<thead class="thead-dark">
<tr>
<th scope="col" class="sort" data-sort="name">Avatar</th>
<th scope="col" class="sort" data-sort="name">Student Name</th>
<th scope="col" class="sort" data-sort="status">Phone Number</th>
<th scope="col" class="sort" data-sort="status">Age</th>
<th scope="col" class="sort" data-sort="status">Gender</th>
<th scope="col" class="sort" data-sort="status">Address</th>
<th scope="col" class="sort" data-sort="status">Action</th>
</tr>
</thead>
<tbody class="list">
<?php
foreach ($rows2 as $row) { ?>
<tr>
<td>
<img src="<?php echo '../profileImages/' . $row['profile_image'] ?>" width="45" height="45" alt="">
<input type="hidden" name="id[]" value="<?php echo $row['student_id']; ?>">
<input type="hidden" name="grade_id" value="<?php echo $grade ?>">
</td>
<th scope="row">
<div class="media align-items-center">
<div class="media-body">
<span class="name mb-0 text-sm"><?php echo $row['fname'] . ' ' . $row['lname']; ?></span>
</div>
</div>
</th>
<td>
<span class="badge badge-dot mr-4">
<span class="status"><?php echo $row['phonenumber']; ?></span>
</span>
</td>
<td>
<span class="badge badge-dot mr-4">
<span class="status"><?php echo $row['age']; ?></span>
</span>
</td>
<td>
<span class="badge badge-dot mr-4">
<span class="status"><?php echo $row['gender']; ?></span>
</span>
</td>
<td>
<span class="badge badge-dot mr-4">
<span class="status"><?php echo $row['address']; ?></span>
</span>
</td>
<td>
<select class="form-control" name="options[]">
<option value="Present" selected>Present</option>
<option value="Absent">Absent</option>
</select>
</td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
</div>
</div>
</div>
<div class="text-center">
<button type="submit" name="submit" style="width: 100%;" class="btn btn-warning">Mark Attendance</button>
</div>
</form>
</div>
</div>
</div>
</div>
<!-- Page content -->
<div class="container-fluid mt--6">
<!-- background-color: #5e72e4 !important; height: 63.9vh; -->
</div>
</div>
</body>
</html>
I've spent 2 hours looking at this code and I can't figure out why the addition of WHERE breaks my code. I've also var_dumped most of the variables, just to check if I was entering some NULL value and I'm not. So kindly look into it and see if you can figure out why data is not being inserted into the database..
