Attendance Ideas

[varun khurana007]

Hi everyone,
As I could not find my problem in the previous threads,i decided to post a new one.
Actually i'm designing a project for a college in which attendance is a very important module. I have an attendance table having the following columns:
Faculty code,subject code,Student roll,attendance date,attend

where attend field is supposed to hold 'A' or 'P' corresponding to every date.
Now i find it difficult when i want to find out the attendance percentage for each subject per student.
Also there are many redundant entries which is making it slower,such as for each student there are 6 subjects
Can u suggest me something to improve upon this so that i can make it work.I dont mind changing the table structure or adding additional tables.
(Btw the first 3 fields are imported from 3 tables namely allotment,subject and student. where allotment stores which faculty takes which subject which in turn corresponds to a list of students).

Any help will be appreciated. Thanks.

[varun khurana007]

Sorry forgot to mention,i'm doing this in PHP/MySQL.

[henry0]

varun khurana007, Welcome to WebmasterWorld! [webmasterworld.com]

you could do a few things
first read about Optimization [dev.mysql.com]

also, couldn't you first average the results of the students six fields and load the result in a new table
then use that single result in your final query?

we need more info on table structure and your scripts logic

[varun khurana007]

Hi henry,thanks for that quick response.
Well I collected many ideas on optimization for my case from the mysql docs and and a book that i have. Its my date column in attendance table that is proabably inefficient. K,i'll give you a example from my 'attendance' table
FacCode SubCode Studentroll date attend
FAC001 ELE123 050903123 CURDATE() P
FAC003 ELE203 050909134 CURDATE() A
FAC001 ELE123 050903123 CURDATE() P
and so on...
where CURDATE() enters the date on which the class was taken.

Now if want to find out the attendance percentage=(Classes attended/Total no of classes) for any one(lets say 050903123 in subject ELE123),what logic can i use here?

[henry0]

Try this, move the variables around until it results as expected, but I cannot remember how to make it in a one shot query.

$sql=mysql_query("SELECT
count(attend)
From
FROM
attendance
WHERE
Studentroll='$studentroll'
AND
SubCode='$subcode' ");

$num=mysql_result($sql,0);
echo"$num<br>";

$sql2=mysql_query("SELECT
count(subcode)
From
FROM
attendance
WHERE
Studentroll='$studentroll'
AND
SubCode='$subcode' ");
$num2=mysql_result($sql2,0);
echo"$num2<br>";

$percent=($num2/$num)/100;
echo"$percent<p>";

[varun khurana007]

Hey thanks a lot man.It worked,doesnt matter to me if its not a one line query. You really saved the day for me! :)

[henry0]

Glad it works for you

but if another member was to remember me how to make it one line
then it'll make my day :)