Display depending on query result

[mtgriffiths]

Hi All,

 

What i am trying to achieve but failing miserably at is....

 

I want to run a query that will select all students registered onto a course....thats the easy bit.

Once the students have been displayed i want to print out if they have registered their attendance for that course for that day. To check if they have attended today i run a query against my attendance table.

 

This is where i am becoming stuck....if the student has attended i want to have the word present displayed next to their name and if they have not registered their attendance i want absent next to their name.

 

Anyone able to help?

 

Thanks in advance

 

Matthew

 

ps if anyone needs any code il post on request

[DeanWhitehouse]

use an if and else statement depending on the query

[mtgriffiths]

I can work out how to do that but when i put it in to a while loop to get all the students details how do i echo out the correct attendance value?

[DeanWhitehouse]

erm, can you post the code you already have and i can edit to show you

[mtgriffiths]

sure..the query to get the students details is:

 

$Search = mysql_query("SELECT DISTINCT Courses.Course_Name, student_courses.Course_ID, student_courses.Student_ID, students.Enrollment, students.First_Name, students.Last_Name FROM Courses INNER JOIN student_courses ON Courses.Course_ID = student_courses.Course_ID INNER JOIN students ON student_courses.Student_ID = students.Student_ID WHERE Courses.Course_ID='$Course'") or die(mysql_error());

 

Then i am using a loop to print the results into a table. Shown below:

 

echo "<table border='0'>";
echo "<tr align='center'>";
echo "<td width='20%'><font color='#FFFFFF' face='Calibri'>";
echo "<b>Enrollment No";
echo "</td><td width='20%'><font color='#FFFFFF' face='Calibri'>";
echo "<b>First Name";
echo "</td><td width='20%'><font color='#FFFFFF' face='Calibri'>";
echo "<b>Last Name";
echo "</td></tr>";

while($row = mysql_fetch_assoc($Search))
{
echo "<tr align='center'>";
echo "<td><font color ='White' face = 'calibri'>";	    echo $row['Enrollment'];
echo "</td><td><font color ='White' face = 'calibri'>";	echo $row['First_Name'];
echo "</td><td><font color ='White' face = 'calibri'>";	echo $row['Last_Name'];
echo "</td></tr>";
}
echo "</table>";
}

 

After the last name field is where the attendance would go.

[mtgriffiths]

Been trying different things...

 

I think that this would be the nicer way to do it but again still unsure...

 

I have 1 query getting all students registered on to a course

I have another query selecting all students who have logged in today.

 

If there is a match display present if there is no match display absent.

 

Is this possible to achieve?

[DeanWhitehouse]

this should send you in the right direction

<?php
$Search = mysql_query(
"SELECT DISTINCT Courses.Course_Name, student_courses.Course_ID, student_courses.Student_ID, 
students.Enrollment, students.First_Name, students.Last_Name FROM Courses INNER JOIN student_courses 
ON Courses.Course_ID = student_courses.Course_ID INNER JOIN students ON student_courses.Student_ID = 
students.Student_ID WHERE Courses.Course_ID='$Course'") or die(mysql_error());
echo "<table border='0'>
<tr align='center'>
<td width='20%'><font color='#FFFFFF' face='Calibri'>
<b>Enrollment No
</td><td width='20%'><font color='#FFFFFF' face='Calibri'>
<b>First Name
</td><td width='20%'><font color='#FFFFFF' face='Calibri'><b>Last Name</td></tr>"

while($row = mysql_fetch_assoc($Search))
$sql = mysql_query(SELECT DISTINCT/*put where the attendance data is coming from here*/); or die(mysql_error());
if ($sql == 1)/*this is the data from the database, 1 = present*/
{
echo "<tr align='center'>
<td><font color ='White' face = 'calibri'> $row['Enrollment'];
</td><td><font color ='White' face = 'calibri'> $row['First_Name'];
</td><td><font color ='White' face = 'calibri'> $row['Last_Name'];
</td></tr><tr><td><font color ='White' face = 'calibri'>Present </td></tr></table></font>";
}
elseif ($sql == 2))/*this is the data from the database, 2= not present*/
{
echo "<tr align='center'>
<td><font color ='White' face = 'calibri'> $row['Enrollment'];
</td><td><font color ='White' face = 'calibri'> $row['First_Name'];
</td><td><font color ='White' face = 'calibri'> $row['Last_Name'];
</td></tr><tr><td><font color ='White' face = 'calibri'>Absent </td></tr></table></font>";
}

?>

[mtgriffiths]

Thanks for the reply.

 

The code i am now using is:

 

$Search = mysql_query("SELECT DISTINCT Courses.Course_Name, student_courses.Course_ID, student_courses.Student_ID, students.Enrollment, students.First_Name, students.Last_Name FROM Courses INNER JOIN student_courses ON Courses.Course_ID = student_courses.Course_ID INNER JOIN students ON student_courses.Student_ID = students.Student_ID WHERE Courses.Course_ID='$Course'") or die(mysql_error());

$Count = mysql_num_rows($Search) or die(mysql_error());

echo "<font color='#FFFFFF' face='Calibri'>There are <b>".$Count."</b> students registered on to the course: <br><b>".$CourseName."<br><br><br>";


echo "<table border='0'>";
echo "<tr align='center'>";
echo "<td width='20%'><font color='#FFFFFF' face='Calibri'>";
echo "<b>Enrollment No";
echo "</td><td width='20%'><font color='#FFFFFF' face='Calibri'>";
echo "<b>First Name";
echo "</td><td width='20%'><font color='#FFFFFF' face='Calibri'>";
echo "<b>Last Name";
echo "</td></tr>";

while($row = mysql_fetch_assoc($Search))
$sql = mysql_query("SELECT * FROM student_attendance WHERE date = '$Date'") or die(mysql_error());
if ($sql == 1)/*this is the data from the database, 1 = present*/
{
echo "<tr align='center'>";
echo "<td><font color ='White' face = 'calibri'>"; echo $row['Enrollment'];
echo "</td><td><font color ='White' face = 'calibri'>"; echo $row['First_Name'];
echo "</td><td><font color ='White' face = 'calibri'>"; echo $row['Last_Name'];
echo "</td></tr><tr><td><font color ='White' face = 'calibri'>Present</td></tr></table></font>";
}
elseif ($sql == 2))/*this is the data from the database, 2= not present*/
{
echo "<tr align='center'>";
echo "<td><font color ='White' face = 'calibri'>"; echo $row['Enrollment'];
echo "</td><td><font color ='White' face = 'calibri'>"; echo $row['First_Name'];
echo "</td><td><font color ='White' face = 'calibri'>"; echo $row['Last_Name'];
echo "</td></tr><tr><td><font color ='White' face = 'calibri'>Absent</td></tr></table></font>";
}

 

i am getting the following error:

 

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /homepages/29/d239295547/htdocs/DDWA4/SearchCourse.php on line 48

 

Line 48 is :

 

echo "<td><font color ='White' face = 'calibri'>"; echo $row['Enrollment'];

[dezkit]

echo "<td><font color='White' face='calibri'>"; echo $row['Enrollment'];

 

 

[dezkit]

I might as well fixed your whole code

 

$Search = mysql_query("SELECT DISTINCT Courses.Course_Name, student_courses.Course_ID, student_courses.Student_ID, students.Enrollment, students.First_Name, students.Last_Name FROM Courses INNER JOIN student_courses ON Courses.Course_ID = student_courses.Course_ID INNER JOIN students ON student_courses.Student_ID = students.Student_ID WHERE Courses.Course_ID='$Course'") or die(mysql_error());

$Count = mysql_num_rows($Search) or die(mysql_error());

echo "<font color='#FFFFFF' face='Calibri'>There are <b>".$Count."</b> students registered on to the course: <br><b>".$CourseName."<br><br><br>";


echo "<table border='0'>";
echo "<tr align='center'>";
echo "<td width='20%'><font color='#FFFFFF' face='Calibri'>";
echo "<b>Enrollment No";
echo "</td><td width='20%'><font color='#FFFFFF' face='Calibri'>";
echo "<b>First Name";
echo "</td><td width='20%'><font color='#FFFFFF' face='Calibri'>";
echo "<b>Last Name";
echo "</td></tr>";

while($row = mysql_fetch_assoc($Search))
$sql = mysql_query("SELECT * FROM student_attendance WHERE date = '$Date'") or die(mysql_error());
if ($sql == 1)/*this is the data from the database, 1 = present*/
{
echo "<tr align='center'>";
echo "<td><font color='White' face='calibri'>"; echo $row['Enrollment'];
echo "</td><td><font color='White' face='calibri'>"; echo $row['First_Name'];
echo "</td><td><font color='White' face='calibri'>"; echo $row['Last_Name'];
echo "</td></tr><tr><td><font color='White' face='calibri'>Present</td></tr></table></font>";
}
elseif ($sql == 2))/*this is the data from the database, 2= not present*/
{
echo "<tr align='center'>";
echo "<td><font color='White' face='calibri'>"; echo $row['Enrollment'];
echo "</td><td><font color='White' face='calibri'>"; echo $row['First_Name'];
echo "</td><td><font color='White' face='calibri'>"; echo $row['Last_Name'];
echo "</td></tr><tr><td><font color='White' face='calibri'>Absent</td></tr></table></font>";
}

 

`,=,