?
Solved

Insert rows into MySQL from PHP checkbox form

Posted on 2009-12-26
4
Medium Priority
?
324 Views
Last Modified: 2013-12-12
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

0
Comment
Question by:willow87
  • 2
4 Comments
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 26123936
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
 

Assisted Solution

by:antz0303
antz0303 earned 248 total points
ID: 26123961
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
 
LVL 17

Accepted Solution

by:
Aleksandar Bradarić earned 252 total points
ID: 26123969
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
 

Author Closing Comment

by:willow87
ID: 31670008
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month17 days, 2 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question