PHP MySqli Inserting

Question

I have this script:

<?php
ini_set('max_execution_time', 0);
ini_set('display_errors','1');
ini_set('default_charset','utf-8');
include("includes/mysqli.php");
$con->set_charset("utf8");


$sql = "INSERT INTO clans(id, clanid, name, badge, status, playercount, score, requiredtrophies, warswon, warslost, warstied, location,warfrequency, exp, level, description, playerjson, lastupdate)
            VALUES ('', ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, now())";
$stmt = $con->prepare($sql); //prepare update statement

$stmt->bind_param('ssisiiiiiissiiss',$clanid,$name,$badge,$status,$playercount,$score,$requiredtrophies,$warswon,$warslost,$warstied,$location,$warfrequency,$exp,$level,$description,$playerarray);

$stmts = $con->prepare("SELECT * FROM activeclans WHERE id > 137439657919 ORDER BY id ASC"); //Prepare select statement
$stmts->execute(); //execute select statement 
$result = $stmts->get_result(); //get select statement results

while ($row = $result->fetch_assoc()) {

    $clanid = $row['id'];
    $clanurl = "http://185.112.249.77:9999/Api/clan?clan=$clanid";
    $jsondata = file_get_contents($clanurl);
    $data = json_decode($jsondata,true);

    if($data['name'] != null){
        $name = $data['name'];
    }else{
        $name = "";
    }

    $badge = $data['badge'];

    if($data['status'] != null){
        $status = $data['status'];
    }else{
        $status = "";
    }

    $playercount = $data['playerCount'];
    $score = $data['score'];
    $requiredtrophies = $data['requiredTrophies'];
    $warswon = $data['warsWon'];
    $warslost = $data['warsLost'];
    $warstied = $data['warsTied'];

    if($data['clanLocation'] != null){
        $location = $data['clanLocation'];
    }else{
        $location = "";
    }

    if($data['warFrequency'] != null){
        $warfrequency = $data['warFrequency'];
    }else{
        $warfrequency = "";
    }

    $exp = $data['exp'];
    $level = $data['level'];
    $description = $data['description'];
    $playerarray = json_encode($data['players']);

    /* Execute update statement */

    $stmt->execute();

}

echo $stmt->affected_rows;

$stmt->close();
$stmts->close();
$con->close();

?>

And it is basically inserting around 157K (157 THOUSAND) rows of data. And the data is quite big as well! You can't check the file_get_contents URL out because the port is open only to localhost.

What is the quickest way to insert all this data? It has been running for almost 24 hours now and done 65K. I did try and use transactions but that didn't work well. It gave my 502 Bad Gateway and therefore I lost a lot of time on the script because it rolled back after adding 3 thousand rows (which was however quite quick!)

Also it is possible that the script may at some point fail and leave some of the varchar fields as null hence I have made it so that they end up as an empty string so that there aren't any mySql errors (I got those exceptions thrown when using transactions)

This is the code I used with the transaction stuff. I'm pretty new to prepared statements. I converted this code from standard queries to prepared today and then tried transactions.

<?php
ini_set('max_execution_time', 0);
ini_set('display_errors','1');
ini_set('default_charset','utf-8');
include("includes/mysqli.php");
$con->set_charset("utf8");


$sql = "INSERT INTO clans(id, clanid, name, badge, status, playercount, score, requiredtrophies, warswon, warslost, warstied, location,warfrequency, exp, level, description, playerjson, lastupdate)
            VALUES ('', ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, now())";
$stmt = $con->prepare($sql); //prepare update statement

$stmt->bind_param('ssisiiiiiissiiss',$clanid,$name,$badge,$status,$playercount,$score,$requiredtrophies,$warswon,$warslost,$warstied,$location,$warfrequency,$exp,$level,$description,$playerarray);

$stmts = $con->prepare("SELECT * FROM activeclans WHERE id > 137439657919 ORDER BY id ASC"); //Prepare select statement
$stmts->execute(); //execute select statement 
$result = $stmts->get_result(); //get select statement results

try{
    $con->autocommit(FALSE);
    while ($row = $result->fetch_assoc()) {

        $clanid = $row['id'];
        $clanurl = "http://185.112.249.77:9999/Api/clan?clan=$clanid";
        $jsondata = file_get_contents($clanurl);
        $data = json_decode($jsondata,true);

        if($data['name'] != null){
            $name = $data['name'];
        }else{
            $name = "";
        }

        $badge = $data['badge'];

        if($data['status'] != null){
            $status = $data['status'];
        }else{
            $status = "";
        }

        $playercount = $data['playerCount'];
        $score = $data['score'];
        $requiredtrophies = $data['requiredTrophies'];
        $warswon = $data['warsWon'];
        $warslost = $data['warsLost'];
        $warstied = $data['warsTied'];

        if($data['clanLocation'] != null){
            $location = $data['clanLocation'];
        }else{
            $location = "";
        }

        if($data['warFrequency'] != null){
            $warfrequency = $data['warFrequency'];
        }else{
            $warfrequency = "";
        }

        $exp = $data['exp'];
        $level = $data['level'];
        $description = $data['description'];
        $playerarray = json_encode($data['players']);

        /* Execute update statement */

        if(!$stmt->execute()){
            throw new Exception("Cannot insert record. Reason :".$stmt->error);
        }
    }
    $con->commit();

}catch (Exception $e) {
    echo 'Transaction failed: ' . $e->getMessage();
    $con->rollback();
}

echo $stmt->affected_rows;

$stmt->close();
$stmts->close();
$con->close();

?>

Thanks :)

Answer 1

        $sql="
            INSERT INTO
                ue_game_alliance_rank_rights
                (
                      rank
                    , `right`   
                )
            VALUES 
        ";

        $insertQuery    = array();
        $insertData     = array();
        foreach ($rights_status['add'] AS $row ) {
            $insertQuery[] = '(?,?)';
            $insertData[] = $rank;
            $insertData[] = $row['id'];
        }

        if (!empty($insertQuery)) {
            $sql .= implode(', ', $insertQuery);
            $stmt = $this->db->prepare($sql);
            $stmt->execute($insertData);
        }
    }

That's an example of the basic technique, you would need to swap the functions for their equivalent mysqli_* functions. For each field having data inserted into it, you need:

$insertData[] = $row['id'];

$row needs to match whatever you've used in your foreach loop and ['id'] needs to be whatever the name of the field is that you're inserting into.

$insertQuery[] = '(?,?)';

You need as many placeholders as fields that you'll be inserting into.

Overall it creates a bulk insert, so you need to have the data to be inserted in an array. Given the amount of data that you're inserting, use transactions, you'll probably need to experiment to see how many rows you can bulk insert at a time before the server complains