mysql update query with form array

Question

Multiple posts but I'm still stuck...I'm missing something fundamental here. I have a form with a select:

<select name="camera_status[]">
  <option <?php echo $enabled_option; ?>>Enabled</option>
  <option <?php echo $disabled_option; ?>>Disabled</option>
</select>

This form is built with a loop to give a list of all camera settings. So you would have multiple cameras and their corresponding camera_status. Also I have a hidden input field with the camera_id:

The camera_id is processed with some javascript. Then I process that with:

$camera_id = $_POST['camera_id'];

if (is_array($_POST['camera_status']))
{
  foreach ($_POST['camera_status'] as $camera_status) {
    $query_status = 'UPDATE `#__cameras` SET `camera_status` ="'.$camera_status.'" WHERE `camera_id`='.$camera_id;
    $db->setQuery($query_status);
    $db->query();
  }
}

If I echo the camera_id it is correct. But my foreach runs an update query for the full list of cameras instead of just the one selected. So it updates only the last camera in the list. Let me know if it makes sense to update the full code. Obviously I'm going about this all wrong...

@Dimme single selection. The user will update the form and hit an "Apply" button which I want to then update that record in the DB and the corresponding form. Currently my code will run an update for every camera with the same camera_id — Tom
– Tom, Commented Nov 15, 2011 at 4:06
@StanislavShabalin just some code to fill in the form and tell if the camera is enabled or disabled. It queries the db and loops over to fill in the data for all cameras. — Tom
– Tom, Commented Nov 15, 2011 at 4:06
@MarcB and every other kind of security hole. It is closed code. Step 1 - make it work, step 2 proper validation — Tom
– Tom, Commented Nov 15, 2011 at 4:11

dimme · Accepted Answer · 2011-11-15 04:12:03Z

1

EDIT: Well if you have single selection then it is simpler than that:

HTML:

<select name="camera_status">
  <option value="Enabled">Enabled</option>
  <option value="Disabled">Disabled</option>
</select>

And PHP:

$camera_id = (int) $_POST['camera_id']; //Here you had SQL injection.
$camera_status = mysql_real_escape_string($_POST['camera_status']); //Neither that was protected.

$query_status = 'UPDATE `#__cameras` SET `camera_status` ="'.$camera_status.'" WHERE `camera_id`='.$camera_id;
$db->setQuery($query_status);
$db->query();

edited Nov 15, 2011 at 4:12

answered Nov 15, 2011 at 4:03

dimme

4,4344 gold badges33 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tom Over a year ago

thanks. I'm still stuck because my form where the select exists is completely contained in a loop. So I loop over all this data in the db and create a populated form. When a user changes something in the form and hits apply it should update the proper row. But for some reason it only updates the last camera. Even though it I know I have the right camera_id. Probably hard to explain without the code. =\

dimme Over a year ago

I see... Hard for me to provide you with some code. I could probably help you if I had access to your system. Of course that is impossible. Check if nickb's solution helps, or try to give us some more code.

Tom Over a year ago

I'll add another post. So I can post the whole thing. Thanks.

nickb · Accepted Answer · 2011-11-15 04:12:40Z

You have a fundamental flaw in your thought process for the page. When you output more than one <select name="camera_status[]"> elements, you're going to get that many results back. With two of them, you'll get two values in the array, and so on.

What it sounds like you're doing is outputting a list of cameras, having the user select a camera to modify, and then, from then on, all of the camera settings now only apply to that one specific camera. If this is the case, then you don't need to use arrays for the camera settings, including camera_status. Just remove the array portion and stop outputting more than one HTML element for each camera setting (since you know that once a camera is selected, those values apply to that specific camera).

However, if your page that displays the multiple cameras allows the user to modify every camera and its settings, you'll need to accommodate for the user's input.

If the latter is the case, here's a neat trick - Modify your <select> so it looks like this when you're outputting your camera form:

<select name="camera_status[<?php echo $row['camera_id']; ?>]">
    <option value="1">Enabled</option>
    <option value="0">Disabled</option>
</select>

Now, when you grab $_POST['camera_status'], it'll be an array with the camera IDs as the keys and their selected value as the value. So now, you can do this:

if( is_array($_POST['camera_status']))
{
    foreach ($_POST['camera_status'] as $camera_id => $camera_status) {
        $camera_status = intval( $camera_status); // Be wary of SQL injection
        $camera_id = intval( $camera_id);
        $query_status = 'UPDATE `#__cameras` SET `camera_status` ="'.$camera_status.'" WHERE `camera_id`='.$camera_id;
        $db->setQuery($query_status);
        $db->query();
    }
}

Now this will update every camera with the correct value chosen.

pho · Accepted Answer · 2011-11-15 05:01:42Z

0

Easy enough. Add value attributes to the <option> tags so that each of them will have a value

<select name="camera_status">
  <option value="1" <?php echo $enabled_option; ?>>Enabled</option>
  <option value="0" <?php echo $disabled_option; ?>>Disabled</option>
</select>

Then, in your php, look for that value

foreach ($_POST['camera_status'] as $camera_status) {
    if($camera_status == 1) {
        $query_status = 'UPDATE `#__cameras` SET `camera_status` ="'.$camera_status.'" WHERE `camera_id`='.$camera_id;
        $db->setQuery($query_status);
        $db->query();
    }
}

answered Nov 15, 2011 at 5:01

pho

25.7k8 gold badges48 silver badges75 bronze badges

Collectives™ on Stack Overflow

mysql update query with form array

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related