如何遍历数组元素以创建多个插入查询

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.

Answer 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