php mysql checkbox and textboxes values insert into database

Question

I have a db table called 'ice_flavours' with the columns: 'id', 'flavours', 'date', 'selected'. and another table called 'ice_today' like: 'id', 'flavours', 'date', 'selected'.

Now, on the left of the page I want to call from 'ice_flavours' and print the entries in a form together with a checkbox. On the right of the page it shall say SHOWCASE where I want the todays selected flavours to be shown and they shall just stay in there until the end of today´s date and then automatically be discarded. Also when they were selected to show in SHOWCASE, i want them to not show on the left side.

So, when I select the checkbox next to a flavour, the values 'id', 'flavours', 'date', 'selected' ought to be entered into 'ice_today' but for some reason just always the last row of the ice_flavours table is entered and i get the message "Duplicate entry '0' for key 'PRIMARY'"

Maybe this can all be done by just using a single db table but I havent figured it yet. Can someone help please

edit.php:

<?php ob_start(); 

include_once("dbinfo.inc.php");
include_once("config.php");

if(isset($_POST['submit'])){ 

$selected  = $_POST['selected'];
$id        = $_POST['id'];
$flavour   = $_POST['flavour'];
$date      = $_POST['date'];

if($_POST["submit"] == "submit") {
for($i=0;$i<sizeof($selected);$i++) {
if(!empty($flavour)) {
echo"";

echo "<pre>";
    print_r($_POST);
    echo "</pre>";

$query="INSERT INTO ice_today VALUES('$id','$flavour','$date','$selected[$i]')";
    mysql_query($query) or die(mysql_error());
}
}
echo "Entry added";
}
}
?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>

<head>
<title>Edit</title>
</head>

<body>
<form name="form1" id="form1" method="POST" action="edit.php">

<table border="1" width="200px">
 <td>
  <table border="1" width="200px">

<?php

$result = mysql_query("SELECT * FROM ice_flavours WHERE ('selected' != '1') ORDER BY flavour ASC");
$results = mysql_num_rows($result);

if ($results > 0)
 {
$num=mysql_numrows($result);
 $i=0;
      while ($i < $num)
         {
          $id=mysql_result($result,$i,"id");
          $flavour=mysql_result($result,$i,"flavour");
?>

<tr>
 <td align="center" valign="middle">
<?php echo $flavour; ?>
<input type="text" name="id" id="id" value="<?php echo $id; ?>">
<input type="text" name="flavour" id="flavour" value="<?php echo $flavour; ?>">
<input type="text" name="datum" id="datum" value="<?php echo date('Y-m-d'); ?>">
<input type="checkbox" name="selected[]" id="selected" value="<?php echo '1'; ?>">
</td></tr>

<?php
$i++;
}
?>

<input type="submit" value="submit">

<?php } ?>

</td>
<td>
<table border="1" width="200px">
<tr><td align="center" valign="middle">
SHOWCASE
</td></tr>

<?php
include_once("dbinfo.inc.php");
include_once("config.php");
 $result2 = mysql_query("SELECT * FROM ice_today ORDER BY flavour ASC");
 $results2 = mysql_num_rows($result2);

 if ($results2 > 0)
     {
$num2=mysql_numrows($result2);
 $j=0;
      while ($j < $num2)
         {
          $id=mysql_result($result2,$j,"id");
          $flavour=mysql_result($result2,$j,"flavour");
?>

<tr><td align="center" valign="middle">
<?php echo $flavour; ?>
</td></tr>

<?php
$j++;
}
}
echo '</td></tr></table>';
?>
</body>
</html>

<?php ob_end_flush(); ?>

david brainerd · Answer 1 · 2014-04-09T06:46:57.710

If the field for ID is an autoincrement field, you need to leave it off the insert because the db server will determine the value itself. You should list the fields being inserted, and leave that one off:

$query="INSERT INTO ice_today (flavours, date, selected) VALUES('$flavour','$date','$selected[$i]')";

Listing the fields you are inserting to also prevents insert statements from breaking when you add new fields later. So its good practice to start now.

score 0 · Answer 2 · edited May 23 '17 at 12:05

Please take a look at the MySQL Reference Manual. You need to specify each column in your INSERT statement:

$query="INSERT INTO ice_today(id, flavours, date, selected) VALUES('$id','$flavour','$date','$selected[$i]')";

I also suggest you to not use the original MySQL-extension anymore, as it is deprecated as of PHP 5.5.x. When using another MySQL extension (MySQLi or PDO for instance: mysqli or PDO - what are the pros and cons?) you can also use prepared statements to become safe against SQL injections (which you aren't right now).

POST-values can be manipulated and as you just feed them into your query via string concatenation, you're not safe against them.

score 0 · Answer 3 · answered Apr 09 '14 at 07:23

There are lots of issues in your code.

You text boxes having same name with different values if there are two flavors. Therefore flavor or id will be always from last row.

You can do something like this.

1.Replace your text boxes with this,

<input type="text" name="id_<?php echo $id; ?>" value="<?php echo $id; ?>"> <input type="text" name="flavour_<?php echo $id; ?>" value="<?php echo $flavour; ?>"> <input type="text" name="datum_<?php echo $id; ?>" value="<?php echo date('Y-m-d'); ?>"> <input type="checkbox" name="selected[]" value="<?php echo $id; ?>">

2.Replace your top if block (i.e. after submit)

if (isset($_POST['submit']) && $_POST["submit"] == "submit") {
$selected  = $_POST['selected'];

    for($i=0; $i < sizeof($selected); $i++) {

        $id        = $selected[$i];
        $flavour   = $_POST['flavour_' . $id];
        $date      = $_POST['date_' . $id];


        $query="INSERT INTO ice_today VALUES('$id','$flavour','$date','$id')";
        mysql_query($query) or die(mysql_error());
    }
}

php mysql checkbox and textboxes values insert into database

3 Answers3