Import big CSV file to dependent MySQL tables using PHP with duplicate checking

Question

I'm reading about 50,000+ .csv rows and create insert query. There is one dynamic value in insert query values. It's from SELECT if exists or INSERT if doesn't and get the inserted id. This is just testing purpose, just ignore the security lack.

$multiquery = "";
$lines = file($furl);

foreach ($lines as $line_num => $line) {
    if (($line_num + 1) % 1000 == 0) {
        include('connection.php');
    }

    $cols = split(';', $line);

    $originid = 1;
    $dest = $cols[3];
    $cost = (int)$cols[5];

    // === start: consume a lot of connections ===
    $query = "SELECT id FROM dests WHERE name = '$dest'";
    if (!$dests = mysqli_query($link, $query)) {
        die(json_encode(array("errmsg" => "Selecting existing shipdest. Error: ".mysqli_error($link))));
    }
    if (mysqli_num_rows($dests) > 0) {
        $dest = mysqli_fetch_assoc($dests);
    }
    else {
        $query = "INSERT INTO dests (name) VALUES ('$dest')";
        if (!mysqli_query($link, $query)) {
            die(json_encode(array("errmsg" => "Inserting new dest.")));
        }

        $dest['id'] = mysqli_insert_id($link);
    }
    // === end: consume a lot of connections ===

    $multiquery .= "INSERT INTO packages (id_origin, id_dest, cost) VALUES ($originid, ".$dest['id'].", ".$cost."); ";

    if (($line_num + 1) % 1000 == 0 && !mysqli_multi_query($link, $multiquery)) {
        die(json_encode(array("errmsg" => "Failed at line ".$line_num)));
    }
}

How to merge that PHP block as id_dest value in $multiquery?

you are using multi_query wrong way. It will never work as intended. run separate queries using prepared statements instead. — Your Common Sense, Apr 20 '16 at 13:06
Insert the record in a batches so that no need to open connection for each query. — Sachin I, Apr 20 '16 at 13:13

score -1 · Accepted Answer · answered Apr 20 '16 at 17:09

I end up with this code:

ini_set('max_execution_time', 900); // 15 minutes

$lines = file($furl);

$multiquery = "";
foreach ($lines as $line_num => $line) {
    $cols = split(';', $line);
    $dest = $cols[3];

    $multiquery .= "INSERT IGNORE INTO dests (name) VALUES ('$dest'); ";

    if (($line_num + 1) % 1000 == 0) {
        include('connection.php');
        if (!mysqli_multi_query($link, $multiquery)) {
            die(json_encode(array("errmsg" => "Failed at line ".$line_num." Error: ".mysqli_error($link))));
        }
        $multiquery = "";
    }
}
include('connection.php');
if (!mysqli_multi_query($link, $multiquery)) {
    die(json_encode(array("errmsg" => "Failed at line ".$line_num." Error: ".mysqli_error($link))));
}

$multiquery = "";
foreach ($lines as $line_num => $line) {
    $cols = split(';', $line);

    $originid = 1;
    $dest = $cols[3];
    $cost = (int)$cols[5];

    $multiquery .= "INSERT INTO packages (id_origin, id_dest, cost) VALUES ($originid, (SELECT id FROM dests WHERE name = '$dest'), ".$cost."); ";

    include('connection.php');
    if (!mysqli_multi_query($link, $multiquery)) {  
        die(json_encode(array("errmsg" => "Failed at line ".$line_num." Error: ".mysqli_error($link))));
    }
    $multiquery = "";
}
include('connection.php');
if (!mysqli_multi_query($link, $multiquery)) {
    die(json_encode(array("errmsg" => "Failed at line ".$line_num." Error: ".mysqli_error($link))));
}

Import big CSV file to dependent MySQL tables using PHP with duplicate checking

1 Answers1