troubleshooting Question

Insert rows into MySQL from PHP checkbox form

Avatar of willow87
willow87 asked on
PHPMySQL Server
4 Comments2 Solutions353 ViewsLast Modified:
I have a form in a facebook application with checkboxes for users to select their computer skills.  When the form loads, it populates the checkboxes from the database if the user has previously selected some skills.  This aspect of the form works fine.  However, when the user clicks 'submit', it does not insert the new selections to the table.  Another problem is that the previously selected skills are deleted from the table at the time the form loads, when I actually want this to happen only when the form is submitted.  The structure of the table Im trying to insert into is:

SQL> CREATE TABLE lookup_skills (
      id        int  not null auto_increment primary key,
      uid       int,
      skill_id  int );

The table that holds the list of skills is:
SQL> CREATE TABLE const_skills (
      id     int  not null primary key,       
      value  varchar(20) );

I'm obviously connecting to the database OK, and the $user variable is working, because the checkboxes are being pre-populated correctly and the correct rows are being deleted (albeit at the wrong time).  So I think I must be missing something simple regarding the 'insert into' part.  Any help would be greatly appreciated.
<form method="post" action="signup3.php">
<?

$result = mysql_query("select * from const_skills order by value");
$myrow = mysql_fetch_array($result);

?>
<table width="600" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>
<? do {
$skill_id = $myrow["id"];

/* find all records that have already been checked */

$checkedresults = mysql_query("Select * from lookup_skills where uid=$user and skill_id=$skill_id");
$num = mysql_num_rows($checkedresults);

/* if a record has already been checked append "checked" to the end of the input tag in the html */

if ($num==1) {
$skill_id = str_replace($skill_id, "$skill_id checked", $skill_id);
}

printf ("<input type=\"checkbox\" name=\"skills[]\" value=%s>%s</input><br>\n", $skill_id, $myrow['value']);
} while ($myrow = mysql_fetch_array($result));
?>
</td>
</tr>
</table>
<input type="hidden" name="uid" value="<? echo $user; ?>"><br>
<input type="submit" name="Submit" value="Submit">

</form>

<?php

$query = "Delete from lookup_skills Where uid=".$user;
            $result = mysql_query($query);
              
//re-insert items checked
            foreach($_POST['skills'] as $skills) {
                  $query = "Insert into lookup_skills (uid, skill_id) Values ($user, $skills)";
                  $result = mysql_query($query) or die("Query failed : " . mysql_error());
            }
?>
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros