Need to iterate through array to capture all rows - currently only saving the number in the [0] or first record

Question

I could use some help with storing an array. The process defined below is working and saving a record! It saves the record defined in the [0] column. If a zero is there, it saves record one, if a 1 is there it saves record two etc.

Now I need to automate this so it will do that for EACH RECORD as it iterates through. Perhaps a second foreach statement to tie them together? Or a for loop with an integer variable? I have tried many combos and it is still not working.

//0 = feeobject row[]: to keyfield=>val
            //Array ([0] => 0)

foreach($AgencyFeesBasicArray['AgencyFeesGetInfoResult']['FeeObject'][0] as $key=>$data ) {  


            $PaymentID = $AgencyFeesBasicArray['AgencyFeesGetInfoResult']['FeeObject'][0]['PaymentID'];
            $ClientID = $AgencyFeesBasicArray['AgencyFeesGetInfoResult']['FeeObject'][0]['ClientID'];
            $ClientName = $AgencyFeesBasicArray['AgencyFeesGetInfoResult']['FeeObject'][0]['ClientName'];
            $Account_ID = $AgencyFeesBasicArray['AgencyFeesGetInfoResult']['FeeObject'][0]['Account_ID'];
            $State = $AgencyFeesBasicArray['AgencyFeesGetInfoResult']['FeeObject'][0]['State'];  
            $PaymentDate = $AgencyFeesBasicArray['AgencyFeesGetInfoResult']['FeeObject'][0]['PaymentDate'];
            $Amount = $AgencyFeesBasicArray['AgencyFeesGetInfoResult']['FeeObject'][0]['Amount'];
            $CheckID = $AgencyFeesBasicArray['AgencyFeesGetInfoResult']['FeeObject'][0]['CheckID'];
            $CreditorName = $AgencyFeesBasicArray['AgencyFeesGetInfoResult']['FeeObject'][0]['CreditorName'];
            $DRC_ClientID = $AgencyFeesBasicArray['AgencyFeesGetInfoResult']['FeeObject'][0]['DRC_ClientID'];      
            $DRC_TransactionID = $AgencyFeesBasicArray['AgencyFeesGetInfoResult']['FeeObject'][0]['DRC_TransactionID'];

            $sql = "INSERT INTO  tblAgencyFees (PaymentID, ClientID, ClientName, Account_ID, State, PaymentDate, Amount, CheckID, CreditorName, DRC_ClientID, DRC_TransactionID) VALUES ('".$PaymentID."', '".$ClientID."', '".$ClientName."', '".$Account_ID."', '".$State."', '".$PaymentDate."', '".$Amount."', '".$CheckID."', '".$CreditorName."', '".$DRC_ClientID."', '".$DRC_TransactionID."')";
            mysqli_query($connection, $sql);
        }
      //}
    //}
echo $sql;

$res = mysqli_query($connection, $sql);
If( !$res ) exit( mysqli_error($connection) );

The array:

Array ( 
    [AgencyFeesGetInfoResult] => Array ( 
        [FeeObject] => Array ( 
            [0] => Array ( 
                [PaymentID] => 816 
                [ClientID] => 1141 
                [ClientName] => Ortega, Daniel 
                [Account_ID] => 2222100000010717 
                [State] => OK 
                [PaymentDate] => 2019-07-31T00:00:00 
                [Amount] => 8.0000 
                [CheckID] => 10 
                [CreditorName] => 0 
                [DRC_ClientID] => 1195 
                [DRC_TransactionID] => 
            ) 
            [1] => Array ( 
                [PaymentID] => 817 
                [ClientID] => 1141 
                [ClientName] => Ortega, Daniel 
                [Account_ID] => 2222100000010717 
                [State] => OK 
                [PaymentDate] => 2019-07-31T00:00:00 
                [Amount] => 92.0000 
                [CheckID] => 11 
                [CreditorName] => MF 
                [DRC_ClientID] => 1195 
                [DRC_TransactionID] => 
            ) 
            [2] => Array ( 
                [PaymentID] => 847 
                [ClientID] => 1141
                [ClientName] => Ortega, Daniel 
                [Account_ID] => 2222100000010717 
                [State] => OK 
                [PaymentDate] => 2019-08-14T13:21:49.23 
                [Amount] => 195.0000 
                [CheckID] => 13 
                [CreditorName] => MF 
                [DRC_ClientID] => 1195 
                [DRC_TransactionID] => 
            ) 
        ) 
    ) 
)

Take a look at this https://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql — Chris Belyeu, Sep 17 '19 at 16:10

score 1 · Accepted Answer · answered Sep 17 '19 at 16:38

You can do a simple loop over your data and do multiple inserts.

$arrFeeObjects = $AgencyFeesBasicArray['AgencyFeesGetInfoResult']['FeeObject'];

if(count($arrFeeObjects) > 0)
{
    foreach($arrFeeObjects as $intKey => $data)
    {
         $sql = "INSERT INTO  tblAgencyFees (PaymentID, ClientID, ClientName, Account_ID, State, PaymentDate, Amount, CheckID, CreditorName, DRC_ClientID, DRC_TransactionID) 
                VALUES ('". $data['PaymentID'] ."', '". $data['ClientID'] ."', '". $data['ClientName'] ."', '". $data['Account_ID'] ."', '". $data['State'] ."', '". $data['PaymentDate'] ."', '". $data['Amount'] ."', '". $data['CheckID'] ."', '". $data['CreditorName'] ."', '". $data['DRC_ClientID'] ."', '". $data['DRC_TransactionID'] ."')";

         mysqli_query($connection, $sql);
    }
}

That worked with one exception: I am getting a duplicate entry error message. INSERT INTO tblAgencyFees (PaymentID, ClientID, ClientName, Account_ID, State, PaymentDate, Amount, CheckID, CreditorName, DRC_ClientID, DRC_TransactionID) VALUES ('847', '1141', 'Ortega, Daniel', '2222100000010717', 'OK', '2019-08-14T13:21:49.23', '195.0000', '13', 'MF', '1195', '')Duplicate entry '847' for key 'PRIMARY' THIS IS ON THE LAST RECORD - ALL THREE WERE SAVED OK BUT THIS IS DISPLAYING. — Dak, Sep 17 '19 at 16:57

Need to iterate through array to capture all rows - currently only saving the number in the [0] or first record

1 Answers1