How to loop through array elements to create multiple insert queries

Question

I have a $_POST array which currently takes the following form:

 ["Area"]=> array(2) { 
    [0]=> string(5) "Title" 
    [1]=> string(5) "Title" 
    } 
 ["Issue"]=> array(2) { 
    [0]=> string(3) "111" 
    [1]=> string(7) "2222222" 
    } 
 ["Elevation"]=> array(2) { 
    [0]=> string(8) "11111111" 
    [1]=> string(7) "2222222" 
    } 
 ["Fix"]=> array(2) { 
    [0]=> string(8) "11111111" 
    [1]=> string(6) "222222" 
    } 
 ["ExpectFee"]=> array(2) { 
    [0]=> string(8) "11111111" 
    [1]=> string(5) "22222" 
    } 
 ["Outlay"]=> array(2) { 
    [0]=> string(9) "111111111" 
    [1]=> string(9) "222222222" 
    } 
 ["ExpctTime"]=> array(2) { 
    [0]=> string(9) "111111111" 
    [1]=> string(11) "22222222222" 
 } 
 ["Checkbox"]=> array(2) { 
    [0]=> string(12) "111111111111" 
    [1]=> string(11) "22222222222" 
    }

I am currently looping through it like this...

 if ($_POST['OthProb']['Issue'] != '') {
       $table = 'tbl_customproblems';
       $kv = array();

            foreach ($_POST['OthProb'] as $array) {
                foreach ($array as $value) {
                    $kv[] = "'".$value."'";

            }
            $string = "INSERT INTO $table (AccountID, myID, Area, Issue, Elevation, Fix, ExpectFee, Outlay, ExpctTime, Checkbox) VALUES ('$_POST[AccountID]', '$_POST[myID]', ".join(", ", $kv).")";     
        }

} else {
  $string = $_SERVER['QUERY_STRING'];
}

$sql = $DBH->prepare($string);
$sql->execute();

Which almost works! It produces this...

"INSERT INTO tbl_customproblems (AccountID, PropertyID, Area, Issue, Elevation, Fix, ExpectFee, Outlay, ExpctTime, WHCheckbox) VALUES ('81', '81', 'Title', 'Title', '111', '2222222', '11111111', '2222222', '11111111', '222222', '11111111', '22222', '111111111', '222222222', '111111111', '22222222222', '111111111111', '22222222222')"

How do I amend my loop to produce seperate inserts, one for each row being passed.

you're misusing prepare(). $_POST values should go into execute(). And what's up with $_SERVER['QUERY_STRING'] ? That's not a SQL query :) — nice ass
– nice ass, Commented Jun 20, 2013 at 15:23
consider me to be completely self taught. Can you provide a link to clarify please, cos I have no idea what you just said. — Funk247
– Funk247, Commented Jun 20, 2013 at 15:25
I have to admit, I've tried to copy and amend something that I've already got in place and functioning correctly for a different type of array. Have only been programming for a few months, am completely self taught and still have some way to go before I get my head round everything. — Funk247
– Funk247, Commented Jun 20, 2013 at 15:31
Im definitely getting the expected values, I just don't know what I'm doing with them :P, 'OthProb' is like a box that contains an load of folders (column names) and their associated files (values). I'm passing an awful lot of stuff so I've do this as I can then use the box name to perform actions on its contents. — Funk247
– Funk247, Commented Jun 20, 2013 at 15:46

Perry · Accepted Answer · 2013-06-20 15:49:38Z

1

It has to be something like this:

if ($_POST['OthProb']['Issue'] != '') {
$table = 'tbl_customproblems';
$string = 'INSERT INTO $table (AccountID, myID, Area, Issue, Elevation, Fix, ExpectFee, Outlay, ExpctTime, Checkbox) VALUES (:AccountID, :myID, :Area, :Issue, :Elevation, :Fix, :ExpectFee, :Outlay, :ExpctTime, :Checkbox)';

$sql = $DBH->prepare($string);

$i = 0;
foreach ($_POST['OthProb'] as $array) {

    $sql->bindParam(':AccountID', $_POST['AccountID'], PDO::PARAM_INT);
    $sql->bindParam(':myID', $_POST['myID'], PDO::PARAM_INT);
    $sql->bindParam(':Area', $array['area'][$i], PDO::PARAM_STR); //it can also be PDO::PARAM_STR

        $sql->execute();
        $i++;
    }
}

I didn't bind all params so you have to do that your self, I hope you get the idea of a prepare statement by this example.

At a prepare statement you use PDO::PARAM_INT when you want a integer and you will be using PDO::PARAM_STR for strings. When you are not sure if it is a integer or a string you better use PDO::PARAM_STR

edited Jun 20, 2013 at 15:49

answered Jun 20, 2013 at 15:38

Perry

11.7k2 gold badges30 silver badges38 bronze badges

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

1 Comment

Funk247 Over a year ago

Thanks for explaining how bound variables work, I feel a bit clearer on that now, but that code I isn't looping through the array properly and is doing 8 insert statements (one for each column in the array) I just want to loop through the first row, do an insert, then loop through the second row, do an insert.

Funk247 · Accepted Answer · 2013-06-21 09:48:34Z

I got it in the end using the following code, its probably hacky to the max and I intend to refine it in the future but it works.

if ($_POST['OthProb']['Issue'] != '') {
       $kv = array();
       // This will help control the array index
       $i = 0;
       // count the number of records in the array
       $count = count($_POST['OthProb']['Area']);
       $table = 'tbl_customproblems';

       // Loop through each index - stop before we reach the value of count.
       for ($i=0; $i < $count; $i++) {
           // Catch the data
           foreach ($_POST['OthProb'] as $value) {
                    $kv[] = "'".$value[$i]."'";       
            }
        // Do the insert!
        $string = "INSERT INTO $table (AccountID, PropertyID, Area, Issue, Elevation, Fix, ExpectFee, Outlay, ExpctTime, Checkbox) VALUES ('$_POST[AccountID]', '$_POST[PropertyID]', ".join(", ", $kv).") ";
        $sql = $DBH->prepare($string);
        $sql->execute();
        // Unset data value to prevent retention
        unset ($kv);  
        }

Collectives™ on Stack Overflow

How to loop through array elements to create multiple insert queries

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related