Update form

jseth · July 19, 2010

I have a database of required skills for employees. There is a many to many relationship between the users table and the skills table, called user_skills. It contains the following fields: auto increment ID called user_skillsID, the foreign keys userID from the users table, and skillID from the skills table, a date field called tngdate and a "set" field called achieved. I have a form (called "updateskills.php") that the employees can open which brings up their un-achieved skills and presents a checkbox for them to check the skills that they can perform. The updateskills.php form action goes to "processskills.php", and when a checkbox is checked and the submit button is clicked, "processskills.php" opens, but nothing has actually happened - no update has occurred. I know that I am not coding this correctly, and I can't seem to get my head around what I need to do to associate the "achieved" checkbox item with the skill it is updating. When I go into phpmyadmin and update the "achieved" field manually, I get $sql = "UPDATE `skills`.`user_skills` SET `achieved` = \'Yes\' WHERE `user_skills`.`user_skillID` = 6191 LIMIT 1;"; but it's the "WHERE" part that I am getting stuck at. How to I associate the `user_skills`.`user_skillID` = 6191 to whatever the user_skillID is of the actual skill that they are clicking the achieved checkbox for?

Thank you in advance for any suggestions.

lemmin · July 19, 2010

When you display the checkbox, you should know what the user_skillID is. If it is based off of just the skillID, you can use that along with the userid in the WHERE clause.

If that doesn't make sense you should post your code that displays the checkbox to the user.

jseth · July 19, 2010

Hi lemmin, thanks for your reply. Your reply almost makes sense, which is why I am having trouble - if it made sense, I'd be able to understand it! So here's my code, which I'm sure is incorrect. This brings up a page with the user's unachieved skills, with a checkbox in the Achieved column. My apologies in advance for its ugliness:

<?php

session_start();

$fname = $_SESSION['fname'];

$lname = $_SESSION['lname'];

$username = $_SESSION['username'];

$user_skillID = $_POST['user_skillID'];

$achieved = $_POST['achieved'];

require 'Includes/Header.php';

require_once('Connections/skillsdb.php');

?>

<html>

<head>

<title>Update My Skills</title>

</head>

<?php

mysql_select_db($database_skills, $skills);

$query_MySkills = "SELECT skills.topic, skills.skill, user_skills.skillID, user_skills.user_skillID, user_skills.tngdate, user_skills.user_skillID, user_skills.achieved, users.username

FROM skills, users, user_skills

WHERE users.username = ('$username') AND users.userID=user_skills.userID AND user_skills.achieved != 'Yes' AND user_skills.achieved != 'N/A'

AND user_skills.skillID=skills.skillID

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($skills, $query_MySkills);

mysql_free_result($result);

?>

<td>

<tr>

<th>

<?php

echo $fname;

echo",";

echo" ";

//echo $username;

?>

</b> put a check in the checkbox next to the skills you can perform, then click the Submit button.

</p><br />

</th>

</td>

</tr>

</table>

<td>

<tr>

<th>Skill ID</th>

<th>Topic</th>

<th>Skill Description</th>

<th>Achieved</th>

</tr>

</td>

<?php

while($row_MySkills = mysql_fetch_assoc($MySkills))

{

echo '<tr>';

echo '<td align="center"> ' . ($row_MySkills['user_skillID']).'</td>';

echo '<td align="left"> ' . ($row_MySkills['topic']).'</td>';

echo '<td align="left"> ' . ($row_MySkills['skill']). '</td>';

echo '<td align="center"><input type="checkbox" name="achieved" value="Yes">' . ($row_MySkills['achieved']). '</td>';

echo '</tr>';

}

?>

<tr>

<td>

</td>

</tr>

</form>

</table>

<td>

<tr>

</th>

</tr>

</td>

</table>

</body>

</html>

*********************************************************************

Just for fun, here is the code that is supposed to update the database:

<?php

session_start();

$fname = $_SESSION['fname'];

$lname = $_SESSION['lname'];

$user_skillID = $_POST['user_skillID'];

$achieved = $_POST['achieved'];

$username = $_SESSION['username'];

require 'Includes/Header.php';

require_once('Connections/skillsdb.php');

?>

<html>

<title>Process Updates</title>

<head>

</head>

<body>

<tr>

<?php

$sql = "UPDATE user_skills SET achieved = \'Yes'\ WHERE users.username = ('$username') AND users.userID=user_skills.userID;";

$result = mysql_query($sql);

if ($result){

print "<br>Thank you for updating your skills.\n";

} else {

print "<br>There was a problem with the database. \n";

}

?>

</td>

</tr>

</table>

</body>

</html>

No surprise, I always get "There was a problem with the database."

lemmin · July 19, 2010

Add a hidden input with the user_skillID as its value so that it is passed to your processskills.php file.

<input type="hidden" name="user_skillID" value="'.$row_MySkills['user_skillID'].'"/>

Your processskills.php file is actually already set up to accept the new hidden input so all you have to do is add it to the form that has the checkbox and I think it should work from there.

jseth · July 19, 2010

Sorry to be so dense... can you tell me exactly where I should add it to the form that has the checkbox (updateskills.php)? I tried adding it before echo '<td align="center"> ' . ($row_MySkills['user_skillID']).'</td>'; but that didn't work. Also added it right before <input type="submit" name="Submit" value="Submit"/>, but I still get the "Trouble with database" message. I should change the message to say "Trouble with programmer"...

lemmin · July 19, 2010

It can go anywhere between the <form> and </form> tags; however, it needs to be output from PHP. So, if you put it above this line: "echo '<td align="center"> ' . ($row_MySkills['user_skillID']).'</td>';." you need to use "echo."

echo '<input type="hidden" name="user_skillID" value="'.$row_MySkills['user_skillID'].'"/>';
echo '<td align="center">  ' . ($row_MySkills['user_skillID']).'</td>';

Also, what exactly is the error you are getting? I don't think I have ever seen an error with "Trouble with database" in it.

jseth · July 19, 2010

I placed the code above the line you mention, yet it does not update the database. The nonsensical error is in the processskills.php page:

<?php

$sql = "UPDATE user_skills SET achieved = \'Yes'\ WHERE users.username = ('$username') AND users.userID=user_skills.userID;";

$result = mysql_query($sql);

if ($result){

print "<br>Thank you for updating your skills.\n";

} else {

print "<br>There was a problem with the database. \n";

}

?>

Apparently I keep getting the "else", since I never get "Thank you for updating your skills." When I check the database, indeed there had been no update.

I think I see why... this isn't what we want: WHERE users.username = ('$username') AND users.userID=user_skills.userID;"; We want something that references the code you just wrote for me, like WHERE user_skillsID = "'.$row_MySkills['user_skillID'].'"; - but it doesn't like it coded that way. Am I getting warmer?

Pikachu2000 · July 19, 2010

Change the query execution to: $result = mysql_query($sql) or die( 'Query failed ' . $sql . '<br />With error: ' .mysql_error() ); and see what, if any, errors you're getting.

jseth · July 19, 2010

I get:

Query failed UPDATE user_skills SET achieved = \'Yes'\ WHERE users.userID=user_skills.userID;

With error: 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 '\'Yes'\ WHERE users.userID=user_skills.userID' at line 1

I changed the code to read:

$sql = "UPDATE user_skills SET achieved = \'Yes'\ WHERE user_skills.user_skillID = ' .$row_MySkills['user_skillID'] . '";

which I think is what I'm actually trying to do, but I just get the white screen, so obviously I have the code really wrong.

(I did watch the "don't be the kid in this video" video - I'm REALLY trying not to!)

jseth · July 19, 2010

Ok, too strange. I changed the code to read:

$sql = "UPDATE user_skills SET achieved = 'Yes' WHERE user_skills.user_skillID = '$user_skillID'";

The database updated, but not for the skill that I checked. It updated for the last skill in the list. For example, I clicked the first skill in the list (user_skillID #6191), and the last skill in the list (user_skillID #6283) is the one that got updated.

We're getting close... I appreciate all of your help, by the way.

Pikachu2000 · July 19, 2010

OK, now just put in an echo $sql; after the line that assigns $sql its value. See if the query contains the values you'd expect it to contain.

jseth · July 20, 2010

Good morning!

echo $sql contains the following:

UPDATE user_skills SET achieved = 'Yes' WHERE user_skills.user_skillID = '6284'

That's nice and all, except that I clicked the Achieved checkbox corresponding to skillID 6191 (the first skill in the list), and 6284 is the last skill in the list.

Also, if I click more than one checkbox, still only one row updates.

jseth · July 20, 2010

I added the following to my processskills.php code:

if(isset($_POST['Submit']))

{

for ($i=0; $i<count($_POST['user-skillID']);$i++){

echo "<br />value $i=".$_POST[.user_skillID'][$id];

and what I get is:

value 0=6

UPDATE user_skills SET achieved = 'Yes' WHERE user_skills.user_skillID = '6284'

I have no idea what the "6" refers to. From what I've been reading, it appears that I need to make give Achieved checkbox an individual identity, which I would like to have the user_skillID be, but, once again, no idea how to do that. So it's still updating the wrong record (the last record in the array).

Pikachu2000 · July 20, 2010

All of the checkboxes have the same name, so only the value of last one checked will be in the $_POST array. You need to make them an array of checkboxes, like so:

<input type="checkbox" name="achieved[]" value="Yes">'

jseth · July 20, 2010

I understand your response. It makes sense that it should work. Except that it isn't working. It's still only updating the last record. The echo $sql says the same thing. Here is all my (horribly ugly) code. Perhaps there is something somewhere that is conflicting with a successful outcome:

updateskills.php:

<?php

session_start();

$fname = $_SESSION['fname'];

$lname = $_SESSION['lname'];

$username = $_SESSION['username'];

$user_skillID = $_POST['user_skillID'];

//$achieved = $_POST['achieved'];

require 'Includes/Header.php';

require_once('Connections/skillsdb.php');

?>

<html>

<head>

<title>Update My Skills</title>

</head>

<?php

mysql_select_db($database_skills, $skills);

$query_MySkills = "SELECT skills.topic, skills.skill, user_skills.skillID, user_skills.user_skillID, user_skills.tngdate, user_skills.user_skillID, user_skills.achieved, users.username

FROM skills, users, user_skills

WHERE users.username = ('$username') AND users.userID=user_skills.userID AND user_skills.achieved != 'Yes' AND user_skills.achieved != 'N/A'

AND user_skills.skillID=skills.skillID

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($skills, $query_MySkills);

mysql_free_result($result);

?>

<td>

<tr>

<th>

<?php

echo $fname;

echo",";

echo" ";

//echo $username;

?>

</b> put a check in the checkbox next to the skills you can perform, then click the Submit button.

</p><br />

</th>

</td>

</tr>

</table>

<td>

<tr>

<th>Skill ID</th>

<th>Topic</th>

<th>Skill Description</th>

<th>Achieved</th>

</tr>

</td>

<?php

while($row_MySkills = mysql_fetch_assoc($MySkills))

{

echo '<tr>';

echo '<input type="hidden" name="user_skillID" value="'.$row_MySkills['user_skillID'].'"/>';

echo '<td align="center"> ' . ($row_MySkills['user_skillID']).'</td>';

echo '<td align="left"> ' . ($row_MySkills['topic']).'</td>';

echo '<td align="left"> ' . ($row_MySkills['skill']). '</td>';

echo '<td align="center"><input type="checkbox" name="achieved[]" value="Yes">' . ($row_MySkills['achieved']). '</td>';

echo '</tr>';

}

?>

<tr>

<td>

</td>

</tr>

</form>

</table>

</body>

</html>

processskills.php:

<?php

session_start();

$fname = $_SESSION['fname'];

$lname = $_SESSION['lname'];

$user_skillID = $_POST['user_skillID'];

//$achieved = $_POST['achieved'];

$username = $_SESSION['username'];

require 'Includes/Header.php';

require_once('Connections/skillsdb.php');

?>

<html>

<title>Process Updates</title>

<head>

</head>

<body>

<tr>

<?php

mysql_select_db($database_skills, $skills);

$sql = "UPDATE user_skills SET achieved = 'Yes' WHERE user_skills.user_skillID = '$user_skillID'";

echo $sql;

echo "<br>";

$result = mysql_query($sql) or die( 'Query failed ' . $sql . '<br />With error: ' .mysql_error() );

if ($result){

print "<br>Thank you for updating your skills.\n";

} else {

print "<br>There was a problem with the stupid programmer. \n";

}

?>

<p>

<a href="http://webapps.eastech.org/skills/updateskills.php">Update more skills.</a><br><br>

<a href="http://webapps.eastech.org/skills/achievedskills.php">Return to view your achieved skills.</a></p><br>

</td>

</tr>

</table>

</body>

</html>

Sign In

Update form

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information