Page 1 of 1

Help With My Code Update Mysql

Posted: Thu Jul 02, 2009 10:00 pm
by bidntrade
Need help opening this csv file and updating Mysql ...

anyone have anyidea why this code does not work
its pulling the data from the csv ok . but
the loop for the mysql is not got the correct
values.

if i echo $list[product_id] they are not correct ids.
what do i have wrong here .

Code: Select all

$link = mysql_connect($mysqlHost, $user, $password) or die('Could not
connect: ' . mysql_error());
$handle = fopen("DataFeed.csv", "r");
 
mysql_select_db($database, $link);
 
 
// loop content of csv file, using comma as delemiter
while (($data = fgetcsv($handle)) !== FALSE) {
 
$id = (int) $data[0];
$orgprice = floatval($data[2]);
$weight = floatval($data[15]);
 
if($orgprice <= 10) { 
$price = ($orgprice / 0.30);
}else if ($orgprice <= 50) {
$price = ($orgprice / 0.50);
}else if ($orgprice <= 100) {
$price = ($orgprice / 0.60);
}else if ($orgprice <= 300) {
$price = ($orgprice / 0.75);
}else{
$price = ($orgprice / 0.85);
}
 
$query = 'SELECT product_id FROM cscart_products';
 
 
if (!$result = mysql_query($query)) {
continue;
}
 
if ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
 
// added so i can see the value for testing
echo $line[product_id] . "<br>";
 
$query = "UPDATE cscart_product_prices SET price='$price' WHERE product_id=$line[product_id]";
mysql_query($query);
if (mysql_affected_rows() <= 0) {
// no rows where affected by update query
}
} else {
// entry don't exists continue or insert...
}
mysql_free_result($result);
}
 
fclose($handle);
mysql_close($link);
 
 

as you can see for some reason when i echo
$list[product_code]
its the same for every data entry...

the number echoed is the same ...
why?

Re: Help With My Code Update Mysql

Posted: Thu Jul 02, 2009 11:16 pm
by bidntrade
also .. I would really like to do the same thing with a xml feed instead of a csv
anyone have idea how to do it ?

Re: Help With My Code Update Mysql

Posted: Fri Jul 03, 2009 1:06 pm
by bidntrade
thank you ....

i got it working like this :

Code: Select all

$link = mysql_connect($mysqlHost, $user, $password) or die('Could not
connect: ' . mysql_error());
$handle = fopen("DataFeed.csv", "r");
mysql_select_db($database, $link);
// loop content of csv file, using comma as delemiter
while (($data = fgetcsv($handle)) !== FALSE) {
$id = (int) $data[0];
$orgprice = floatval($data[2]);
$weight = floatval($data[15]);
if($orgprice <= 4) { 
$price = ($orgprice * 3);
}else if ($orgprice <= 10) {
$price = ($orgprice * 2);
}else if ($orgprice <= 35) {
$price = ($orgprice / 0.70);
}else if ($orgprice <= 45) {
$price = ($orgprice / 0.76);
}else if ($orgprice <= 150) {
$price = ($orgprice / 0.80);
}else if ($orgprice <= 250) {
$price = ($orgprice / 0.83);
}else{
$price = ($orgprice / 0.85);
}
// entry exists update
if(mysql_query("UPDATE cscart_product_prices PP LEFT JOIN cscart_products PI USING (product_id) SET PP.price = '$price' WHERE PI.product_code = '$id'")){
}
}
fclose($handle);
mysql_close($link);
unlink("DataFeed.csv");

I would like to convert this script to do the same thing
but from a xml file from another site .... does anyone have a idea how to do it ?

Re: Help With My Code Update Mysql

Posted: Fri Jul 03, 2009 6:26 pm
by bidntrade
can someone tell me why this code update every products price
even when it has not changed ?

Code: Select all

$link = mysql_connect($mysqlHost, $user, $password) or die('Could not
connect: ' . mysql_error());
//$handle = fopen("DataFeed.csv", "r");
 
$handle = fopen("DataFeed.csv", "r");
 
 
mysql_select_db($database, $link);
 
 
// loop content of csv file, using comma as delemiter
while (($data = fgetcsv($handle)) !== FALSE) {
 
$id = (int) $data[0];
$orgprice = floatval($data[2]);
$weight = floatval($data[15]);
$qty = floatval($data[8]);
 
if($orgprice <= 4) { 
$price = ($orgprice * 3);
}else if ($orgprice <= 10) {
$price = ($orgprice * 2);
}else if ($orgprice <= 35) {
$price = ($orgprice / 0.70);
}else if ($orgprice <= 45) {
$price = ($orgprice / 0.76);
}else if ($orgprice <= 150) {
$price = ($orgprice / 0.80);
}else if ($orgprice <= 250) {
$price = ($orgprice / 0.83);
}else{
$price = ($orgprice / 0.85);
}
 
$price = round($price, 2);
 
 
 
$query = 'SELECT cscart_products.product_id FROM cscart_products LEFT JOIN cscart_product_prices USING (product_id) WHERE product_id = ' . $id . ' AND weight = ' . $weight . ' AND price != ' . $price;
 
 
if ($result = mysql_query($query)) {
 
if($go){
// entry exists update
if(mysql_query("UPDATE cscart_product_prices PP LEFT JOIN cscart_products PI USING (product_id) SET PP.price = '$price', PI.amount = '$qty' WHERE PI.product_code = '$id' AND PI.weight = '$weight'")){
$emailinfo .= "Product ".$id." Price Updated To ".$price. " Stock= " . $qty . " .\n";
echo "Product ".$id." Price Updated To ".$price. " Stock= " . $qty . " .<br>";
}
}
}
 
$result = "";
$go='1';
 
}
 
fclose($handle);
mysql_close($link);

I have it doing a select and comparing price in database with new price....
but for some reason it updates all of them every time even when there is no need....

some of the prices after using the round function are showing as

9.5
and my database actually has 9.50

is this the reason ?

are there a better way to do this ....