I am doing inserts via a loop, unfortunately, it seems to insert only a few of the data and ignore some.
I'm reading the contents of a file and inserting them into a postgres database using PHP.
See my code below.
$source='/Users/gsarfo/AVEC_ETL/TCCDec052016OSU.DAT';
$lines=file($source);
$len =sizeof($lines);
$connec = new PDO("pgsql:host=$dbhost;dbname=$dbname", $dbuser, $dbpwd);
$ins=$connec->query('truncate table tbl_naaccr_staging');
try {
for ($x = 0; $x < $len; $x++) {
$a1=substr($lines[$x],146,9);
$a2=substr($lines[$x],2182,9);
$a3=substr($lines[$x],192,3);
$connec->beginTransaction();
$sql2=$connec->prepare("INSERT INTO tbl_naaccr_staging
(addr,name, email) VALUES (?, ?, ?");
$sql2->execute(array($a1, $a2, $a3));
$connec->commit();
}
$res=$connec->query($sql) ;
}
catch (PDOException $e) {
echo "Error : " . $e->getMessage() . "<br/>";
die();
}
if ($sql2)
{echo 'success';}
?>
It worked, problem was due to unescaped characters in the strings being inserted so pg_escape_string helped clean out the string prior to insert