Insert rows into MySQL from PHP checkbox form

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());
            }
?>

Open in new window

willow87Asked:
Who is Participating?
 
Aleksandar BradarićConnect With a Mentor Software DeveloperCommented:
The code for inserting new skills actually works for me. The problem is that you've put it after the code which displays the checkboxes :) Move it in front of it and it will work just fine :) Below is the modified code - hope it works better now :)
<form method="post" action="signup3.php">
<?
  # Pressed the Submit button?
  if($_POST['Submit']) {
    $query = "Delete from lookup_skills Where uid=".$user;
    $result = mysql_query($query);

    # Any skills checked?
    if($_POST['skills']) {
      foreach((array)$_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());
      }
    }
  }

  $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>

Open in new window

0
 
Aleksandar BradarićSoftware DeveloperCommented:
First of all the "Delete from lookup_skills ... " SQL is executed on each call. Try putting it inside an if() so it's being call only after submitting the form, like this:

I'll get back to you about the other issue...
if($_POST['Submit']) {
  $query = "Delete from lookup_skills Where uid=".$user;
  $result = mysql_query($query);
}

Open in new window

0
 
antz0303Connect With a Mentor Commented:
1. Not inserting the records upon submission.
When you click submit, the form is submitted to signup3.php, but not posted back to this php itself.
I am not sure what signup3.php does.

2. Deleting on load of the page
It is because the lower part of the php script is run.
So the delete query is executed. (and so is the insert query).

My suggestions:
1. Use signup3.php to do the delete and insert. i.e. moving the lower part of the php script to signup3.php
OR
2. Do the delete and insert in the same page.
But check if $_POST['skills'] is set, i.e. if (isset($_POST['skills'])) { // do delete and insert }

HTH
0
 
willow87Author Commented:
Guys - thank you both very much.  I used the full code from leannonn, and antz0303 - posting the page back to itself was the final piece in the puzzle.  Will eventually put the code on signup3.php as suggested.  Im a little embarressed at how simple it seems now, but probably would never have got there on my own.  Very, very happy!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.