PHP Mysqli insert into database with large iterations

Question

Below I have Php code that loops through an array and for each it checks if the value already exists in the database and if not, create it. The code itself is working but the loop itself can be insanely big, maximum of a couple tens thousand iterations.

How can I optimize this code? What to use and how to use. There should be a better way to insert this many times without looping through each individual.

foreach($arr as $value){
    $checkID = mysqli_query($cenn, "SELECT item_id from items WHERE item_id = '$value'");

    if (!$checkID) {
        die("Query '$checkID' failed to execute for some reason");
    }else{

        if (mysqli_num_rows($checkID) > 0) {
            $user = mysqli_fetch_array($checkID);
            echo "item_id" . checkID . "exists already";
        }
        else{
            echo "item_id: '$user_id' doesn't exist<br>";
            $gw2Api = file_get_contents("https://api.guildwars2.com/v2/items/" . $user_id); //12452 30704
            $gw2Api_result = json_decode($gw2Api,true);

            /*Here would be some code to determine values that are being inserted*/

            if (!array_key_exists("description",$gw2Api_result)) {
                $description = 'No description available...';
            } else{
                if($gw2Api_result['description'] === ''){
                    $description = "No description available...";
                } else {
                    $description = $gw2Api_result['description'];
                }
            }

            $insertItem = "INSERT INTO items 
                             (item_id, name, description, 
                              AccountBindOnUse, AccountBound, 
                              last_update
                             ) 
                      VALUES ('$user_id', '$gw2Api_result[name]', '$description', 
                              '$AccountBindOnUse', '$AccountBound', CURRENT_TIMESTAMP)";


            if ($cenn->query($insertItem) === true) {
                echo "New record '$user_id' created successfully";
            } else {
                echo "Error: " . $sql . "<br>" . $cenn->error;
            }
        }
    }
} // end foreach

The question: How to insert many values, new rows, into mysqli database as fast as possible.

You might save some time if you used a prepared statement for your INSERT with Parameter markers. Then the INSERT would only need to be compiled and optimised by the database server once rather than 1000's of times — RiggsFolly
– RiggsFolly, Commented Sep 5, 2015 at 15:36
@RiggsFolly Could you provide some code if possible? Rather new to this all and not quite sure how to begin with what you suggested. — Rien
– Rien, Commented Sep 5, 2015 at 15:42

M0rtiis · Accepted Answer · 2015-09-05 16:50:35Z

1

Just use bulk insert. Collect all the rows for insertion and pass it in one query.

echo 'hi';
if (!empty($arr)) {
    echo 'ok';
    $values             = "'" . implode("', '", $arr) . "'";
    $qExistingItemIds   = mysqli_query($cenn, "SELECT item_id from items WHERE item_id IN($values)");
    $existingItemIds    = [];
    while ($existingItemId = mysqli_fetch_array($qExistingItemIds)) {
        $existingItemIds[] = $existingItemId['item_id'];
    }

    $arr = array_diff($arr, $existingItemIds);

    $inserts = array();

    $i = 0;
    $ic = count($arr);
    foreach ($arr as $value) {
        $i++;
        echo "item_id: $value doesn't exist<br>";

        $gw2Api = file_get_contents("https://api.guildwars2.com/v2/items/" . $value); //12452 30704
        $gw2Api_result = json_decode($gw2Api,true);

        /*Here would be some code to determine values that are being inserted*/

        if (!array_key_exists("description", $gw2Api_result)) {
            $description = 'No description available...';
        } else {
            if ($gw2Api_result['description'] === '') {
                $description = "No description available...";
            } else {
                $description = $gw2Api_result['description'];
            }
        }

        $inserts[] = "
            ('$value', '$gw2Api_result[name]', '$description', '$AccountBindOnUse', '$AccountBound', CURRENT_TIMESTAMP)
        ";

        if ($i == 50 OR $i == $ic) {
            $inserts = implode(",", $inserts);
            $insert = "
                INSERT INTO items
                    (item_id, name, description, AccountBindOnUse, AccountBound, last_update)
                VALUES
                    $inserts
            ";

            if ($cenn->query($insert) === true) {
                echo 'great';
                echo "New records created successfully";
            } else {
                echo "Error: " . $sql . "<br>" . $cenn->error;
            }

            $ic -= 50;
            $i = 0;
            $inserts = array();         
        }
    }
}

so now we have only 2 queries. not thousands

details about bulk insert: http://www.geeksengine.com/database/data-manipulation/bulk-insert.php

edited Sep 5, 2015 at 16:50

answered Sep 5, 2015 at 15:47

M0rtiis

3,7611 gold badge17 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Rien Over a year ago

Your first code block I am not quite sure how to use. It returns me an error with mysqli_fetch_array() expects parameter 1 to be mysqli_result, array given on line while ($existingItemId = mysqli_fetch_array($existingItemIds)) {

M0rtiis Over a year ago

answer fixed. use them one by one

M0rtiis Over a year ago

it can not be the same. unswer updated. just replace your code posted in question with that in answer

M0rtiis Over a year ago

if so, then all data proposed in $arr is already in the table

N.B. Over a year ago

Your solution to the problem is sub-par and will probably be rejected by MySQL due to too large packet. This is solved using prepared statements. I suggest showing the example with that instead of a bulk insert query.

|

RiggsFolly · Accepted Answer · 2015-09-05 21:14:02Z

If you use prepared statement you should reduce the round trips to the database server and only compile and optimise each query once instead of Number_of_inputs * 2 queries. This should reduce the workload.

I would be very interested to know by how much.

$sql = "SELECT item_id from items WHERE item_id = ?";
$db_select = $cenn->prepare($sql);
if ( ! $db_select ) {
    echo $cenn->error;
    exit;
}

$sql_insert = "INSERT INTO items 
                       (item_id, name, description, 
                        AccountBindOnUse, AccountBound, last_update) 
                VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP)";

$db_insert = $cenn->prepare($sql);
if ( ! $db_insert ) {
    echo $cenn->error;
    exit;
}


foreach($arr as $value){
    $db_select->bind_param('i', $value);
    $res = $db_select->execute()
    if ( $res === FALSE ) {
        echo $cenn->error;
        exit;
    }

    if ($db_select->num_rows > 0) {
        // dont bother fetching data we already know all we need to
        $user = $db_select->free();
        echo "item_id $value exists already";
    } else {
        echo "item_id: $value doesn't exist<br>";
        $gw2Api = file_get_contents("https://api.guildwars2.com/v2/items/" . $value); 
        $gw2Api_result = json_decode($gw2Api,true);

        if ( ! array_key_exists("description",$gw2Api_result)
            || $gw2Api_result['description'] === '') {
            $description = 'No description available...';
        } else{
            $description = $gw2Api_result['description'];
        }


        $db_insert->bind_param('issss', $value, $gw2Api_result[name],
                                        $description, $AccountBindOnUse, 
                                        $AccountBound)

        if ($cenn->query($insertItem) === true) {
            echo "New record $value' created successfully";
        } else {
            echo "Error: " . $sql_insert . "<br>" . $cenn->error;
        }
    }
} // end foreach

Collectives™ on Stack Overflow

PHP Mysqli insert into database with large iterations

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related