PHP Forloop loops through an array and insert current array item into sql database, only one record inserting

Question

I have a script that gets data from a database, then loops through the results from the SELECT query by using a forloop and has a query that inserts the array data into another datbase. However only the first record gets inserted.

I get no errors

Here is the code.

//Get all from job
$getRecords = $connection->prepare("SELECT `CustomerFirstName`,`CustomerLastName`,`CASS_STD_LINE1`,`CASS_STD_LINE2`,`CASS_STD_CITY`,`CASS_STD_STATE`,`CASS_STD_ZIP`,`CustomerCounty`,`CustomerNumber`,`DealNumber`,`TradeIn_1_VIN`,`TradeIn_1_Make`,`TradeIn_1_Model`,`TradeIn_1_Year`,`FrontGross`,`BackGross`,`HoldBackAmount`,`VehicleYear`,`VehicleMake`,`VehicleModel`,`VehicleVIN`,`EntryDate`,`matched`,`notNew` FROM `auth` WHERE `matched` = ?");
$getRecords->execute(array($_POST['jobName']));
$gotRecords = $getRecords->fetchAll(PDO::FETCH_ASSOC);
$getRecords = null;


//Loop Through all records found with matching job name
for($i=0;$i<count($gotRecords); ++$i){
    $rec = $remote->prepare("INSERT INTO `cob_matched_records`(first) VALUES (?)");
    $rec->execute(array($gotRecords[$i]['CustomerFirstName']));

}

you should do this in a single insert statement. not a loop around numerous ones.. — amdixon
– amdixon, Commented Jan 9, 2016 at 6:02
see sql_insert_into_select. just need a simple insert into select .. from .. where .. - and you've already got the select .. from .. where .. part — amdixon
– amdixon, Commented Jan 9, 2016 at 6:10
Is your script doing anything else with $gotRecords? You're selecting a lot of fields but only CustomerFirstName is used "in" the INSERT statement. And what's this: $getRecords = null; before the loop? Can't imagine that the code inserts anything. — VolkerK
– VolkerK, Commented Jan 9, 2016 at 6:12
@amdixon : I don't think that will work (out of the box) because of $connection->.../$remote->... — VolkerK
– VolkerK, Commented Jan 9, 2016 at 6:16

VolkerK · Accepted Answer · 2016-01-09 06:27:17Z

The problem with your script is most likely the line

$getRecords = null;

destroying the array before the code hits your for-loop.
(But that doesn't fit your description "However only the first record gets inserted.". Did you post the actual, unaltered code?)

The point (or one of the points) of prepared statements is that you prepare them once and then execute them multiple times with varying parameters. So, prepare the INSERT statement once before the loop and then execute it within with the current parameter(s):

// assuming PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION, otherwise error handling is missing....
// you might also be interested in http://docs.php.net/manual/en/pdo.begintransaction.php
$stmtSelect = $connection->prepare("SELECT `CustomerFirstName` FROM `auth` WHERE `matched` = ?");
$stmtInsert = $remote->prepare("INSERT INTO `cob_matched_records` (first) VALUES (?)");

$stmtSelect->execute( array($_POST['jobName']) );
foreach( $stmtSelect as $row ) {
    $stmtInsert->execute( array($row['cob_matched_records']) );
}

Collectives™ on Stack Overflow

PHP Forloop loops through an array and insert current array item into sql database, only one record inserting

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related