Script working very slow

[calle81]

It takes about 5 min to run this script... is this normal?

<?php
$con = mysql_connect('mysql.cjhweb.se', 'web30414_cjhweb', 'xxx');
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

$db_selected = mysql_select_db('web30414_cjhweb', $con);
if (!$db_selected) {
    die ('Can\'t use foo : ' . mysql_error());
}
 $sql = 'TRUNCATE TABLE `Games`'; 

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }

 $sql = 'TRUNCATE `Games`';
 $sql = 'TRUNCATE `Odds`';

   if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }

$dom = new DomDocument;
$dom->load('http://odds.expekt.com/exportServlet');
$dom->preserveWhiteSpace = false;
$note = $dom->getElementsByTagName('game');

foreach ($note as $value) {
 $description = $value->getElementsByTagName('description')->item(0)->childNodes->item(2)->nodeValue; 
 $category = $value->getElementsByTagName('category')->item(0)->nodeValue;
	$date= $value -> getAttribute('date');
	$time= $value -> getAttribute('time');
$description = str_replace("'", '', $description);
$description = str_replace("ü", 'u', $description);
$category = str_replace("Handball WC Women", 'Handbolls VM Damer', $category);
$category = str_replace("Curling EC Women", 'Curling EM Damer', $category);
$description = str_replace("Neutral Ground", '', $description);
$description = str_replace("<BR/><span></span>", '', $description);
$description = str_replace("<BR/><span>", '', $description);
$description = str_replace("<span>", '', $description);



   if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }

$sql = "INSERT IGNORE INTO `web30414_cjhweb`.`Games` (`GameID`, `Game Name`, `Game Date`, `Game Time`,`League Name`,`Odds_1`,`Odds_X`,`Odds_2`) VALUES (NULL, '$description','$date','$time','$category','','','');"; 

   if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }

$alt    = $value->getElementsByTagName('alternative');
$length = $alt->length;
for ($index = 0; $index < $length; $index++) {
    $odds = $alt->item($index)->attributes->getNamedItem('odds')->nodeValue;
    $text = $alt->item($index)->nodeValue;
	$text = str_replace("'", '', $text);


$sql = "INSERT INTO `web30414_cjhweb`.`Odds` (`OddsID`, `Game Name`, `Odds Name`, `Odds`) VALUES (NULL, '$description','$text','$odds');"; 
   if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }


}     $sql = "
UPDATE `Games`,`Odds` SET `Games`.`Odds_1`=`Odds`.`Odds` WHERE `Games`.`Game Name` = `Odds`.`Game Name` AND `Odds`.`Odds Name` = '1'"; 
    if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  } 

  $sql = "
UPDATE `Games`,`Odds` SET `Games`.`Odds_X`=`Odds`.`Odds` WHERE `Games`.`Game Name` = `Odds`.`Game Name` AND `Odds`.`Odds Name` = 'X'"; 
    if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  } 

  $sql = "
UPDATE `Games`,`Odds` SET `Games`.`Odds_2`=`Odds`.`Odds` WHERE `Games`.`Game Name` = `Odds`.`Game Name` AND `Odds`.`Odds Name` = '2'"; 
    if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  } 

}

echo "Allt Klart!";

mysql_close($con)


?> 

[tomhath]

How many times does the "foreach ($note as $value) {" loop iterate?

Because inside that loop you have nested the "for ($index = 0; $index < $length; $index++) {" loop which has five different database hits.

The UPDATEs of games might be hitting a lot of rows each time, and you execute them for every $row for every $index. How many times are you hitting the database?

[calle81]

Yes, I put the game name in the game table to unique and now it works faster 😃