phppostgresqlplpgsqlphp-pgsql

Looping through Inserts - PHP Postgres Insert


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';}
?>

Solution

  • 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