MySQL foreach INSERT when I have an array from checkboxes.

Question

I have a list of checkboxes that I want to insert into a database, which I have processed as an array:

<input name="my_array[]" value="<?php echo $data['ID'] ?>" type="checkbox">
<?php echo $data['name'] ?><?php echo "br/>";

I'm getting my list of checkboxes from my database, and the selected checkboxes are being posted like so:

   $strFoo=$_POST['my_array'];

    $strBar = "";


    foreach ($strFoo as $strFooName) {

    $strBar .= $strFooName . ", ";
    }
    $strBar = substr($strBar, 0, -2);

Echoing, I get my list of ID's selected like so 1, 2, 3, 4

My below foreach is unfortunately only inserting the first ID of the array only..

foreach ($strFoo as $strFooName) {

        $strSql="INSERT INTO table (ID) 
                 VALUES ('$strBar')";
    }

How can I insert each ID into my table?

Marc B · Accepted Answer · 2011-11-09 14:25:27Z

1

It'd have to be:

INSERT INTO table (ID) VALUES (1), (2), (3), (4)

so your processing step would need to be

$values = array();
foreach ($strFoo as $strFooName) {
     $values[] = '(' . intval($strFooName) . ')';
}

$strBar = implode(',', $values);

Note the addition of intval - that ensures that only valid numbers get inserted into the SQL statement. Your version was vulnerable to SQL injection attacks.

answered Nov 9, 2011 at 14:25

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

4 Comments

SMacFadyen Over a year ago

What would the INSERT statement by with the (1), (2), etc?

Marc B Over a year ago

That's mysql's "extended" insert syntax. each (#) becomes a new record in the table you're inserting to.

SMacFadyen Over a year ago

I see what your saying, but won't that only insert the first 4 INT's selected?

Marc B Over a year ago

The loop should generate (#) sets for every checkbox selected in th form, since it's looping on strFoo, and that's just a copy of $_POST['my_array']. It'll insert as many checkboxes are selected. It will break if NO checkboxes are selected, however.

Vikk · Accepted Answer · 2011-11-09 14:25:50Z

0

$strSql="INSERT INTO table (ID) VALUES ('$strBar')";

should be

$strSql="INSERT INTO table (ID) VALUES ('$strFooName')";

answered Nov 9, 2011 at 14:25

Vikk

3,3833 gold badges25 silver badges25 bronze badges

Comments

Fatih Donmez · Accepted Answer · 2011-11-09 14:26:26Z

0

You're looping in the array with $strFooName but you're inserting $strBar which is updated just once above.. Maybe it's the problem

answered Nov 9, 2011 at 14:26

Fatih Donmez

4,3473 gold badges36 silver badges48 bronze badges

Comments

Ben Swinburne · Accepted Answer · 2011-11-09 14:27:30Z

0

From 12.2.5. INSERT Syntax

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

That said, however. It looks like you're trying to just loop straight through your post array with this piece of code

foreach ($strFoo as $strFooName) {
        $strSql="INSERT INTO table (ID) VALUES ('$strBar')";
}

$strBar in that example hasn't been created. You might try

foreach ($strFoo as $strFooName) {
        $strSql="INSERT INTO table (ID) VALUES ('$strFooName')";
}

Although you should also look into preventing SQL injection if you're going to do that.

answered Nov 9, 2011 at 14:27

Ben Swinburne

26.6k10 gold badges75 silver badges113 bronze badges

2 Comments

Ben Swinburne Over a year ago

Is the code in your question two different attempts, or does the first block of code influence the second?

SMacFadyen Over a year ago

Not sure what you mean by that, I've just changed the $variable it's inserting.

Collectives™ on Stack Overflow

MySQL foreach INSERT when I have an array from checkboxes.

4 Answers 4

4 Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related