I'm using CURL to request large XML Files from an API. To prevent memory leaks I use this CURL option to stream the data and send it to the function curlCallback:
curl_setopt($ch, CURLOPT_WRITEFUNCTION, array($splitter, 'curlCallback'));
In the curlCallback I prepare the incoming XML Stream and call the function below to store every main XML Node in the MySQL Database. Everything works well but:
I want to optimize the efficiency to store the data in the MySQL Database. This is the actual code:
public function processLine($str) {
$prdData = simplexml_load_string($str);
// connect to mysql db
$servername = "localhost";
$username = "";
$password = "";
$dbname = 'temp';
$db = new \PDO('mysql:host=' . $servername . ';dbname=' . $dbname . ';charset=utf8mb4',
$username,
$password,
array(
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_PERSISTENT => false
)
);
try {
$stmt = $db->prepare("INSERT IGNORE INTO Product (PRDNO, DSCRD ,DSCRF, DSCRLONGD, DSCRLONGF, PRTNO, SMCAT, DEL, BNAMD) VALUES (:prdno, :dscrd, :dscrf, :dscrlongd, :dscrlongf, :prtno, :smcat, :del, :bnamd)");
// MySQL Transaction
$db->beginTransaction();
$stmt->bindParam(':prdno', $prdData->PRDNO);
$stmt->bindParam(':dscrd', $prdData->DSCRD);
$stmt->bindParam(':dscrf', $prdData->DSCRF);
$stmt->bindParam(':dscrlongd', $prdData->DSCRLONGD);
$stmt->bindParam(':dscrlongf', $prdData->DSCRLONGF);
$stmt->bindParam(':prtno', $prdData->PRTNO);
$stmt->bindParam(':smcat', $prdData->SMCAT);
$stmt->bindParam(':del', $prdData->DEL);
$stmt->bindParam(':bnamd', $prdData->BNAMD);
$stmt->execute();
$db->commit();
} catch (PDOException $e) {
error_log(date("d.m.Y H:i:s") . ' | ' . $e->getMessage() . PHP_EOL, 3, '/var/www/html/log/import.log');
$db->rollBack();
}
}
How can I optimize this to just send one transaction including for example 100 Rows?