Problems adding checkbox data into mySQL query

PHP
1

Hi, I’m having an issue adding data from checkboxes on a form into a MySQL query. I’ve checked a number of examples but they all seem to be slightly different to my setup and nothing I’ve tried so far has worked.

Basically the form displays five photo filenames from a database, so the images are displayed, along with a checkbox that will say “Check here if you want to delete this photo”.

I set up the various checkbox inputs as an array, as recommended:

if (!empty($row['pic1'])) { echo '<div class="editpics"><img src="images/carpictures/' . $pic1 . '" width="100"><br /><label for="Pic[]">Delete Photo 1</label><input name="Pic[]" type="checkbox" class="formfield" value="'.$pic1.'" /></div>'; } 

if (!empty($row['pic2'])) { echo '<div class="editpics"><img src="images/carpictures/' . $pic2 . '" width="100"><br /><label for="Pic[]">Delete Photo 2</label><input name="Pic[]" type="checkbox" class="formfield" value="'.$pic2.'" /></div>'; } 

if (!empty($row['pic3'])) { echo '<div class="editpics"><img src="images/carpictures/' . $pic3 . '" width="100"><br /><label for="Pic[]">Delete Photo 3</label><input name="Pic[]" type="checkbox" class="formfield" value="'.$pic3.'" /></div>'; } 

if (!empty($row['pic4'])) { echo '<div class="editpics"><img src="images/carpictures/' . $pic4 . '" width="100"><br /><label for="Pic[]">Delete Photo 4</label><input name="Pic[]" type="checkbox" class="formfield" value="'.$pic4.'"/></div>'; } 

if (!empty($row['pic5'])) { echo '<div class="editpics"><img src="images/carpictures/' . $pic5 . '" width="100"><br /><label for="Pic[]">Delete Photo 5</label><input name="Pic[]" type="checkbox" class="formfield" value="'.$pic5.'" /></div>'; }

So the above code checks to see if there’s a photo in the database and will display it if there is. The values of the checkboxes are the filenames of each of each of the photos.

So when this is submitted, I want the form handler to check each of these checkbox inputs, and for every one that’s checked, change the field value for that photo to blank. So for example if we no longer want Pic3, we check it in the form, and the form handler changes the database entry for the photo for that record from (e.g) somepicorother.jpg, to just nothing.

I didn’t get very far with the form handler script, so this is clearly incomplete:

if($_POST['deletephotos']) //if the form is submitted
{
  
  $carid = $_POST['_CarID']; //this is to make sure we only edit photos for the specified record
  
   $pic = $_POST['Pic'];


foreach ($_POST['Pic'] as $key => $value) {

$updatestockdetails = $mysqli->query("UPDATE cars SET " ); //Not sure how to complete this query

if ($updatestockdetails) {echo "<p>Selected photos deleted successfully.</p>"; }

}

The various photo fields in the db are called Pic1, Pic2, Pic3, Pic4 and Pic5.

I guess I’m trying to figure a way of only getting the field value changed if the appropriate checkbox has been checked, I can’t figure a way of doing this in the query.

2

Something along the lines of
if ($value) {$updatestockdetails = $mysqli->query(“UPDATE cars SET Pic$key = NULL where carid = $carid”); }

would do the trick, however, I’d look at a prepared query or use of mysqli_real_escape_string function to help reduce the chance of rogue data

4

Thanks, I changed the code so it’s now as below, it doesn’t throw an error but neither does it set the specified Pic fields to null…???

<?php
include ('inc/dbconnect-mysqli.php');

if($_POST['deletephotos'])
{
 
  $carid = $_POST['_CarID'];
  
   $pic = $_POST['Pic'];

foreach ($_POST['Pic'] as $key => $value) {
	if ($value) {
		$updatestockdetails = $mysqli->query("UPDATE cars SET Pic$key = NULL where carid = $carid"); 
		}
}

if ($updatestockdetails) {echo "<p>Selected photos deleted successfully.</p>"; }

}
mysqli_close();

?>

Ignore the italic “POST” for carid above, for some reason the forum formatted that line…

6

Anyone got any ideas why this isn’t working? I’m guessing it’s just not picking up the checked inputs for whatever reason, although it isn’t throwing an error.

7

If you step through the code, is it getting the correct values for $key, $value and $carid, and is it ever running the query? If you run one of the queries directly in MySQL does it work correctly and not throw an error? Have you got error-reporting enabled? Assuming you can’t use xdebug:

<?php
include ('inc/dbconnect-mysqli.php');
$debug = true; // change this when you fix it
if($_POST['deletephotos'])
{
  if ($debug) echo "Got deletephotos"; 
  $carid = $_POST['_CarID'];
  if ($debug) echo $carid;
  $pic = $_POST['Pic']; // this doesn't seem to be used anywhere

  foreach ($_POST['Pic'] as $key => $value) {
    if ($debug) echo $key . " / " . $value;
    if ($value) {
      $query = "UPDATE cars set Pic$key = NULL where carid = $carid";
      if ($debug) echo $query;
      $updatestockdetails = $mysqli->query($query); 
      if ($debug) echo $updatestockdetails;
      }
}

if ($updatestockdetails) {echo "<p>Selected photos deleted successfully.</p>"; }
}
mysqli_close();
?>

You do probably want to look at prepared statements as well, once you have it working.

ETA - One thing you’ll notice when you show the values of $key is that the field numbering on the forms starts at zero, where your field names start at Pic1. So you can’t just use $key for the field name, you’ll have to add one to it.

8

You need to surround your code posts with “code” in square-brackets, rather than “php” as you were (I think someone reformatted for you), the underline character in a normal message turns on italics.

9

Thanks, I’ve actually got it working just by changing the names of the various checkbox inputs from just Pic to Pic where x = 1 to 5.

10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.