CODEWITHSUNDEEP
phpmysqlsqlxml
user2997497
user2997497

Reputation: 160

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

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)

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?

Upvotes: 1

Views: 2229

Answers (2)

Roy Haskell
Roy Haskell

Reputation: 498

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();

Upvotes: 1

Joel Small
Joel Small

Reputation: 197

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.

Upvotes: 1

Related Questions