CODEWITHSUNDEEP
phpmysql
eric
eric

Reputation: 454

MySQL and PHP for attendance

I know that there are quite a few different topics out there but I don't believe they are exactly what I'm looking for.

I need to make a database using MySQL for a club I'm in. I want to create a webpage that the Secretary can log into and take attendance for the meeting. First off I need to know what exactly I would need for creating the table.

I was thinking something like 

CREATE TABLE 'Members'('memberName' VARCHAR(40) NOT NULL, 
    '11/1/11' INT NOT NULL DEFAULT 0, 
    'totalAttendance' INT NOT NULL DEFAULT 0, 
     PRIMARY KEY('memberName'), UNIQUE INDEX 'memberName'_UNIQUE('memberName' ASC));

and just keep adding columns for the date using ALTER TABLE 'Members' ADD '11/8/11' INT NOT NULL DEFAULT 0;

I was going to use PHP to create the column and change the default 0 to a 1 if they attended (not exactly sure how I was going to do that but I was thinking some check boxes and if they are checked edit that column for that name?) So something like mysql_query("UPDATE 'Member' SET 'meetingDate'='1' WHERE memberName='nameOfMember'") or die(mysql_error()); I believe.

What I really need is a way to count across each column to count the total number of times each member has attended and then placing that into the totalAttendance column.

Can someone help me out with that? I'm thinking it is a really easy statement I'm just not asking Google the correct question. Thank you for any help you can give.

Upvotes: 0

Views: 2784

Answers (3)

swatkins
swatkins

Reputation: 13630

I'd suggest you set up something like this:

id    |  MeetingDate |    MemberName    |    Present
 1    |  2011-11-01  |    John Doe      |       1
 2    |  2011-11-01  |    John Smith    |       0
 3    |  2011-11-01  |    John Jackson  |       1
 4    |  2011-11-02  |    John Doe      |       0
 5    |  2011-11-02  |    John Smith    |       1
 6    |  2011-11-02  |    John Jackson  |       1
 7    |  2011-11-03  |    John Doe      |       1
 8    |  2011-11-03  |    John Smith    |       1
 9    |  2011-11-03  |    John Jackson  |       1

So you'd have a row for each member for each meeting whether they attended or not. My guess is you're not going to have millions and millions of rows with this system, so don't worry about performance.

You can get the total attendance for a member with something like:

SELECT SUM(Present) as total FROM attendance WHERE MemberName = 'John Doe';
// returns -> 2

You can get the total attendance for a meeting with something like:

SELECT SUM(Present) as total FROM attendance WHERE MeetingDate = 2011-11-01;
// returns -> 2

You can get all of the members that missed a specific meeting with something like:

SELECT MemberName FROM attendance WHERE Present = 0 AND MeetingDate = 2011-11-01;
// returns -> John Smith

Upvotes: 2

Naftali
Naftali

Reputation: 146300

Please Please Please do not do this.

Make every date a new row. Make a date column.

So your table would look something like this:

  ID   |    Date      | Attendance |  
   1   |  2011-11-01  |      5     |
   2   |  2011-11-02  |      12    |
   3   |  2011-11-03  |      3     |

Then you can have a table of members.

Then you can have a join table of member to meeting. -- Then when you have this you do not even need an Attendance column in the Meeting table because you can just count how many members are at each meeting with a simple query:

SELECT m.id, m.date, count(*) as `attendance`
FROM meeting m
LEFT JOIN meeting_to_member mm ON mm.meetingid = m.id
LEFT JOIN member mem ON mem.id = mm.memberid 
GROUP BY m.id;

Upvotes: 4

Derek
Derek

Reputation: 4931

You could try using INSERT into... SELECT COUNT...

Look at this guys code: insert ...select ... count ... on duplicate key update error

You can cut out a lot of his query but it should help.

Upvotes: 0

Related Questions