Problem getting checkbox data into mySQL query

[galahad2]

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:

[PHP]
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>'; }
[/PHP]

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 got as far as this with the form handler script:

[PHP]
<?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();

?>
[/PHP]

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.

[whitespace]

The main problem would seem to be with your foreach() loop - stepping through the submitted Pic[] array. It is important to note with HTML checkboxes that only the checkboxes that are actually checked are submitted with the form data. If nothing is checked then nothing is submitted. Your code is assuming that all the checkboxes are submitted. Also, array indexes start at 0, not 1.

You'll need to make the pic# (1-5) part of the value so you can extract this after the form is submitted, or not use an array at all (you don't need to use an array).

There are some other issues with your script that throw some alarm bells...

You check $row['pic1'] in the IF condition, but then use $pic1 in the statement. I assume this works, but just looking at the script there is no reason that it should.

$pic = $_POST['Pic'];

$_POST['Pic'] might not even exist, so this would trigger an E_NOTICE. Also, you are not using $pic in the code that follows.

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

$updatestockdetails only holds the state of the last "photo deleted". So, if an earlier query failed then your message would still state "success!". $updatestockdetails would also be undefined and trigger an E_NOTICE if no photos are being deleted. Variables should be initialised at the start of your script.

[whitespace]

Just to add to above...

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>'; } 

You should generate your 5 pic controls as part of a loop (set $maxPics = 5 and loop) to avoid repetition.

It looks like you could simply set the value of your checkboxes to the pic number (1..5) - it doesn't look like it needs to be the filename, you don't appear to reference this later? Incidentally, the FOR attribute of the LABEL references the value of the ID attribute of the INPUT, not the NAME (you don't currently have an ID attribute).

Enable full error_reporting at the top of your script (at least whilst developing) to catch any undefined variables and potential pitfalls etc.:

error_reporting(E_ALL | E_STRICT);
ini_set('display_errors','1');

So, something like the following to generate your HTML (untested)...
(Assuming $row['pic1'] is the same as $pic1?)

$htm = '';  // Assign to variable and echo once at the end instead of echo, echo, echo
$maxPics = 5;  // Maybe you need this in other places?
for ($i = 1; $i <= $maxPics; $i++) {
  if (!empty($row['pic'.$i])) {
    // For larger blocks of text/HTML it can be easier to use HEREDOC syntax
    $htm .= <<<EOD
<div class="editpics"><img src="images/carpictures/{$row['pic'.$i]}" width="100"><br>
<label for="pic$i">Delete Photo $i</label><input name="Pic[]" id="pic$i" type="checkbox" class="formfield" value="$i"></div>
EOD;
  }
}
echo $htm;

Then, to check for the checkboxes...

// Note that $_POST['Pic'] might not be set at all if no checkboxes are checked!
$pics = isset($_POST['Pic']) ? $_POST['Pic'] : array();
// The array "key" is meaningless here, the pic# is stored in the value
foreach ($pics as $value) {
  // (NB: Check for errors, probably wise to break on first error)
  $mysqli->query("UPDATE cars SET Pic$value = NULL where carid = $carid");
}