PHP + mySQL when inserting many rows in loop to table my php hungs/freases/

Question

I have some strange problem with inserting rows in loop into mySQL table. Let me show you php code first that I use then I describe some statistic.

I tend to think that it is some mySQL issue, but absolutely no idea what kind of. Max inserts in table per minute? (Can't be max row reached - planty of spase on disk)

 

echo " For character=" . $row[1];
$xml = simplexml_load_file($api_url);
$i=0;
foreach ($xml->result->rowset->row as $value) {
        $newQuery = 'INSERT INTO '.$tableName.' (transactionDateTime, quantity, typeName, price, clientName, station, transactionType, seller) VALUES ("'.$value['transactionDateTime'].'",'.$value['quantity'].',"'.$value['typeName'].'","'.$value['price'].'","'.$value['clientName'].'","'.$value['stationName'].'","'.$value['transactionType'].'","'.$row[1].'")';
        $i++;
        if (!mysqli_query($conn, $newQuery))    {
                die('Error while adding transaction record: ' . mysqli_error($conn));
        } // if END
} // foreach END
echo " added records=" . $i;

I have same data in XML that doesn't change. (XML has something like 1400+ rows that i would insert)

It always inserts different amount of rows. Max amount it inserted was around 800+
If I insert like 10sec delay into foreach loop at $i==400 it will add even less rows. And more delays - less rows.
It never comes to that part of code where mysqli_error($conn)
It never reaches echo " added records=" . $i; part of the code.

Since it alwasy stops on different recors I have to assume nothing wrong with INSERT query. Since it never reaches line after foreach loop echo " added records=" . $i; I also assume XML data wasn't processed by the end of it. If I use another sources of data (another character) where are less records in XML then this code works just fine.

What could possibly be my problem?

Your PHP execution might be timing out. How long does it normally take before the browser stops loading? — scrowler
– scrowler, Commented Nov 17, 2013 at 23:22

Roy Haskell · Accepted Answer · 2013-11-17 23:47:10Z

1

Could be that your firing multiple queries at your SQL server. Better to build a single SQL query via your foreach then fire it once.

Something like this, basically:

$db = new mysqli($hostname, $username, $password, $database);
    if($db->connect_errno > 0) 
    {
        $error[] = "Couldn't establish connection to the database.";
    }
$commaIncrement = 1;
$commaCount = count($result);
$SQL[] = "INSERT INTO $table $columns VALUES"; 
foreach ($result as $value)
{
    $comma = $commaCount == $commaIncrement ? "" : ",";
    $SQL[] = "(";
    $SQL[] =  "'$value[0]'"."'$value[1]'"."'$value[2]'"."'$value[3]'";
    $SQL[] = ")$comma";
    $commaIncrement++;
}
$SQL[] = ";";
$completedSQL = implode(' ',$SQL);
$query = $db->prepare($completedSQL);
if($query)
{
    $db->query($completedSQL)
}
$db->close();

answered Nov 17, 2013 at 23:47

Roy Haskell

4981 gold badge4 silver badges6 bronze badges

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

2 Comments

user2997497 Over a year ago

Very interesting way of coding. I guess I'll have to study it and perhaps take it into use. Though without good knowledge of mySQL I thought that may be whole one thousand of inserts at once it too much? May be grouping inserts by e.g. 100 is already good enough?

scrowler Over a year ago

@RoyHaskell is correct - better to do one query with 1000 statements than 1000 queries with one statement. @user2997497 if you want to break it up you could create seperate queries with a for loop to be sure.

Joel Small · Accepted Answer · 2013-11-17 23:36:16Z

1

Scrowler is right, your php is timing out. As a test, you can add

set_time_limit(0);

to the start of your php script.

WARNING - Don't use this in production or anywhere else. Always set a reasonable time limit for the script.

answered Nov 17, 2013 at 23:36

Joel Small

3072 silver badges5 bronze badges

4 Comments

Joel Small Over a year ago

Could be a problem fetching the XML from the API url, may be adding too much time to the request and timing out the script.

user2997497 Over a year ago

To Sebas, that loop actually takes some time. Didn't do any percise counting but it may take let's say 20sec or may be more.

user2997497 Over a year ago

To Joel, XML comes at once, then I just parse it. In other words I believe that work with XML doesn't take that long. I guess the approach that I use isn't best, perhaps there is more robust way to do it. But what I did so far is: I added set_time_limit(0); as u adwised, which isn't helped yet. Then I simplified query by taking away bracket with column order, anyway I supply data in column order. And it helped - script works now. But I know that problem still exist, need to turn back to it later.

Joel Small Over a year ago

Maybe take it down to smaller data sets and time it specifically. If it's mysql connectivity thats causing the lag, you may have a bit of an issue. I know with some system installs, they have trouble resolving localhost instead of 127.0.0.1, and that adds an extra second to EVERY database connection. You can test by seeing how quickly you can get into the mysql console on your host. If it's quick and easy, your existing config is fine. If it takes a whole second to load (or more), there's your problem. You could also add a flag so you know when xml is received and inserts commenced.

Collectives™ on Stack Overflow

PHP + mySQL when inserting many rows in loop to table my php hungs/freases/

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related