how to make an attendance list

[siobhan]

I am trying to make a form that will keep a record of 1) all the people who have attended a sports practice,2) what activity they completed and 3) the date they completed it.

Right now I have the list of people populated from a user table in my database.

CREATE TABLE tbl_user (
userID int(11) NOT NULL auto_increment,
firstName varchar(50) default NULL,
lastName varchar(50) default NULL,
email varchar(50) default NULL,
username varchar(50) default NULL,
password varchar(50) default NULL,
usergroup varchar(50) default NULL,
PRIMARY KEY (userID),
KEY userID (userID)
) TYPE=MyISAM AUTO_INCREMENT=54 ;

I have another table which lists the type of activity and date of the practice:

CREATE TABLE attendancelist (
userID int(11) NOT NULL auto_increment,
firstName varchar(50) default NULL,
lastName varchar(50) default NULL,
activity varchar(50) default NULL,
dateday int(11) default NULL,
datemonth text,
password varchar(50) default NULL,
usergroup varchar(50) default NULL,
PRIMARY KEY (userID),
KEY userID (userID)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

Question one: are the database tables okay? Is there anything you might do differently?

Question two: Okay, so I have constructed the form.

Right now I am pulling the names for the userlist from the user table in the database and then displaying them in the form. Beside the names I have created three checkboxes so that the person taking attendance can select which activity they've completed. I've used checkboxes instead of drop down to make data entry more speedy.

So, the form looks like this:

Joe James []run []walk []play
Gee Whiz []run []walk []play
etc....

Question three: Is the above a reasonable way to handle this or would you do it differently?

The code for the form is below.

<?php do { ?>
<tr valign="top">
<td height="30"><?php echo $row_userlist['firstName']; ?> <?php echo $row_userlist['lastName']; ?> <input name="firstName" type="hidden" id="firstName" value="<?php echo $row_userlist['firstName']; ?>">
<input name="lastName" type="hidden" id="lastName" value="<?php echo $row_userlist['lastName']; ?>"></td>
<td>
<table width="200">
<tr>
<td width="67"><label>
<input name="walk" type="checkbox" id="walk" value="checkbox">
walk</label></td>
<td width="63"><input name="run" type="checkbox" id="run" value="checkbox">
run</td>
<td width="54"><input name="play" type="checkbox" id="play" value="checkbox">
play</td>
</tr>
</table></td>
</tr>
<?php } while ($row_userlist = mysql_fetch_assoc($userlist)); ?>
</table>
<input type="submit" name="Submit" value="Submit">
</form>

Question four: The problem is when I do the insert I'd like each user who is selected as having attended the practice to show up in the database table. But right now only the last name in the list is inserted. Why is this happening?

Is it possible to write an insert statement that allows data collected from one form to go into multiple rows? If so, how would you write an sql insert statement to insert the data from this form so that each user appears in a separate row in the database table? Or would you set up the form entirely differently?

Thanks for your help
Siobhan

[devinemke]

your user table has 2 indexes on the same field which is un-necessary: you have PRIMARY KEY (userID), and then another KEY userID (userID).

your second table has a lot of redundant data that is already stored in the first table. plus you should never store date/time data as VARCHARS. i suggest this data stucture:

CREATE TABLE `attendance` ( 
`attendanceID` int(11) NOT NULL auto_increment, 
`date` date default NULL, 
`userID` int(11) default NULL, 
`activity` varchar(50) default NULL, 
PRIMARY KEY (`attendanceID`), 
KEY `userID` (`userID`) 
) TYPE=MyISAM AUTO_INCREMENT=1 ;

if the activities are a set list (like run, walk, play) you may want to think about making the activity field an ENUM type.

the point here is all the user data is in the first table and attendance/activity data in the second and the related field is "userID".

[siobhan]

Thanks for the tips! I will try this and report back. One other quick question. I had set the dates as 2 var char fields because I have two drop downs lists: one for day and one for month.

How would I get these two lists to go into one date field?

siobhan

[devinemke]

Originally posted by siobhan
I had set the dates as 2 var char fields because I have two drop downs lists: one for day and one for month. How would I get these two lists to go into one date field?

that depends on how you are collecting the month/day data in the form. if the dropdowns are just numbers then you could simply do:

$date = date('Y') . '-' . $_POST['month'] . '-' . $_POST['day']

if the months are text (Jan, Feb, etc...) then you could do something like:

$date = date('Y-m-d', strtotime($_POST['month'] . ' ' . $_POST['day']));

note: the above examples use the current year
remember: mySQL stores dates as YYYY-MM-DD