SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Mar 2006
    Posts
    701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    xml insert to mysql

    I have a xml file that I have to input every hour the values at mysql.
    It is the weather for some locations.
    When I do the insert for the first values the php runs at 2 sec.
    But at update for the locations the php time outs at 60''! And the update runs only for 187 from the 100000+ locations
    Is the update at mysql so slow?What is wrong?
    Dimis

  2. #2
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you able to post your code that does the reading and inserting of the xml into the database?

  3. #3
    SitePoint Guru
    Join Date
    Mar 2006
    Posts
    701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this is a version of the code
    Code:
    <? include("cur_class.php"); ?>
    <? include("liveshop.php"); ?>
    <?php
        
        $file=simplexml_load_file("current.xml");
        $a=new cur_class();
          $query1="truncate    current ";
     $stmt1 = $liveshop->prepare($query1) or die("error".mysqli_error($liveshop));
      $stmt1->execute();
        foreach ( $file->current as $current)
        {     
            try {
           $location =  $current->location;
            $phrase=$current->phrase;
            $temp=$current->temp;
            $aptemp=$current->aptemp;
            $wndchl=$current->wndchl;
            $rhumid=$current->rhumid;
            $wind_dir=$current->wind_dir;
            $windspeed =$current->windspeed;
            $pres=$current->pres;
            $vis=$current->vis;
            $icon=$current->icon;
            $q=$a->beaufort($current->windspeed);
            $A=$q['bay'];
            $b= $q['ypsos'];
              $query="UPDATE   current  set phrase=?,temp=?,aptemp=?,
    wndchl=?,rhumid=?,wind_dir=?,windspeed=?,pres=?,
    vis=?,icon=?,entasi_anemou=?,ypsos_kymatos=?  where location=?;";
            $stmt = $liveshop->prepare($query) or die("error".mysqli_error($liveshop));
           $stmt->bind_param("sddddsdddsdss",$phrase, $temp,$aptemp,$wndchl,$rhumid,$wind_dir,$windspeed,$pres,$vis, $icon,$A,$b,$current->location);
            //   
            
        $stmt->execute();
           }
           catch (Exception $ex)
          { print ("error".mysqli_error($liveshop));}
        }
         $a=null;
        mysqli_close($liveshop);
    ?>

  4. #4
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Dimis.

    Have you tried running the code without the update query to see how long it take to loop through the array?

    so just comment out the following lines:
    Code:
    $query="UPDATE   current  set phrase=?,temp=?,aptemp=?,
    wndchl=?,rhumid=?,wind_dir=?,windspeed=?,pres=?,
    vis=?,icon=?,entasi_anemou=?,ypsos_kymatos=?  where location=?;";
            $stmt = $liveshop->prepare($query) or die("error".mysqli_error($liveshop));
           $stmt->bind_param("sddddsdddsdss",$phrase, $temp,$aptemp,$wndchl,$rhumid,$wind_dir,$windspeed,$pres,$vis, $icon,$A,$b,$current->location);
            //   
            
        $stmt->execute();

  5. #5
    SitePoint Guru
    Join Date
    Mar 2006
    Posts
    701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I take off the execute and the the code is very quick, this code with a stored procedure at mysql that does an insert is 2-3'' job but with a proc with update has a time out.
    The "update" code make some update at a minute but has a time out, it wants more time.
    My solution now is first to delete all data and insert new with the new values.

  6. #6
    SitePoint Member
    Join Date
    Jul 2008
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As long as it works for you


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •