Help with submitting records into a database.

[shuffweb]

Hi everyone, Im looking for a bit of help concerning a php problem ive stumbled upon.

 

Scenario: For my final year project at uni I have to create an online attendance register system using PHP & MySQL which allows a lecturer to;

 

1) login ->

2) view the modules they teach (modules.php)->

3) view a list of timetabled sessions for the chosen module (Labs, Tutorials, Lectures etc...) (session.php)->

4) view a class list for the chosen session, containing a list of all the students timetabled for the session (student_session.php) -->

5)submit a form which inserts the relevant values (student_id, session_id, week_no, attendance[0 or 1]) into a 'student_session' table.

 

Problem:So far I have completed tasks 1-4 without any problems, however I am considerably stuck on task 5.

 

Below is the relevant part of 'student_session.php', which displays a new row for every timetabled student, along with a check box to mark their attendance, and then passes it onto the form handler (submit_attendance.php)

 

<?php	
	include ('./includes/db_connect.php');

				$query  = 
				"SELECT  CONCAT_WS(' ',student.first_name, student.last_name) AS name, student.student_id, student.course_id, module.module_id, module.course_id
				FROM	student, module
				WHERE student.course_id = module.course_id 
				AND module.module_id = '{$_SESSION['module_id']}'
				ORDER BY name ASC ";

				$result = mysql_query($query);
				$num = mysql_num_rows($result);					

				// If it ran OK, display the records.
				if ($num > 0) 

				{ 
					echo 
						"<p>There are <strong>$num</strong> students timetabled to attend this session.</p>\n"

					;


					// Table header.
					echo 
						'<form method="post" action="submit_attendance.php">
						<table class="modules" align="left" cellspacing="0" cellpadding="5" >
							<tr>
								<td class="moduletitle" colspan="2">Tick the box to record a students attendance</td>
							</tr>

							<tr class="hover">
								<td class="module" align="left"><b>Name</b></td>
								<td class="module" align="left"><b>Present</b></td>
							</tr>'
					;
				// Fetch and print all the records.
				while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) 

					{
						echo 
							'<tr class="module" align="left">
								<td class="module" align="left">
									<a href="./student_details.php? student_id=' . $row['student_id'] .'">' . $row['name'] .'</a>
									<input type="hidden" name="student[]"  />
								</td>

								<td class="module" align="left">


										<input type="checkbox" name="attendance[]" value="1" >
											<img src="./images/green_tick.png" width="12px" height="12px"/>
										<br>

								</td>


							</tr>';

					}
						echo 
							'<tr>
								<td>

								<input type="submit" class="submit"  name="submit" value="Submit" />
								<input type="hidden" name="submitted" value="TRUE" />
								</td>
							</tr>
							</table></form><br/>'
						;

				// Free up the resources.						
				mysql_free_result ($result); 

				} 

				else // If it did not run OK.
				{ 
					echo
						'<p class="error">No students are timetabled to attend this session.</p>'
					;
				}


				?>

 

submit_attendance.php - This is where the problem occurs, the desired result would be to enter the values of the $_SESSION['session_id'] and $_SESSION['week_no'] (to represent the teaching session: for later use to generate reports)

and then the names of every student who was present.

However all it does at present is enter one row into the 'student_session' table with null values for all fields except a '1' in the 'attendance' field.

 

 

	<?php
		session_start();

					include ('./includes/db_connect.php');													




				if (isset($_POST['submitted'])) { // Check if the form has been submitted.


				$a = isset( $_POST['attendance'] ) ? 1 : 0;


	if (isset($_POST['student'])) {
		$student = escape_data($_POST['student']);
	} else {
		$student = NULL;
	}

	if (isset($_SESSION['week_id'])) {
		$week = escape_data($_SESSION['week_id']);
	} else {
		$week = NULL;
	}

	if (isset($_SESSION['session_id'])) {
		$sess = escape_data($_SESSION['session_id']);
	} else {
		$sess = NULL;
	}




	}

	if ($a && $student && $week && $sess) { // If everything's OK.

	// Add the student_session to the database.
	$query = "INSERT INTO student_session (attendance, student_id, week_id, session_id) VALUES ('$a', '$student', '$week', '$sess')";
	if ($result = mysql_query ($query)) { // Worked.
		echo '<p>The attendance has been recorded.</p>';
	} else { // If the query did not run OK.
		echo '<p><font color="red">Your submission could not be processed due to a system error.</font></p>'; 
	}

	}



?>

 

I know im nearly there, and im more than certain its just a case of a missing for loop or an array,

sadly im more of a front end developer and I would really appreciate it if some kind PHP/MySQL guru would give me a hint or a push in the right direction.

 

Thanks in advance - Dan

 

 

[GingerRobot]

1.) I suggest you add a key to your form's array containing the student ID - you need to be able to identify which student the attendance mark refers to:

<input type="checkbox" name="attendance['.$row['student_id'].'" value="1" >

 

 

2.) You need to loop through the students. How you do this depends on the result you want. If you would like to only insert those students who were present, you can loop through the $_POST['attendance'] array, using the key for the student's ID.

 

However, if you want to insert ALL students who should have been there with marks for who was present and who was absent, you'll need to return to your code to select the students who were on the register. You'll then have to cycle though this list, and compare it to the $_POST['attendance'] array. If the student ID is in the array, insert a record showing them present. If its not, insert a record showing them absent.

 

3.) As for the sessions - where are they set? You'll need to trace the error back to that.

[shuffweb]

Thanks for the hasty reply, highly appreciated

I have added the key to the array ,

if possible could you demonstrate how I would go about implementing the for loop to retrieve the student_id for each marked student.

 

I tried the following, to no avail...

 

	
foreach($_POST['attendance'] as $a) {

$student = $_REQUEST['student_id'];
}

 

Cheers -Dan

[GingerRobot]

Just as a quick example:

 

<?php
foreach($_POST['attendance'] as $k=> $v) {
    echo 'Student'.$k.' was present';
}
?>

[Barand]

I'd change it around a little and make the value of the checkbox = id

 

echo "<input type=\"checkbox\" name=\"attendance[]\" value=\"{$row['student_id']}\" />";

 

You can then

<?php
$students = join (',', $_POST['attendance']);
$sql = "SELECT name FROM students WHERE student_id IN ($students)";
    // list student names who attended
?>

[shuffweb]

Appreciating your effort guys,

 

Trying GingerRobot's way, using the following code ..

 

if (isset($_POST['submitted'])) { // Check if the form has been submitted.

		foreach($_POST['attendance'] as $k=> $v) {

			$query = "INSERT INTO student_session (student_id) VALUES ('$k')";
			echo 'Student'.$k.' was present<br/>';
			}

	// Add the student_session to the database.

	if ($result = mysql_query ($query)) { // Worked.
		echo '<p>The attendance has been recorded.</p>';
	} else { // If the query did not run OK.
		echo '<p><font color="red">Your submission could not be processed due to a system error.</font></p>'; 
	}



}

 

I have removed the session_id and week_id fields for now just to make sure im getting all the students in their.

The echo statement returns the values of all the students who were marked present.

 

 

However only the last student in the array gets inserted into the database table.

 

 

As you can tell PHP isnt my thing, id probably kick myself if i knew what the problem was, but I am a complete rookie and I would love it if one of you kind souls would be a legend and help me out.

 

Cheers Dan

 

 

 

 

[GingerRobot]

That woudl be because you only execute the query outside of your loop - move the call to mysql_query() inside.