compare 2 tables with csv table data and update one table

[ras68]

I'm banging my head trying to figure this out:

I'm trying to update a price in table Our_Price from a csv file by comparing it with a second table Products, but its not updating the table.

csv data is:
num, sku,list_price, our_price
"1","12345","60.98","56.98"
"2","12346","70.98","66.98"
"3","12347","90.98","86.98"
...

The "sku" value in the csv file is what needs to be compared to the "productcode" in the table Products.

The Our_price and Products tables have a common field "productid"

Products table has columns

productid productcode list_price etc.

Our Price table has columns

productid Our_price etc.

Here's my code:

<?php
mysql_pconnect("localhost", "username", "password") or die("Could not connect : " . mysql_error());
mysql_select_db("dbName");
$handle = fopen("price_update.csv", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== false) {
$sku = (int) $data[1];
echo "sku = ".$sku."\n\n";
$list_price = floatval($data[2]);
echo "List Price = ".$list_price."\n\n";
$our_price = floatval($data[3]);
echo "Our Price = ".$our_price."\n\n";

$query = 'SELECT p.productcode FROM Products p JOIN Our_Price pp ON pp.productid=p.productid';
if (!$result = mysql_query($query)) {
continue;
}
if ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$query = "UPDATE Our_Price pp SET pp.list_price='$list_price' WHERE p.productcode=$sku";

mysql_query($query);
if (mysql_affected_rows() <= 0) {
echo "no rows affected\n\n";
}
} else {
}
mysql_free_result($result);
}

fclose($handle);

?>

Any help would be greatly appreciated.

Thanks.

[NogDog]

Looks like you left out a "p" for the table alias in your where condition:

//                                                                  v
$query = "UPDATE Our_Price pp SET pp.list_price='$list_price' WHERE p.productcode=$sku";
//                                                                  ^

A good idea would be to check the return value from your mysql_query() calls for a false result, and if so log some debug info via mysql_error().

[ras68]

thanks. I edited the code but it still didn't work.

Do I have to re Join the tables in that statement or will the Join from the Select statement hold?

[johanafm]

Each statement is its own. There is no connection between one and the other. At least not unless you create it yourself. Moreover, since you never use anything retrieved by your select statement, it should be removed.

When you claim it does not update, is it because the script doesn't tell you so, or have you compared the actual table data before and after update?
What does mysql errorhandling tell you once you add it?
Should it be list_price and not our_price

[ras68]

Thanks so much guys. Both your inputs made the difference. I rewrote the query to include the JOIN of the 2 tables and added the error code and got it working. Here's the line rewritten:

$query = "UPDATE Our_Price p JOIN Products pp ON pp.productid=p.productid SET p.price='$our_price' WHERE pp.productcode=$sku";
            mysql_query($query)or die("Bad mysql call? : " . mysql_error());

.

Thanks again!