[SOLVED] Conceptual DB Construction

[karatekid36]

I want to create an attendance tracking table.  I have a list of members already and I want that to populate the attendance table.  The amount of members will grow and shrink over time so i would like to create a table that will account for that, meaning that if the active membership changes from meeting to meeting it will not be a big deal.  Also, I want to be able to add a new event that attendance can be taken for.  Some of the meetings are required while others are not.  Running stats on number of total meetings attended and total required meetings attended is pretty much a necessary part of this concept.  I want this mysql db to be interfaced with a php page.  I am very knowledgeable in both mysql and php, but I have hit a mental block as to how I need to go go about this.  ANYONE with an input, ideas, or thoughts should please respond, pm me, email me, send smoke signals, do whatever to get a hold of me.  I need some help figuring this out.  thanks!

[Wildbug]

Three tables, one members, one events, one attendance.

 

CREATE TABLE members (
id INTEGER UNSIGNED ZEROFILL AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(64),
lastname VARCHAR(64),
joined DATE,
quit DATE
etc);

CREATE TABLE events (
id INTEGER UNSIGNED ZEROFILL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
date DATE,
etc
);

CREATE TABLE attendance (
mid INT UNSIGNED,
eid INT UNSIGNED
);

 

If the member attends, enter his/her member id and the event id into the attendance table.  A LEFT JOIN can be used to find members who didn't attend; dates can be used to seperate members who were not active during a given event/meeting.  Information pertaining to mandatory/voluntary meetings can be stored in the events table.

[karatekid36]

I very much appreciate the time and effort you put into this response.  This is a solid base for me to go from.  My question in response to your post has to do with running stats on attendance.  If a member wants to take certain roles in the organization, they must have a solid attendance record and meet certain percentages.  Meetings and events happen at random during the week, so in a given time period there can be many or few events, but never the less I would like to go to a page that will just list each member’s attendance percentage.  I think having it recalculate every time I access the page could be slow once membership rises above 100 or so, but I feel that it would provide the most up to date attendance record.  If you have any thoughts as to storing the percentage or calculating the percentage every time, I would love to hear it.  If you would like any more information about my current db setup or table set up feel free to ask for it. 

[Wildbug]

With a judicious use of incidies, you shouldn't have any trouble with calculating stats on the fly for large numbers of members (unless you're running this off a 5.25" floppy drive in your i386, then beware!).

 

SELECT event.name,event.mandatory,attendance.eid,COUNT(*) FROM member LEFT JOIN attendance ON member.id=mid LEFT JOIN events ON events.id=eid WHERE mid=<member_id here> AND events.date BETWEEN member.since AND member.quit GROUP BY events.mandatory,attendance.eid;

 

That should list four groups: (1) mandatory events; member attended (2) voluntary events; member attended (3) mandatory events; member did not attend (4) voluntary events; member did not attend.  You could also add a condition to the WHERE clause only selecting events.mandatory=true.  Then the percentage can be calculated in PHP by: mandatory_attended_group / (mandatory_attended_group + mandatory_did_not_attend_group).

[karatekid36]

Okay I am going to chew on this for a little and then I will get back to you.  Thanks for all of your help thus far and I am sure I will have some questions in the near future.  Thanks!

[karatekid36]

I would like to ask some more specific questions.  Shall we continue this via email or do you want to continue through the forum?

[fenway]

I would like to ask some more specific questions.  Shall we continue this via email or do you want to continue through the forum?

Keep the discussion to the forums, so we can all beneift.

[karatekid36]

the members of this club have a "status"; active, past, honorary.  this is how it will be determined if they current members.  joins are my mysql weakness and I am not very sure how to get the results I need.  With this new information, how can an sql statement now be made to determine if a person attended an event.

 

My events table is going to look like this...

Events (

    Event_id

     Name

     Type

     Location

     Time

     Description

     Date)

 

and the type column will be where a meeting is denoted as MAN of VOL.

 

Where do you think we go from here?

 

[Wildbug]

I think this should work (column/table names might not match yours; change to suit your setup):

 

SELECT member.name,member.status,event.name,event.type,IF(attendance.mid IS NULL,'Did not attend','Attended') AS attended FROM member JOIN event LEFT JOIN attendance ON event.id=attendance.eid AND member.id=attendance.mid

[karatekid36]

SELECT brothers.user_id, brothers.first_name, brothers.last_name, brothers.status, events.name, events.type, IF( attendance.mid IS NULL , 'Did not attend', 'Attended' ) AS attended

FROM brothers

JOIN EVENTS LEFT JOIN attendance ON events.event_id = attendance.eid

AND brothers.user_id = attendance.mid

 

This query is very much what I was looking for.  From here, how could I calculate a member's attendance record from a certain date to a certain date?  For example, member x attended xx% of the mandatory events from 06/01/07 to 06/11/07.  Your input would be greatly appreciated.  Thank you.

[Wildbug]

You can use a subquery to find the total number of mandatory events.  You'll then need to divide the number of a brother's attended events by that number.  I haven't tested this since I haven't made a mockup of your database, but it should work if you have MySQL 4.1+:

 

SELECT
brothers.user_id,
CONCAT(brothers.first_name," ",brothers.last_name) AS BrotherName,
brothers.status,
100*SUM(attendance.mid IS NOT NULL)/(SELECT COUNT(*) FROM events WHERE type="Man" AND date IS BETWEEN 20070601 AND 20070611) AS Attendance
FROM brothers
JOIN events
LEFT JOIN attendance ON (events.event_id = attendance.eid AND brothers.user_id = attendance.mid)
WHERE
events.type="Man"
AND events.date IS BETWEEN 20070601 AND 20070611
GROUP BY brothers.user_id

[karatekid36]

using this query...

SELECT

brothers.user_id,

CONCAT(brothers.first_name," ",brothers.last_name) AS BrotherName,

brothers.status,

100*SUM(attendance.mid IS NOT NULL)/(SELECT COUNT(*) FROM events WHERE type="Mandatory" AND date IS BETWEEN 2007-06-01 AND 2007-06-13) AS Attendance

FROM brothers

JOIN events

LEFT JOIN attendance ON (events.event_id = attendance.eid AND brothers.user_id = attendance.mid)

WHERE

events.type="Mandatory"

AND events.date IS BETWEEN 2007-06-01 AND 2007-06-13

GROUP BY brothers.user_id

 

I am getting this error....

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BETWEEN 2007-06-01 AND 2007-06-13) AS Attendance

FROM brothers

    JOIN events

    ' at line 5

 

Any thoughts?  I think it has to do with my date structure, but I am using the 0000-00-00 format so I was giving that a go.  Let me know what you think.

[Wildbug]

Yep, that's your date format.  You can specify date (and time, for that matter) in a few different ways.  The one I used in the sample query was 20070601 without quotes.  If you want to include separators, you'll need quotes:  "2007-06-01".

 

See date/time formats in the MySQL manual for the grisly details.

[Wildbug]

Oh jeez.  My mistake.  Remove the "IS" -- it should just be "...events.date BETWEEN 20070601 AND 20070611..."

[karatekid36]

SELECT

  brothers.user_id,

  CONCAT(brothers.first_name," ",brothers.last_name) AS BrotherName,

  brothers.status,

  100*SUM(attendance.mid IS NOT NULL)/(SELECT COUNT(*) FROM events WHERE type="Mandatory" AND date IS BETWEEN "2007-06-01" AND "2007-06-13") AS Attendance

FROM brothers

  JOIN events

  LEFT JOIN attendance ON (events.event_id = attendance.eid AND brothers.user_id = attendance.mid)

WHERE

  events.type="Mandatory"

  AND events.date BETWEEN "2007-06-01" AND "2007-06-13"

GROUP BY brothers.user_id

 

this is still producing an error...

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BETWEEN "2007-06-01" AND "2007-06-13") AS Attendance

FROM brothers

  JOIN eve' at line 5

I have been messing with this for a little bit here at work(shhh don't tell me boss) and I can't get it to work.

[karatekid36]

I fixed it.  I missed the other IS.

[karatekid36]

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BETWEEN "2007-06-01" AND "2007-06-13") AS Attendance

FROM brothers

  JOIN eve' at line 5

 

This is the error that I am getting when I am running it in myphpadmin. 

[karatekid36]

The current output of this is awesome.  It is almost there as to what I was after.  Thank you for all of your help so far.  The final out put needs to be only brothers with a status = x.  Where should I place this little piece of the query in so that it does not slow down the query?

[Wildbug]

First, tell me where you think it needs to go.

[karatekid36]

SELECT

   brothers.user_id,

   CONCAT(brothers.first_name," ",brothers.last_name) AS BrotherNameWHERE brothers.status = "Undergraduate",

   100*SUM(attendance.mid IS NOT NULL)/(SELECT COUNT(*) FROM events WHERE type="Mandatory" AND date IS BETWEEN "2007-06-01" AND "2007-06-13") AS Attendance

FROM brothers

   JOIN events

   LEFT JOIN attendance ON (events.event_id = attendance.eid AND brothers.user_id = attendance.mid)

WHERE

   events.type="Mandatory"

   AND events.date BETWEEN "2007-06-01" AND "2007-06-13"

GROUP BY brothers.user_id

 

It should be something like this right?

[Wildbug]

No.  The "S" in "SQL" stands for "structured."  SQL queries have a certain structure/order that must be followed.  First a SELECT..., then a FROM..., then a WHERE....  You already have a WHERE clause in this query, and it doesn't go between the SELECT and the FROM parts.  All you need to do is to add your new condition to the existing WHERE clause.  Use an AND.

[karatekid36]

Okay that now makes a lot of sense when you put it that way.  Thank you.  I really appreciate your patience with me on this.  It has only been 3 months since I started getting into this. 

[karatekid36]

Okay I have tweaked the Query right to where I want it and it is perfect.  Now I would like to display it in a php/html file.  Because it is not really a table that I would be pulling the information from, all I should have to do is make the query and then put the results of the query in an array and then have the array be used in making an html table correct?

[Wildbug]

Yes, retrieve it as any other query.  You have three columns in that query, and they are all named, either with their column name or an alias for calculated/new values (such as "BrotherName").  You can expect these names to be used when using something like mysql_fetch_assoc().

[karatekid36]

Wildbug,

 

I can not thank you enough for all the help you have provided me.  You have made my life a lot easier and you were extremely patient the whole time dealing with me.  This forum needs more people like you!  I just wanted to say thank you very much.  I owe you a favor big time.  I can not say enough to thank you. 

 

Karatekid36

aka

Ryan