While Loop for student attendance

tha_ripmasta · Apr 3, 2007

Hello

I created a search script to find student attendance from my db. The search works fine and I get the right results.

But what i'm trying to do is get the results to display like this:

Student1 name - Student1 lastname

Month

day1, day2, day3

Student2 name - Student2 lastname

Month

day1, day2, day3

But i can't seem to figure out the right way to loop the results

Can anyone please help me and if you need more info or code ..let me know.

Here is my search code.

SEE POST BELOW

ahundiak · Apr 3, 2007

Yikes. My head always seems to spin around when trying to decipher this sort of code. Try breaking the problem into two pieces.

Start by making yourself an array that looks something like this:

$students = array(
  // First Student
  array(
    'lastName' => ' name1',
    'month' => 'month1',
    'days' => array('day1','day2','day3'),
  ),
  // Second student
 array(
    'lastName' => ' name2',
    'month' => 'month2',
    'days' => array('day1','day2','day3'),
  ),
);

I think you can see that once you have the data properly organized then it becomes much easier to loop through it and generate your html.

Exactly how you make the array from your select statements I'll leave up to you. Cant really follow your logic in any detail. But be assured that taking the time to separate the sql code from the html code will pay off.

tha_ripmasta · Apr 3, 2007

hi ahundiak,

thank you for replying, and sorry i couldn't make the code easier...still learning.

Now you presented me with a solution, but i have no idea on how i should start or implement that with arrays.

can u help me in putting a piece of my code in your array example, so i know how it should be done?

ahundiak · Apr 3, 2007

problem is that I can't really follow your queries. Too much extraneous stuff going on. You want something like this:

$students = array();
$studentQuery = "SELECT whatever FROM students WHERE something";
$studentResult = mysql_query($studentQuery) or die(mysql_error());
while($studentRow = mysql_fetch_array($studentResult)) {
    $student = array();
    $student['student_id'] = $studentRow['student_id'];
   ...
    $students[] = $student;
}
print_r($students);

I really don't understand how you can get multiple student records when you only have one student id. But again, try to move all the query stuff into it's own section and strip out all the html code.

tha_ripmasta · Apr 4, 2007

Thank you for responding and thanks for the tips.

I tried what you suggested, but can't get it working.

Well it seems like i still not getting it, so here i try to break it down again...

I created a search script to find student attendance from my db.

But i can't seem to figure out the right way to loop the results

Here is my search code (REVISED).

==========================================>> DB TABLES

CREATE TABLE students (
student_id int(11) NOT NULL auto_increment,
organization_id int(11) NOT NULL default '0',
group_id int(11) NOT NULL default '0',
student_first_name varchar(70) NOT NULL default '',
student_last_name varchar(70) NOT NULL default '',
student_dob date NOT NULL default '0000-00-00',
student_address varchar(100) NOT NULL default '',
student_homephone varchar(24) NOT NULL default '',
student_cellphone varchar(24) NOT NULL default '',
student_email varchar(100) NOT NULL default '',
student_gender varchar(7) NOT NULL default '',
student_nationality varchar(35) NOT NULL default '',
student_pob varchar(35) NOT NULL default '',
school_id int(11) NOT NULL default '0',
hobbies varchar(100) NOT NULL default '',
end_date varchar(12) NOT NULL default '',
registered_date varchar(20) NOT NULL default '',
registered_by varchar(70) NOT NULL default '',
doctor_id int(11) NOT NULL default '0',
image varchar(255) NOT NULL default '',
status int(1) NOT NULL default '1',
PRIMARY KEY (student_id)
) TYPE=MyISAM;

CREATE TABLE attendance (
id int(21) NOT NULL auto_increment,
studentid int(11) NOT NULL default '0',
organization_id int(11) NOT NULL default '0',
attend_date date NOT NULL default '0000-00-00',
attend_option int(2) NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;

==========================================>> SEARCH FORM

<table width="545"  border="0" cellspacing="3" cellpadding="5" bordercolor="#CCCCCC">

<form method="post" name="formquery1" action="<?php echo '?id=searchdb_attendance&&p=search' ?>">
<tr>
	<td align="left">
	<span class="small-txt-1">Student:</span><br />
	<select name="studentid" style="width: 50%" class="input">
	<option value="">Any</option>

	
	<?php
	$ses_organization = $_SESSION['organization_id'];
	$selstudent="SELECT * from students where organization_id ='$ses_organization' AND status='1' order by student_last_name ASC";
	$selstudent2=mysql_query($selstudent) or die("Could not select students / Contact Administrator or Developer");

while($selstudent3=mysql_fetch_array($selstudent2))
{
	$student_id=$selstudent3['student_id'];					                  
	$student_first_name=stripslashes($selstudent3["student_first_name"]);			
	$student_last_name=stripslashes($selstudent3["student_last_name"]); 

	echo "<option value='$student_id'>$student_last_name $student_first_name </option>";

}
?>

</select>
</td>
</tr>
<tr>
	<td align="left">
	<span class="small-txt-1">Attendance:</span>
	<br>
	<select name="search_attendance" style="width: 50%" class="input">
	<option value="1">General Attendance</option>
	<option value="2">Homework Attendance</option>
	<option value="3">Tutoring Attendance</option>
	</select>
	</td> 
</tr> 
<tr>
	<td align="left">
	<span class="small-txt-1">Month:</span>
	<br>
	<select name="month_att" style="width: 50%" class="input">
	<option value="">Any</option>
	<option value="01">January</option>
	<option value="02">February</option>
	<option value="03">March</option>
	<option value="04">April</option>
	<option value="05">May</option>
	<option value="06">June</option>
	<option value="07">July</option>
	<option value="08">August</option>
	<option value="09">September</option>
	<option value="10">October</option>
	<option value="11">November</option>
	<option value="12">December</option>
	</select>
	</td>
</tr>
<tr>
	<td align="left">
	<span class="small-txt-1">Year:</span><br />
	<select name="year_att">
	<option value="">Any</option>
	<option>2006</option>
	<option>2007</option>
	<option>2008</option>
	<option>2009</option>
	<option>2010</option>
	<option>2011</option>
	<option>2012</option>
	<option>2013</option>
	<option>2014</option>
	<option>2015</option>
	</select>
	</td>
</tr>
<tr>
	<td align="left">
	<span class="small-txt-1">Date:</span><br />
	<input type="text" name="attendance_date"> example (11-21-2006)
	</td>
</tr>
<tr>
	<td align="left">
	<input name="btnSubmitsearch" type="submit" value="Search" class="btn">		
	</td>
</tr>
</form>

</table>

==========================================>> END SEARCH FORM

==========================================>> SEARCH SCRIPT

// get search choices
///////////////////////////////

$SearchAttendance=$_POST["search_attendance"]; 

$SearchStudentid=$_POST["studentid"]; 

$SearchMonth=$_POST["month_att"]; 

$SearchYear=$_POST["year_att"]; 

$SearchDate=$_POST["attendance_date"];

$SearchDate2=$_POST["attendance_date"];

// query
///////////////////////////////

$query= "Select * from attendance WHERE organization_id='$ses_organization' AND ";
if ($SearchStudentid !=""){
$query .= "studentid ='$SearchStudentid' AND ";
}
if ($SearchMonth !=""){
$query .= "MONTH(attend_date) ='$SearchMonth' AND ";
}
if ($SearchYear !=""){
$query .= "YEAR(attend_date) ='$SearchYear' AND ";
}
if ($SearchDate !=""){

$changedate_act_search="$SearchDate";
list($month_act,$day_act,$year_act) = split('[-./]', $changedate_act_search); 
$SearchDate=$year_act."-".$month_act."-".$day_act;

$query .= "attend_date ='$SearchDate' AND ";
}
if ($SearchAttendance !=""){
$query .= "attend_option = '$SearchAttendance' AND ";
}

$query2 = substr($query,0,-4);

$SearchResult=mysql_query("$query2") or die(mysql_error());

$NumberOfResults=mysql_num_rows($SearchResult);

Now I want to display the results like this:

Student Attendance

Terry James

December
1, 5, 9

Paul Martin
June

8, 2, 20

ahundiak · Apr 4, 2007

Ok. Thats a bit clearer. Start by joining the student table to the attendance table so you have the student name. Basically need to add
LEFT JOIN students ON students.student_id = attendence.studentid

If you have not used joins before then read through the mysql documentation for some examples.

Execute the query manually to make sure everything is working. Make sure you order by studentid to group all the attendance records for a given student together. Now you need a loop which detects when a new student is being processed.

$currentStudentId = 0;
while ($row = mysql_fetch_array($attendenceResults)
{
    // Test for new student
    if ($currentStudentId != $row['student_id']) {
        // Start a new student
        echo $row['student_last_name'];

    // Save the id
   $currentStudentId = $row['student_id'];
}
// Now put out the day 
}

}

tha_ripmasta · Apr 4, 2007

Thank you very much ahundiak.... it's working great now.

Here is what I got now:


$query= "Select * from attendance LEFT JOIN students ON students.student_id = attendance.studentid WHERE attendance.organization_id='$ses_organization' AND ";
				if ($SearchStudentid !=""){
				$query .= "studentid ='$SearchStudentid' AND ";
				}
				if ($SearchMonth !=""){
				$query .= "MONTH(attend_date) ='$SearchMonth' AND ";
				}
				if ($SearchYear !=""){
				$query .= "YEAR(attend_date) ='$SearchYear' AND ";
				}
				if ($SearchDate !=""){

			$changedate_act_search="$SearchDate";
			list($month_act,$day_act,$year_act) = split('[-./]', $changedate_act_search); 
			$SearchDate=$year_act."-".$month_act."-".$day_act;

			$query .= "attend_date ='$SearchDate' AND ";
			}
			if ($SearchAttendance !=""){
			$query .= "attend_option = '$SearchAttendance' AND ";
			}

			$query2 = substr($query,0,-4);

			$SearchResult=mysql_query("$query2 order by studentid") or die(mysql_error());

			$NumberOfResults=mysql_num_rows($SearchResult);


$Values1 = array();

			$currentStudentId = 0;
			while ($row = mysql_fetch_array($SearchResult))
			{

			$attendance_date=$row['attend_date'];

			$day = date("j", strtotime($attendance_date));
			$month = date("m", strtotime($attendance_date));
			$year = date("Y", strtotime($attendance_date));

			$Values1[] = $day;

				// Test for new student
				if ($currentStudentId != $row['student_id']) {
					// Start a new student
					echo $row["student_first_name"];
					echo $row["student_last_name"];
					echo "<br><br>";

					// Save the id
				   $currentStudentId = $row['student_id'];

				}
				// Now put out the day
				echo "$day, ";


			}

WHAT I CAN'T FIGURE OUT NOW IS HOW TO FORMAT THE OUTPUT IN TABLES:

I TRIED BUT DIDN'T GET ANY GOOD RESULTS... IT SHOULD BE LIKE THIS

///////  begin while loop
<table>
<tr><td>
$student_first_name $student_last_name
</td></tr>
<tr><td>
$day
</td></tr>
</table>

/////// end while loop

tha_ripmasta · Apr 16, 2007

Anyone can help?

While Loop for student attendance

Ttha_ripmasta

Aahundiak

Ttha_ripmasta

Aahundiak

Ttha_ripmasta

Aahundiak

Ttha_ripmasta

Ttha_ripmasta