Simpler Compare Prices Script

[e1seix]

OK, let's forget my last question and look to improve my existing method of updating my database. A bit of background, my website is pre-filled with lots of products from different datafeeds.

 

Currently, my method for updating the info is so: once a day I run a script for all my datafeeds. I automatically revert every one of my database entries to instock='no'. And repopulate it from whatever's available on the datafeed, like so:

 

<?php

$xml = simplexml_load_file('http://datafeeds.productserve.com/datafeed_products.php?user=xxxxx&password=xxxxxxxx&mid=xxx&format=xml&dtd=1.2');
foreach ($xml->xpath('//prod') as $character)
{
  $pId911 = $character->pId;
  $awLink911 = $character->awLink;
  $desc911 = $character->desc;
  $search911 = $character->price->search;

$result911 = mysql_query("UPDATE admin SET inStock='yes',dataDesc='$desc911',dataP='$search911',dataID='911',dataName='CheapSmells.com',dataWeb='www.cheapsmells.com',webLink='http://www.awin1.com/awclick.php?mid=xxx&id=xxxxx',dataLink='$awLink911' WHERE dataID='911' AND dataCode='$pId911'") or die(mysql_error());
}

echo 'Done with Script!<br />';

?>

 

Using dataCode='$pId911' I'm able to locate the entries in my database where the dataCode matches $character->pId in the datafeed, hence it knows to reinstate all the info and mark it again to inStock='yes'

 

Now once I have all of this done I have many duplicate products with the same sku from the various datafeeds, so I need to delete the more expensive ones leaving me with the cheapest inStock='yes' product to display on the site.

 

To do this I use a very elaborate script full of - I think - unneccessary loops and confusion, but it DOES work:

 

<?php

$goDouble=mysql_query("SELECT * FROM admin WHERE sku IN(select sku from admin WHERE inStock='yes' 
GROUP BY sku HAVING COUNT(*) > 1) AND inStock='yes' ORDER BY sku,dataP")or
die(mysql_error());

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

$skuDouble=$row[sku];

$fetchDouble=mysql_query("SELECT * FROM admin WHERE sku='$skuDouble' AND inStock='yes' ORDER BY dataP")or
die(mysql_error());

$i=10;

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

$dataID=$row[dataID];

mysql_query("UPDATE admin SET rank=$i WHERE dataID='$dataID'");

$limit=10;
$sku=$row[sku];

if($i > $limit){
mysql_query("UPDATE admin SET inStock='no' WHERE sku='$sku' AND dataID='$dataID'"); }

else {
mysql_query("UPDATE admin SET inStock='yes' WHERE sku='$sku' AND dataID='$dataID'"); }

$i++; }

}

echo 'Done with delete!<br />';

?>

 

So in other words I range them in ORDER BY dataP (the price) and assign a value for these starting at '10'. So three products the same will result in:

 

Product 1 dataP='10.99' $i='10'

Product 2 dataP='11.25' $i='11'

Product 3 dataP='14.99' $i='12'

 

And say in my script at the end that if $i is greater than '10' to mark as inStock='no'

 

Now, SURELY there must be a simpler way of doing things so that as each datafeed is scanned it can tell that if there is a similar product and the price is less than the one already in stock, to mark it as inStock='yes' and the existing one as no.

 

To try and help you understand this better, $pId911 is how I check the info with the entries in my website. The same variable for the same product will vary from datafeed to datafeed, hence it needs to be verified with the database and defined by the sku, as the sku for the same products is the same in my database - and then rechecked again against the datafeed.

 

I know I'm probably kidding myself thinking someone can help me, but I have come across some of you that really enjoy a challenge of sorts so maybe you know of somewhere you can direct me or websites I can look at.

 

The whole point of wanting to change the way it's scripted is the less interruption made to the website content the better and with doubles and duplicates, for about 5 mins a day while the update script is running the website looks a mess and also it sometimes times out and doesn't complete, so...

 

Best Wishes  x

[amites]

hard to follow the specifics of what your looking for without more code to look at,

 

one thought would be to set a marker on your site so that you have a 3rd option for stocking

 

0 = not in stock

1 = in stock

2 = site update

 

just a thought

[Psycho]

Well, first off using 'yes' and 'no' is terrible. You should be using 0 (false) and 1 (true).

 

 

Anyway, it can be done much simpler. This should be able to be done with a single query, but I couldn't get it to work so, I made it into two;

 

<?php

//Get the IDs of the minimums for each sku
$query = "SELECT dataID, MIN(dataP)
         FROM admin
         GROUP BY sku";
$result = mysql_query($query) or die ("Query: $query<br>".mysql_error());

//Create array of the min IDs
while($min_record = mysql_fetch_array($result)) {
 $mins[] = $min_record[dataID];
}

//Update instock value for all records that are not the mins
$query = "UPDATE admin
          SET inStock='no'
          WHERE dataID NOT IN (" . implode(',', $mins) . ")";
$result = mysql_query($query) or die ("Query: $query<br>".mysql_error());

?>