Issue with Updating Checkbox PHP/MYSQL

[Nightkon]

Hi All,

 

I am trying to setup a basic Checkbox in PHP which saves to a MYSQL database. I have spent some time coding this myself and have reached a point where I am able to create a Checkbox which is populated from a MYSQL table. The issue is that when a user makes a change to the checkbox on the HTML page and clicks on the save button the change does not get saved correctly (there is no change at all).

 

The Table is called car_checkbox and has the fields FileSerial (Primary Key), 2006 and 2007. I have one row in the table which contains the values AAC, 1, 0

 

The .php files I am using are TESTPHPCHECK.php which contains the code used for updating the Checkbox. TESTCheckbox.php which holds the form and table.  CAR_Checkbox which is the code for the checkbox itself and populates the values stored in the table.

 

I have had no trouble getting my dropdown menus, radio buttons and fields working on my pages. I am not sure what I am doing wrong here so if anyone has any solutions or advice it would be very much appreciated. Thanks in advance.

 

 

TESTPHPCHECK.php

 

<?php
include ('./mysql_connect.php');
$val1 = $_POST['2006'];
$val2 = $_POST['2007'];
$id = $_POST['id'];

$query17 = "UPDATE 'car_checkbox' SET 2006 = '$val1', 2007 = '$val2' WHERE FileSerial= '$id'";
$result17 = mysql_query($query17);
header("Location: TESTCheckbox.php?id=AAC");
mysql_close($link);
?>

 

TESTCheckbox.php

 

<?php include ('./populate.php'); ?>
<?php
$id=$_GET['id'];
$val1=$_GET['2006'];
if ($val1=="on") {
$val1=1;
} else {
$val1=0;
}

$val2=$_GET['2007'];
if ($val2=="on") {
$val2=1;
} else {
$val2=0;
}

?>
<table width='1044' align="left" cellpadding="0" cellspacing="0" >
<form action="TESTPHPCHECK.php?id=$id" method="POST" name='theForm' onsubmit=''>
<input type="hidden" name="id" value="<?php echo $id; ?>">
  <!--DWLayoutTable-->
    <td height="117" colspan="17" valign="top"><INPUT name="image" TYPE="image" class='forminput' value="Save" SRC="Buttons/NavBanner.gif" /> <h3><?php echo $row->current_name; ?></h3></td>

    <td width="11"> </td>


            <tr>
              <td height="13"></td>
              <td></td>
              <td colspan="5" rowspan="2" valign="top" class="edit">CAR Recd. </td>
              <td rowspan="2" align="center" valign="top"><?php include ('CAR_checkbox.php'); ?></td>
              <td></td>
              <td></td>
              <td></td>
              <td></td>
            </tr>
           
</form>
</table>

 

CAR_checkbox.php

 

<?php
include("mysql_connect.php");

  $q="SELECT * FROM `car_checkbox` WHERE `FileSerial` = '$id'";
  $r=mysql_query($q);
  $num=mysql_numrows($r);
   if ($num>0) {
    $val1=mysql_result($r,'0','2006');
    $val2=mysql_result($r,'0','2007');
} else {
  //Else nothing is checked
   $val1=0;
   $val2=0;
   }
//Show form
echo "<input type='hidden' name='id' value='$id'>";
if ($val1=="0") {
echo "<input name='2006' type='checkbox'>2006";
} else {
echo "<input checked='checked' input name='2006' type='checkbox'>2006";
}
if ($val2=="0") {
echo "<input name='2007' type='checkbox'>2007";
} else {
echo "<input checked='checked' input name='2007' type='checkbox'>2007";
}

?>

 

[ethought]

Normally I would use something like this (note one update field per query);

 

include("db.php");

 

$query17 = mysql_query("UPDATE car_checkbox SET 2006='$val1' WHERE FileSerial='$id'", $db);

$query18 = mysql_query("UPDATE car_checkbox SET 2007='$val2' WHERE FileSerial='$id'", $db);

 

Where db.php would look something like this:

 

<?

 

$db = mysql_connect("localhost", "user", "password") or die(mysql_error());

mysql_select_db('database_name', $db);

 

?>

[Nightkon]

Thanks ethought i modified my TESTPHPCheck.php file to (setting up db.php in the way you described):

 

<?php

include("db.php");

$query17 = mysql_query("UPDATE car_checkbox SET 2006='$val1' WHERE FileSerial='$id'", $db);
$query18 = mysql_query("UPDATE car_checkbox SET 2007='$val2' WHERE FileSerial='$id'", $db);

$result17 = mysql_query($query17);
$result18 = mysql_query($query18);
header("Location: TESTCheckbox.php?id=AAC");
mysql_close($link);
?>

 

and also tried:

 

<?php

include("db.php");

$val1 = $_POST['2006'];
$val2 = $_POST['2007'];
$id = $_POST['id'];

$query17 = mysql_query("UPDATE car_checkbox SET 2006='$val1' WHERE FileSerial='$id'", $db);
$query18 = mysql_query("UPDATE car_checkbox SET 2007='$val2' WHERE FileSerial='$id'", $db);

$result17 = mysql_query($query17);
$result18 = mysql_query($query18);
header("Location: TESTCheckbox.php?id=AAC");
mysql_close($link);
?>

 

But the checkbox still does not update properly

[ethought]

hmmm

 

you do not need to run the mysql_queries twice ie:

 

<?php

include("db.php");

$val1 = $_POST['2006'];
$val2 = $_POST['2007'];
$id = $_POST['id'];

$query17 = mysql_query("UPDATE car_checkbox SET 2006='$val1' WHERE FileSerial='$id'", $db);
$query18 = mysql_query("UPDATE car_checkbox SET 2007='$val2' WHERE FileSerial='$id'", $db);
## The above statements run the whole query
## tyr adding some error checking
if(!$query17){
            echo "There is an error with query 17 <br>";
} else {
            echo "Query 17 was successful<br>";
}

if(!$query18){
            echo "There is an error with query 18 <br>";
} else {
            echo "Query 18 was successful<br>";
}

#for more error checking you can try checking your posted variables with
print_r($_POST);

header("Location: TESTCheckbox.php?id=AAC");
mysql_close($link);
?>

[ethought]

also it looks as though you are not sending any values with your posted checkboxes

 

echo "<input name='2006' type='checkbox'>2006";

 

I would have thought should be something like:

 

echo "<input name='2006' type='checkbox' value='2006'>2006";

[Nightkon]

Thanks again for your help. After making the changes you provided i get the following errors

 

There is an error with query 17

There is an error with query 18

Array ( [id] => AAC [2007] => on [image_x] => 477 [image_y] => 40 )

 

Any ideas? I will try playing around some more with the code.

[BlueSkyIS]

change these

 

$query17 = mysql_query("UPDATE car_checkbox SET 2006='$val1' WHERE FileSerial='$id'", $db);
$query18 = mysql_query("UPDATE car_checkbox SET 2007='$val2' WHERE FileSerial='$id'", $db);

 

to these

 

$query17 = mysql_query("UPDATE car_checkbox SET 2006='$val1' WHERE FileSerial='$id'") or die(mysql_error());
$query18 = mysql_query("UPDATE car_checkbox SET 2007='$val2' WHERE FileSerial='$id'") or die(mysql_error());

[Nightkon]

Thanks BlueSky, I'm getting a more detailed error now I have double checked my code and I don't see any simple errors.

 

This occurs when the 2006 checkbox is checked

 

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 '2006='2006' WHERE FileSerial='AAC'' at line 1

 

This is the error when the 2006 checkbox is unchecked

 

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 '2006='' WHERE FileSerial='AAC'' at line 1

[BlueSkyIS]

do you have fields in your table called 2006 and 2007? I would change them to something beginning with text instead of numbers. But barring that, you'll probably have to backtick the field names for fields that are numeric.

 

$query17 = mysql_query("UPDATE car_checkbox SET `2006`='$val1' WHERE FileSerial='$id'") or die(mysql_error());
$query18 = mysql_query("UPDATE car_checkbox SET `2007`='$val2' WHERE FileSerial='$id'") or die(mysql_error());

[Nightkon]

Yes both 2006 and 2007 are fields in the database I will change them to something beginning with text as you advised. The backtick fixed the error message now I’m getting:

 

Out of range value adjusted for column '2006' at row 1

 

[Nightkon]

Also when the 2006 checkbox is unchecked the error is different

 

Incorrect integer value: '' for column '2006' at row 1

[micmania1]

You don't need two queries.

 

$query17 = "UPDATE car_checkbox SET `2006`='$val1', `2007`='$val2'  WHERE FileSerial='$id'");
$result17 = mysql_query($query17);
if (!$result17) {
      echo 'You have an error<br />'.mysql_error().'<br />'.$query17;
} else {
      echo mysql_affected_rows().' rows successfully updated.';
}

 

I think BlueSkyIS was right about changing your field names. I remember trying to name a fieldname numerically and there was an error. Try `year_2006` or whatever suits you. That will probably be the error.

[Nightkon]

Thanks Micmania I have changed my field names as suggested but I'm still receiving the same error.

[micmania1]

I've looked about on google and it seems the error is caused by MySQL strict mode being turned on.

 

http://lists.mysql.com/mysqldoc/106

 

Here's info on turning it off.

http://forum.mambo-foundation.org/showthread.php?t=5254

[Nightkon]

Thanks for that! I would never have figured that out myself now the query updates successfully but it changes the values to 0 in the table even if the checkbox is checked. I will have a play around with some of the code maybe to get it working properly but at least the query is without errors now 

[Nightkon]

I'm still having no luck with getting the Checkbox to update properly but I feel that it is pretty close. I have reposted my code as I have made a few changes. I have realised that when working with the code that creates the Checkbox whatever the value is in this section of code ' if ($val1=="0") ' (in CAR_checkbox //show form) is what the checkbox will be saved as (in this case the value 0 will be saved to the table even if the checkbox was checked). So I don't think that the code is able to determine if the checkbox is checked. I hope this makes sense and thanks for all the help thus far.

 

CAR_checkbox

 

<?php
include("mysql_connect.php");

  $q="SELECT * FROM `car_checkbox` WHERE `FileSerial` = '$id'";
  $r=mysql_query($q);
  $num=mysql_numrows($r);
   if ($num>0) {
    $val1=mysql_result($r,'0','year_2006');
    $val2=mysql_result($r,'0','year_2007');
} else {
  //Else nothing is checked
   $val1=0;
   $val2=0;
   }
//Show form
echo "<input type='hidden' name='id' value='$id'>";
if ($val1=="0") {
echo "<input name='year_2006' type='checkbox' value='year_2006'>2006";
} else {
echo "<input checked='checked' input name='year_2006' type='checkbox' value='year_2006'>2006";
}
if ($val2=="0") {
echo "<input name='year_2007' type='checkbox' value='year_2007'>2007";
} else { 
echo "<input checked='checked' input name='year_2007' type='checkbox' value='year_2007'>2007";
}

?>

 

TESTCheckbox.php

 

I believe the code at the start of TESTCheckbox.php may have something to do with the issue but I have not been able to work it out.

 

<?php include ('./populate.php'); ?>
<?php
$id=$_GET['id'];
$val1=$_GET['year_2006'];
if ($val1=="on") {
$val1=1;
} else {
$val1=0;
}

$val2=$_GET['year_2007'];
if ($val2=="on") {
$val2=1;
} else {
$val2=0;
}
?>
<table width='1044' align="left" cellpadding="0" cellspacing="0" >
<form action="TESTPHPCHECK.php?id=$id" method="POST" name='theForm' onsubmit=''>
<input type="hidden" name="id" value="<?php echo $id; ?>">
  <!--DWLayoutTable-->
    <td height="117" colspan="17" valign="top"><INPUT name="image" TYPE="image" class='forminput' value="Save" SRC="Buttons/NavBanner.gif" /> <h3><?php echo $row->current_name; ?></h3></td>

    <td width="11"> </td>


            <tr>
              <td height="13"></td>
              <td></td>
              <td colspan="5" rowspan="2" valign="top" class="edit">CAR Recd. </td>
              <td rowspan="2" align="center" valign="top"><?php include ('CAR_checkbox.php'); ?></td>
              <td></td>
              <td></td>
              <td></td>
              <td></td>
            </tr>
           

</form>
</table>

 

TESTPHPCHECK.php

 

<?php

include("mysql_connect.php");

$val1 = $_POST['year_2006'];
$val2 = $_POST['year_2007'];
$id = $_POST['id'];

$query17 = mysql_query("UPDATE car_checkbox SET year_2006='$val1' WHERE FileSerial='$id'") or die(mysql_error());
$query18 = mysql_query("UPDATE car_checkbox SET year_2007='$val2' WHERE FileSerial='$id'") or die(mysql_error());
## The above statements run the whole query
## tyr adding some error checking
if(!$query17){
            echo "There is an error with query 17 <br>";
} else {
            echo "Query 17 was successful<br>";
}

if(!$query18){
            echo "There is an error with query 18 <br>";
} else {
            echo "Query 18 was successful<br>";
}

#for more error checking you can try checking your posted variables with
print_r($_POST);


mysql_close($link);
?>