Help me to display attendance between two dates in PHP

[pallavibhoite]

I want to develop application using PHP. One module in this application displays attendance of students under particular teacher, between given two dates.
The structure of tables in database -

1. student_info(Roll_No, Teacher_Id, First_Name, Last_Name)
2. attendance_info(Teacher_Id,Roll_No,Date,Present)
Present stores '1' if student is present on that date and '0' if absent.

If user selected two dates then attendance of students between those dates must be displayed in table.

I want to display result in the form

Roll No First Name Last Name Conducted lectures Attended Lectures
1 ABC ABC 4 2
2 XYZ XYZ 4 3

I tried to do that by first obtaining the set of students under particular Teacher from "student_info" table. Then for each student in this set I fired following query

$Query=SELECT sum( attendance_info.Present ) , count( attendance_info.Present) FROM attendance_info WHERE attendance_info.Teacher_Id ='20' AND  attendance_info.Roll_No ='1' AND Date BETWEEN '2011-03-09' AND '2011-03-15';

$view_attendance = mysql_query($query1, $database) or die(mysql_error());
$row_view_attendance = mysql_fetch_assoc($view_attendance);

echo $row_view_attendance['sum( attendance_info.Present )'];
echo $row_view_attendance['count( attendance_info.Present )'];

When I run this query in query analyzer it worked. But I'm not getting it with PHP.

Plz help me..

[wcordio]

pretty sure 'AND date' (from your query) is trying to use the php function, not an actual field name

[diafol]

It seems to me your second table is repetitive. Perhaps you need a greater degree of separation for your data. It should be normalised. I don't see why you need 'empty records' in your second table (present = 0), just don't have the student show up at all. To ultra-separate, I'd do this:

TEACHER
teacher_id [PK]| teacher_first | teacher_last | teacher_initials | other teacher fields...

STUDENT (I've used student_id instead of roll_no - I assume that's what roll_no means)
student_id [PK]| firstname | lastname | other student fields...

CLASS(lecture series - impt so that teachers can conduct more than one series of lectures with different students)
class_id [PK]| teacher_id [FK]| class_name | other fields...

CLASS_STUDENT (list of students for the class)
class_id [FK]| student_id [FK]

CLASS_DATES (when lectures delivered)
period_id [PK] | class_id [FK] | date | time_start | time_end

REGISTER
period_id [FK]| student_id [FK]

[PK] = Primary Key (int), [FK] = Foreign Key (int)
Although FK's aren't usually defined as such in MySQL
Now, to get all this to work, you need to use a number of different JOINS.

Apologies if this isn't what you want, but your structure as is looks wasteful.