#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    20
    Rep Power
    0

    Inserts failing. Not sure whats happening


    I have PHP that pulls XML data and parses it and basically sticks it into the SQL database based on the XML tag.

    Today it stops at 1010 records(rows) yesterday it stopped at 3000. I need to insert ~29000 records. The only difference between today and yesterday is I commented out a couple of echoes and did an insert delayed. After the delayed stopped at 1010 record, i switched back to the insert without the delay and it still stops at 1010. I can kill and recreate the table and it still does the same.
    I suspect its the database crapping out, but I wouldn't think 29k records would be too many for MYSql.

    Its not the code stopping looping because I can see the totality of the loops completing via echoes that echo the same variable data that is going into the database fields.

    I have tried
    1) insert
    2)insert delayed
    3)limiting the record number and waiting, theoretically, for it to catch up then manually trying to add another 1000 or so. Waiting, rerunning the script for another 1000 records, etc.

    Any ideas? Below is my code - Would this be more appropriate in the php forum?


    PHP Code:
    <?php 
    // create a new cURL resource
    //$maindomain="http://www.ssicat.com/";


    /////////////////VAR NAMES

    $TableName='testtable4';
    $VarCharLength=1000;
    $datevalue='01/01/1990';
    $lastitemvar='1';
    ini_set('max_execution_time'2000);///sets a max execution time of 33 mins
    $counter=0;//tracks how many times thru the loop

    //Open DB
    $link mysqli_connect('localhost''root''password','businessdata');//connect to DB
    if (mysqli_connect_errno())
      {
     
      die(
    "Failed to connect to MySQL: " mysqli_connect_error());
      }

    $maxcounturl="http://webservices.theshootingwarehouse.com/smart/inventory.asmx/DailyItemCount?CustomerNumber=15753&UserName=15753&Password=75035&LastUpdate={$datevalue}&Source=15753";
    $maxiteminitialdata=file_get_contents($maxcounturl);//get the data
    $maxitemcount=simplexml_load_string($maxiteminitialdata);//clean it up - this is the maximum count value
    //Pull the maximum item count  
    //ECHO "maxTItems = {$maxitemcount}<br />\n";

    fnprocessincoming($datevalue,$lastitemvar,$counter,$link,$maxitemcount);

    function 
    fnprocessincoming($datevariable,$lastitem,$inccounter,$sqllink,$fnmaxitemcount)
    {

    $dailyitemupdateurl="http://webservices.theshootingwarehouse.com/smart/inventory.asmx/DailyItemUpdate?CustomerNumber=15753&UserName=15753&Password=75035&LastUpdate={$datevariable}&LastItem={$lastitem}&Source=15753";
    $url_to_work_on=file_get_contents($dailyitemupdateurl);//get the data
    $wrapping_xml=simplexml_load_string($url_to_work_on);//clean it up
    $wrapping_xml=str_replace("&#x1A","",$wrapping_xml);//clean out goofy characters
    $final_data_to_parse=simplexml_load_string($wrapping_xml);
    $child=$final_data_to_parse->children();//Theis gets the children of the main data (i.e, this retruns "TABLE" since its NewDataSet>Table
    $parenttag=$child->getName();//holds the root XML tag name



    foreach($final_data_to_parse->$parenttag as $parent)
        { 
        
    $inccounter=$inccounter+1;
        
        echo 
    "{$inccounter},<br />\n";
        
    //echo "Loop count = {$counter}<br />\n";
        
    foreach($child->children() as $child2)
            { 
    //This goes through TABLE and gets the sub names  
            
    if ( ($value $child2->getName()) == 'ITEMNO' )
                { 
                
    $ITEMNO=$parent->$value;
                
    //echo "-----------------Found My Item Number ! it's ".$parent->$value."<br />\n";      
                
    }  
            
            
    // echo "INSERT INTO testtable5 (ITEMNO, {$value}) VALUES ({$ITEMNO},{$parent->$value})  
            //    ON DUPLICATE KEY UPDATE {$value} = {$parent->$value}";
            
            //echo "<br />\n";
            
            
    $sql_connect=mysqli_query($sqllink,"INSERT INTO testtable5 (ITEMNO, {$value}) VALUES ({$ITEMNO},'{$parent->$value}')  
                ON DUPLICATE KEY UPDATE 
    {$value} = '{$parent->$value}'");        
            
            } 
        } 
    IF(
    $inccounter<$fnmaxitemcount)
        {
        
    fnprocessincoming($datevariable,$inccounter,$inccounter,$sqllink,$fnmaxitemcount);    
        }

    }
    ?>
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    20
    Rep Power
    0
    This most recent run did a little more, it ran 2618.

IMN logo majestic logo threadwatch logo seochat tools logo