Adding Controls for each record

[TheIdiot]

I am wanting to create an online attendance sheet that would allow users to log on and use check boxes or option buttons for twelve weeks corresponding to wk1, wk2, wk3, etc... I have no problem retrieving the members of the group (records from db) but do not know how to build the controls based off of a variable number of members. I am working with PHP and MySQL. Any help or pointing in the correct direction is appreciated!

[smantscheff]

The distinctive feature of the controls is their html property "name". Build the names as unique strings using the group member IDs, so that your controls have names like cb1_1, cb_1_2, cb2_1, cb2_2 and so on. Then on the receiving side walk through the $_POST array and check each input name with a regular expression like

foreach( $_POST as $key => $value)
  if (preg_match( '/cb([0-9]+)_([0-9]+)/', $key, $match )) {
    $checkbox = $match{1];
    $groupmember = $match[2];
    ...

]

[TheIdiot]

Sorry, I don't seem to be following you. Forgive me as I am a newb, but it looks like you want me to literally code each control (I could be misunderstanding you). That would be 12 controls times who knows how many group members. It also needs to be dynamic as there are about 40+ groups and the number for each group changes.

[smantscheff]

That's what a programming language is for: automating tasks.
Write a PHP script which sets up your input HTML page with all the controls you need. E.g. along the following lines:

$groups = mysql_query('SELECT * FROM groups');
while ($group = mysql_fetch_array( $groups )) {
  $groupId = $group['id'];
  for ($week = 1; $week <= 12; $week++) {
    echo "<input type=checkbox name='cb$week"  . "_$groupId'/>Group $groupId, week $week<br/>";
  }
}

[TheIdiot]

Ok, I am following it now. Now an additional question. I will obviously want to store the changes and recall them the next week when they log in. I know that I will need to store the values into an array, but I am unclear as to how I get them from the array into the db and into the correct fields.

[shdwmage]

Problem:

• Online attendance sheet
• Unknown number of users

Known Information:

• Course is 12 weeks
• Multiple groups

Unknowns:

• Is the person checking in on their own?
• Or is one person checking everyone in?
• The structure you currently have setup for the SQL table
• Can a person be in more than one group?
• Do the groups run at the same time?
• Will the attendance be changed weekly, or manipulated after the fact?

[TheIdiot]

Knowing the unknowns:

-One person will fill out the attendance sheet for the group.
-The structure is setup as: id, f_name, l_name, email, phone, group_id, and then 12 additional fields as wk1, wk2, etc... with boolean values.
-A person can be in more than one group, but we have their name stored in the db multiple times depending on if they are in multiple groups. Multiple same f_name and l_name with different group_id.
-As far as the groups running at the same time, I think yes but not exactly sure what you are looking for. The time period is generally the same, such as they will all start within the same week but not the same date.
-Not sure what you mean by changed weekly but there could be additions to the group at a point during the 12 weeks but generally they stay the same.

[shdwmage]

Thanks, I'll formulate up some thoughts and give you a probably overly detailed answer with bunny trails on notes as far as the eye can see! I'll put the bunny trails at the bottom though.

[shdwmage]

Details:
Using PHP / MySQL to create an attendance list.
All group members show up at the same time so one person can enter attendance.
All courses are 12 weeks long.

Information / Thoughts:
I know from personal experience rechecking check boxes can be a pain in the behind.

That being said I would store all 12 weeks into 1 field in the database using an implode command.

I would use a select field like:

<select name="week[]">
<option value="Yes">yes</option>
<option value="No">No</option>
</select>

By using code similar to the above I can then do the following:

//explode the week variable - x is the field number in the output
$week = explode(",",$row[x]);
//set each drop down
foreach ($week as $w){
$body ='<select name="week[]">
<option value="Yes">yes</option>
<option value="No">No</option>
<option value="' . $w .'" selected="selected">' . $w .'</option>
</select>';

The explode command will take the information already stored and turn it into an array.

Then when I go to put the information into the database I would use an implode command

$week = implode(",", $week);

That is the easy part, the complicated part comes when you have multiple users showing all up at the same time.

-- the complicated part - multiple rows --
First I would run a count of the number of people in the group and store it as a hidden field.

Then I would use a while loop and nest the current one in it.

So it would be something like: (this is psuedocode)

while($row = mysql_fetch_array($result, MYSQL_ASSOC)){


$output .= '<input name="id'. $row[id] . '" type="hidden" value="' . $row[id] . '" />';
$output .= 'Name: ' . $row[f_name] . ' ' . $row[l_name];

$week = explode(",",$row[x]);

//set each drop down
foreach ($week as $w){
$output .='Week ' . $a .'
<select name="week'. $variable1 . '[]">
<option value="Yes">yes</option>
<option value="No">No</option>
<option value="' . $w .'" selected="selected">' . $w .'</option>
</select>'
$a += 1
};
};

Now you will need to do your own customized formatting, but that's the basic.

When you post the data use something similar to what is posted above.

[TheIdiot]

That is a great help, and I appreciate it very much. If you could do one more thing, I think it would help me in the long run. I get the gist of what is going on, but if you can kind of walk me through each segment so I understand exactly what is going on and how it is communicating with the db I know that I will retain the information and be able to correct any mistakes that I may make. I would rather learn then just be handed the code. I guess I am an exception to the rule!

[shdwmage]

What this code is doing is manipulating the results you are getting from your Query with PHP. There is no need to change the actual queries you run on the server.

What the implode command does is take an array and turn it into plain text.
So if you had an array 1 2 3 4, it would convert it to 1,2,3,4

Explode is doing the opposite. It is turning it from 1,2,3,4 into an array 1 2 3 4.

So I will go over this line by line: (I added comments to the code, and fixed a couple typos.)

//Run through the records retrieved with the MySQL query.
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
//set a to the first week
$a = 1
//Setup the hidden input so each row has its own id.
$output .= '<input name="id'. $row[id] . '" type="hidden" value="' . $row[id] . '" />';
//Output the first and last name with a space between them.
$output .= 'Name: ' . $row[f_name] . ' ' . $row[l_name];
//Turn row[x] into an array with the explode command, where x is the name of the field
$week = explode(",",$row[x]);
//for every item in the array week do the following
foreach ($week as $w){
//Store the information into the variable output
$output .= 'Week ' . $a .'
<select name="week'. $row[id] . '[]">
<option value="Yes">yes</option>
<option value="No">No</option>
<option value="' . $w .'" selected="selected">' . $w .'</option>
</select>';
//Increase the count by 1
$a += 1};
};

Line 2: What this is saying is that when you are on the current row of your query results do the following: (then whatever is typed below it)
Line 4: Start a counter at 1.
Line 6: Setup a hidden field to differentiate the different people based on their ID
Line

[shdwmage]

Continuing where i left off
Line 8: Output the persons name
Line 10: In line 10 the data retrieved from the weeks field in the database is exploded. It is turned back into an array so we can use other commands on it.
Line 12: The foreach loop will run through all the items in the array and manipulate them in the ways presented inside the loop.
Line 14 - 19: Format output and display it. By using ' . $variable . ' you are just adding the variable to the text. So if I said "cow's like to "' . $action . '" grass." It would output "cow's like to eat grass" or whatever was in the action variable. The $row[id] locks in the persons ID.
Line 21: Increase the counter and finish the loop. If it's out of numbers it will exit and go to the next record.
Line 22: Finishes out the while loop and moves onto the next record if there is one.

By using the .= command I am taking everything that was entered into this variable before and then adding new content to it.

[TheIdiot]

That helps out a lot! I appreciate the time that you put into explaining it all to me. Now it is time to play around with it. Thanks again!

[shdwmage]

One thing I forgot to mention when I revised my post, the reason I use the array stored to a field in instances like this is because it allows me to change the number of weeks on the fly.

When I go and create the records I can choose 1 week up through infinity really. And it doesn't really require to many other changes to achieve the goal. No need to add extra fields, or change code to allow for the fields.

But I am glad I could be of assistance.