Help With My Code Update Mysql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
bidntrade
Forum Commoner
Posts: 33
Joined: Thu Jul 02, 2009 9:54 pm

Help With My Code Update Mysql

Post 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?
Last edited by califdon on Thu Jul 02, 2009 11:08 pm, edited 1 time in total.
Reason: Moderator added proper tags to display PHP
bidntrade
Forum Commoner
Posts: 33
Joined: Thu Jul 02, 2009 9:54 pm

Re: Help With My Code Update Mysql

Post 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 ?
bidntrade
Forum Commoner
Posts: 33
Joined: Thu Jul 02, 2009 9:54 pm

Re: Help With My Code Update Mysql

Post 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 ?
bidntrade
Forum Commoner
Posts: 33
Joined: Thu Jul 02, 2009 9:54 pm

Re: Help With My Code Update Mysql

Post 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 ....
Post Reply