Simpler Looping

[e1seix]

Hi all,

 

I have a slight issue with looping at the moment. It's not so much coding issues, as the script works fine.

 

What I have below is a script for updating my databse with info from an affiliate datafeed. From the printed results I get I have a feeling that there's almost TOO MANY loops as I get the desired result but I think it's looped and performed several times as i get the result printed more than once if you see what i mean and it takes longer than i would expect it to. This is causing problems as I actually use 16 different datafeeds and hence it always times out when I contain them all in just one script. Having 16 different individual scripts is sufficient but not really practical. Any tips on how I could do this better?

 

1. The first part of my script selects all the relevant products from my database, by their sku number one by one in a loop. It also reverts all produycts to "out of stock".

 

2. Second part establishes the feed for the datafeed and names the corresponding components.

 

3. Third part again calls upon my database to check which product codes match ones that are present on the databse and reverts them to "in stock" and updates relevant information etc.

 

<?php

mysql_connect("***.*.*.*", "*****", "********") or die(mysql_error());
mysql_select_db("*****_db") or die(mysql_error());

$qry=mysql_query("SELECT * FROM fragrances WHERE sto_ima='directcosmetics'")or
die(mysql_error());

while ($row = mysql_fetch_array($qry))
{

$sku=$row[sku];
$sto=$row[sto_ima];

// UPDATE avail
mysql_query("UPDATE fragrances SET avail='NULL' WHERE sku='$sku' AND sto_ima='$sto'");

// UPDATE MerchID
mysql_query("UPDATE fragrances SET MerchID='590' WHERE sku='$sku' AND sto_ima='$sto'");

}

$xml = simplexml_load_file('http://datafeeds.productserve.com/datafeed_products.php?user=74040&password=22o622&mid=590&format=xml&dtd=1.2');

foreach ($xml->xpath('//prod') as $character) {

$pId= $character->pId;
$brand = $character->brand;
$name = $character->name;
$awLink = $character->awLink;
$desc = $character->desc;
$display = $character->price->search;

mysql_connect("***.*.*.*", "*****", "********") or die(mysql_error());
mysql_select_db("*****_db") or die(mysql_error());

$fetch=mysql_query("SELECT * FROM fragrances WHERE sto_ima='directcosmetics' AND Code='$pId'")or
die(mysql_error());

while ($row = mysql_fetch_array($fetch))
{

$sku=$row[sku];
$MerchID=$row[MerchID];
$sto=$row[sto_ima];

print $avail_db." ".$brand_db." ".$title_db." ".$sku." ".$desc_db." ".$display_db."<br />";
print $pId_db." ".$awLink_db." ".$MerchID."<br /><br />";

// UPDATE avail
mysql_query("UPDATE fragrances SET avail='true' WHERE sku='$sku' AND sto_ima='$sto'");

// UPDATE manufacturer
mysql_query("UPDATE fragrances SET manufacturer='$desc' WHERE sku='$sku' AND sto_ima='$sto'");

// UPDATE pound
mysql_query("UPDATE fragrances SET pound='$display' WHERE sku='$sku' AND sto_ima='$sto'");

// UPDATE MerchID
mysql_query("UPDATE fragrances SET MerchID='590' WHERE sku='$sku' AND sto_ima='$sto'");

// UPDATE MerchID
mysql_query("UPDATE fragrances SET sto_alt='www.directcosmetics.com' WHERE sku='$sku' AND sto_ima='$sto'");

// UPDATE sto_link
mysql_query("UPDATE fragrances SET sto_link='http://www.awin1.com/awclick.php?mid=590&id=74040' WHERE sku='$sku' AND sto_ima='$sto'");

// UPDATE Code
mysql_query("UPDATE fragrances SET Code='$pId' WHERE sku='$sku' AND sto_ima='$sto'");

// UPDATE buy_link
mysql_query("UPDATE fragrances SET buy_link='$awLink' WHERE sku='$sku' AND sto_ima='$sto'");

// UPDATE back
mysql_query("UPDATE fragrances SET back='25' WHERE sku='$sku' AND sto_ima='$sto'");

}

}
?>

 

I'm right in thinking that even if I did have each script saved sparately and used something like "include" to run them all from just one script, it's not going to cut down on the time it takes to run - it still might time out, right?

 

Many THANKS!!!!

[rhodesa]

If I understand your code correctly, you should be able to use this:

 

<?php
//Do connect only once
mysql_connect("***.*.*.*", "*****", "********") or die(mysql_error());
mysql_select_db("*****_db") or die(mysql_error());

$qry = mysql_query("UDPATE fragrances SET avail = NULL,MerchID='590' WHERE sto_ima='directcosmetics'")
  or die(mysql_error());

$xml = simplexml_load_file('http://datafeeds.productserve.com/datafeed_products.php?user=74040&password=22o622&mid=590&format=xml&dtd=1.2');
foreach ($xml->xpath('//prod') as $character)
{
  $pId= $character->pId;
  $brand = $character->brand;
  $name = $character->name;
  $awLink = $character->awLink;
  $desc = $character->desc;
  $display = $character->price->search;

  $result = mysql_query("UPDATE fragrances SET avail='true',manufacturer='$desc',pound='$display',MerchID='590',sto_alt='www.directcosmetics.com',sto_link='http://www.awin1.com/awclick.php?mid=590&id=74040',buy_link='$awLink',back='25' WHERE sto_ima='directcosmetics' AND Code='$pId'")
    or die(mysql_error());
  print $avail_db." ".$brand_db." ".$title_db." ".$sku." ".$desc_db." ".$display_db."<br />";
  print $pId_db." ".$awLink_db." ".$MerchID."<br /><br />";
  print 'Rows updated: '.mysql_affected_rows($result).'<hr>';
}
?>

 

With MySQL you can update several rows and several columns at once.