PHP Check box form

jseth

11-15-2010, 08:35 PM

I'm certain that this is easy.... for some one! I have an application which stores user skills for our employees. The user can log in and click on a menu item which will display (results from a query) their unachieved skills, with a check box, the intention of which it to have them check which skills they can perform.

I can make a checkbox form using values I already know, but I do not know how to to deal with the results of a query being the values. What I would like to have happen is that when a user checks one or more skill check boxes, the field "achieved" in the db would be set or updated to "Yes". Each skill has a unique user_skillID, so that is the field I am looking to "hook up" with its "achieved" field when the user checks the box. I have it going to a changeskillsrecord.php page, but I'm not really sure what to put in there either.

I hope someone can help me with this, I just can't seem to get my head around it! Currently, the query below displays all the skills that a user would have NOT achieved, for the Access 2007 topic.

<html>
<head>
<title>Microsoft Access 2007 Skills Listing</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link href="Includes/si.css" rel="stylesheet" type="text/css" />
</head>

<body topmargin="5" leftmargin="0">
<table width="980" align="center" cellpadding = "0">
<td>
<tr>
<th align="center">
<b>Microsoft Access 2007 skills for
<?php
echo $_SESSION['fname'];
echo ' ';
echo $_SESSION['lname'];
echo ":";
?>
<br/><p>(If no skills appear, there are no skills for you to update.)</p>
</th>
</tr>
</td>
<tr>
<td>
<?php require 'Includes/topicsmenu.php'; ?><br />
</tr>
</td>
</table ><br />

<table width="800" align="center" border="1">
<td>
<tr>

<th>Skill ID</th>
<th>Skill Description</th>
<th>Select Achieved Skills</th>

</tr>
</td>

<?php
mysql_select_db($database_skills, $skills);

$query_MySkills = "SELECT user_skills.user_skillID, skills.topic, skills.skill, user_skills.tngdate, user_skills.achieved, users.username, DATE_FORMAT(`tngdate`,'%d-%b-%y') AS tngdate
FROM skills, users, user_skills
WHERE users.username = ('$username') AND users.userID=user_skills.userID AND user_skills.achieved != 'N/A' AND user_skills.achieved !='Yes'
AND user_skills.skillID=skills.skillID
AND skills.topic = 'Access'
ORDER BY topic, skill";

$MySkills = mysql_query($query_MySkills, $skills) or die(mysql_error());
//$row_MySkills = mysql_fetch_assoc($MySkills);
$totalRows_MySkills = mysql_num_rows($MySkills);

$result = mysql_query($query_MySkills, $skills);

while($row_MySkills = mysql_fetch_assoc($MySkills))
{
echo '<tr>';
echo '<td align="center">' . ($row_MySkills['user_skillID']).'</td>';
echo '<td align="left">  ' . ($row_MySkills['skill']). '</td>';
echo '<td <form method="POST" action="changeskillsrecord.php">
<p align="center"><input type="checkbox" name="user_skillID[]" value="$row_MySkills[user_skillID]">';
echo '</tr>';
}
?>
<br /><br />
<td>
<td>
<td>
<p align="center"><input type="submit" value="Select"></p>
</form>
</td>
</td>
</td>
</table>
</body>
</html>

mlseim

11-15-2010, 10:55 PM

There are three parts to your problem ...

1) Processing the checkboxes.
You are using a checkbox array, which is fine.
When the form is processed, you have to loop through the checkbox array,
and pick out the values. The array will only contain the checkboxes that
were checked. So the value needs to be some sort of code so you know
which one was checked. Don't use the name of your MySQL column, because
anyone can see that in the HTML source.

2) Saving the checkbox array into your MySQL table.
You already know who the person is, so you need to loop through that
checkbox array and determine which column to update to "YES" for that user.
I don't know if you have separate columns (variables) for each skill?

3) Re-displaying your form with the checkboxes checked.
Each time you display your form, you have to query the database and pull-out
all of the skill variables. If the variable says "YES", then you have to add "checked"
in that <input ... checkbox> tag. This will be done in a loop or individually as
PHP builds your dynamic form.

hint: instead of using the word "YES" for the value of the skill, use the word "checked".
then, you can use it directly in your <input ... checkbox> tag.

I hope all that makes sense, or at least triggers some "aha" moments.

.

jseth

11-16-2010, 01:01 PM

Thank you for your reply.

#1: "When the form is processed, you have to loop through the checkbox array, and pick out the values. The array will only contain the checkboxes that were checked. So the value needs to be some sort of code so you know which one was checked." I was intending to use the "user_skillID", which is a unique number of a particular skill for a user.

#2: The column to update to "Yes" for the selected user is the "achieved" column in the "user_skills" table. This table uses a many-to-many relationship of users to skills, skills to users, from a table called "skills" which contains the skills, and a table called "users" which contains the users, comprised of foreign keys, creating this table called "user_skills" which contains each user and their required skills (each user can have many skills, and each skill can have many users).

#3: Don't know how to do this.

No aha moments here. I'm not that experienced with this - if I can see instructions, I can "plug in" my stuff to make it work, but I have no idea how to create it from scratch.

mlseim

11-16-2010, 05:58 PM

So I'm not following the skills thing ...

Does each user have a MySQL row like this?

UserID,skill1,skill2,skill3,skill4 .... etc.

So you have a column for each skill?

Or do you have a row for each skill ...

userID,skill1
userID,skill2
userID,skill3

jseth

11-16-2010, 06:21 PM

A row for each skill. The link table called user_skills has the following columns:
user_skillID - the auto increment number of the record
userID - linked to the users table, this is the userID for each user
skillID - linked to the skillID in the skills table, this is the skillID for each skill
tngdate - a date field where the date of training, if needed, can be entered
achieved - initially, this field is null, can be Yes or N/A

Looks like:
user_skillID, userID, skillID, tngdate, achieved

What I can't seem to fathom is how to get the check box that the user clicks on associated with the user_skillID, so that that user_skillID's achieved field gets updated to "Yes".

mlseim

11-16-2010, 08:58 PM

I will have to study it a bit more.

jseth

11-17-2010, 03:17 PM

Ok, I've got it figured out to where the user can check their skills, and it will update the achieved field for that skill record to "Yes"!

On the form processing page, I have it displaying to the screen which skillIDs the user has selected to update, and also code verifying that they have actually updated.

Here's my NEW problem! Only one of the records in the array is updating, not all of them. Here's my code for the input form:

...connect to database...

<table width="800" align="center" border="1">
<td>
<tr>

<th>Skill ID</th>
<th>Skill Description</th>
<th>Select Achieved Skills</th>

</tr>
</td>

<?php
mysql_select_db($database_skills, $skills);

$query_MySkills = "SELECT user_skills.user_skillID, skills.topic, skills.skill, user_skills.tngdate, user_skills.achieved, users.username, DATE_FORMAT(`tngdate`,'%d-%b-%y') AS tngdate
FROM skills, users, user_skills
WHERE users.username = ('$username') AND users.userID=user_skills.userID AND user_skills.achieved != 'N/A' AND user_skills.achieved !='Yes'
AND user_skills.skillID=skills.skillID
AND skills.topic = 'Access'
ORDER BY topic, skill";

$result = mysql_query($query_MySkills) or die(mysql_error());

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

echo '<tr>';
echo '<td align="center">' . ($row['user_skillID']).'</td>';
echo '<td align="left">  ' . ($row['skill']). '</td>';
echo '<td <form method="POST" action="changeskillsrecord.php">
<p align="center"><input type="checkbox" name="ach_skillID[];" value='. ($row['user_skillID']).'>';
echo '</tr>';
}

?>

<br /><br />
<td>
<td>
<td>
<p align="center"><input type="submit" name="formSelect" value="Select"></p>
</form>
</td>
</td>
</td>
</table>
</body>
</html>

and this is the code for the processing form:

...connect to database...

<html>
<head>
<title>Change My Skills Record Form</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link href="Includes/si.css" rel="stylesheet" type="text/css" />
</head>

<body topmargin="5" leftmargin="0">

<table width="980" align="center" border="1">
<td>
<tr>
<th align ="left">
You have selected the following skill(s) as achieved:<br />

<?php

foreach($ach_skillID as $value){
echo $value;
echo "<br />";
}

?>
<?php
mysql_select_db($database_skills, $skills);
$result = mysql_query("UPDATE user_skills SET achieved='Yes' WHERE user_skillID = $value") or die(mysql_error());

echo "The following skill(s) have been updated:";
print "$value";

?>
</body>
</html>

When I select more than one skill, the output of the foreach statement displays all of the records selected, but only the last skill displays in the print "$value" statement, and indeed, only the last record is actually updated in the database.

How do you make all of the records update? Thanks!

djm0219

11-17-2010, 03:26 PM

Something along these lines. You need to do the updating as you go through the check boxes.

<?php
mysql_select_db($database_skills, $skills);

foreach($ach_skillID as $value){

$result = mysql_query("UPDATE user_skills SET achieved='Yes' WHERE user_skillID = $value") or die(mysql_error());

echo "The following skill has been updated: $value <br />";
}

?>

jseth

11-17-2010, 04:11 PM

Hurray!!! Thank you SO much! That's it!!!! Thank you again!