0

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?

Simon Hagmann
  • 131
  • 11
  • this https://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query maybe helps you.. – Raymond Nijland Jun 30 '17 at 15:36
  • Don't create a new PDO object for every query. Create it once when the script starts, and reuse it for all the queries. – Barmar Jun 30 '17 at 16:05
  • Since you're using a class, you can make `$db` a class property, and create it in the class's constructor function. – Barmar Jun 30 '17 at 16:06
  • Ok, I have just changed this, getting the PDO Object with a get method: $stmt = $this->getDb() – Simon Hagmann Jun 30 '17 at 16:21

0 Answers0