thameslink Posted February 9, 2011 Share Posted February 9, 2011 Hi, I'm only just getting started with php and mysql but I like to go off the deep end. something is wrong with my script. it seems to work up untill it comes to the UPDATE part. It wont actually update the table and the mysql_affected_rows bit returns -1 I've searched for an explanation and can't find one. Can someone tell me what I'm doing wrong Thanks. //connect to mysql $con = mysql_connect("xxx","yyy","zzz"); if (!$con) { die('Could not connect: ' . mysql_error()); } //use the right database mysql_select_db("foo_bar"); //select all cancelled tickets $status_query = "SELECT * FROM jos_vm_orders where order_status = 'X'"; $status_result = mysql_query($status_query) or die(mysql_error()); while ($newArray = mysql_fetch_array($status_result, MYSQL_ASSOC)) { $order_id = $newArray['order_id']; $quantity_query = "SELECT * FROM jos_vm_order_item where order_id = $order_id"; $quantity_result = mysql_query($quantity_query) or die(mysql_error()); while ($newArray2 = mysql_fetch_array($quantity_result, MYSQL_ASSOC)) { $product_quantity = $newArray2[product_quantity]; $order_item_sku = $newArray2[order_item_sku]; echo ("$order_id cancelled $product_quantity tickets sku was $order_item_sku \n"); mysql_query("UPDATE LOW_PRIORITY jos_vm_product SET product_in_stock = product_in_stock + $product_quantity WHERE product_sku = $order_item_sku AND STR_TO_DATE($order_item_sku, '%Y-%d-%m') $quantity_query = "SELECT * FROM jos_vm_order_item where order_id = $order_id"; $quantity_result = mysql_query($quantity_query) or die(mysql_error()); while ($newArray2 = mysql_fetch_array($quantity_result, MYSQL_ASSOC)) { $product_quantity = $newArray2[product_quantity]; $order_item_sku = $newArray2[order_item_sku]; echo ("$order_id cancelled $product_quantity tickets sku was $order_item_sku \n"); mysql_query("UPDATE LOW_PRIORITY jos_vm_product SET product_in_stock = product_in_stock + $product_quantity) WHERE product_sku = $order_item_sku AND STR_TO_DATE($order_item_sku, '%Y-%d-%m') >= CURDATE()"); echo ("$product_quantity tickets released into $order_item_sku \n"); echo ("Affected rows "); echo (mysql_affected_rows()); echo ("\n"); } } mysql_free_result($status_result); mysql_free_result($quantity_result); mysql_close($con); ?> Quote Link to comment https://forums.phpfreaks.com/topic/227203-help-with-using-variables-to-set-mysql-update/ Share on other sites More sharing options...
Maq Posted February 9, 2011 Share Posted February 9, 2011 You have 2 fatal errors. You're missing the terminating double quote and the semi colon for your UPDATE query. The negative 1 is explained in the manual: Returns the number of affected rows on success, and -1 if the last query failed. Quote Link to comment https://forums.phpfreaks.com/topic/227203-help-with-using-variables-to-set-mysql-update/#findComment-1172001 Share on other sites More sharing options...
thameslink Posted February 9, 2011 Author Share Posted February 9, 2011 You have 2 fatal errors. You're missing the terminating double quote and the semi colon for your UPDATE query. The negative 1 is explained in the manual: Returns the number of affected rows on success, and -1 if the last query failed. Thanks for the explaination of the error code, so whats wrong with those the closing quote and semi-colon for the UPDATE query right after the CURDATE() mysql_query("UPDATE LOW_PRIORITY jos_vm_product SET product_in_stock = product_in_stock + $product_quantity WHERE product_sku = $order_item_sku AND STR_TO_DATE($order_item_sku, '%Y-%d-%m') >= CURDATE()"); Quote Link to comment https://forums.phpfreaks.com/topic/227203-help-with-using-variables-to-set-mysql-update/#findComment-1172010 Share on other sites More sharing options...
Maq Posted February 9, 2011 Share Posted February 9, 2011 I'm talking about these lines: mysql_query("UPDATE LOW_PRIORITY jos_vm_product SET product_in_stock = product_in_stock + $product_quantity WHERE product_sku = $order_item_sku AND STR_TO_DATE($order_item_sku, '%Y-%d-%m') $quantity_query = "SELECT * FROM jos_vm_order_item where order_id = $order_id"; Quote Link to comment https://forums.phpfreaks.com/topic/227203-help-with-using-variables-to-set-mysql-update/#findComment-1172012 Share on other sites More sharing options...
thameslink Posted February 9, 2011 Author Share Posted February 9, 2011 oops I mashed the code up a bit when I posted it, it should be <?php //connect to mysql $con = mysql_connect("host","user","password"); if (!$con) { die('Could not connect: ' . mysql_error()); } //use the right database mysql_select_db("foo_bar"); //select all cancelled tickets $status_query = "SELECT * FROM jos_vm_orders where order_status = 'X'"; $status_result = mysql_query($status_query) or die(mysql_error()); while ($newArray = mysql_fetch_array($status_result, MYSQL_ASSOC)) { $order_id = $newArray['order_id']; $quantity_query = "SELECT * FROM jos_vm_order_item where order_id = $order_id"; $quantity_result = mysql_query($quantity_query) or die(mysql_error()); while ($newArray2 = mysql_fetch_array($quantity_result, MYSQL_ASSOC)) { $product_quantity = $newArray2[product_quantity]; $order_item_sku = $newArray2[order_item_sku]; echo ("$order_id cancelled $product_quantity tickets sku was $order_item_sku \n"); mysql_query("UPDATE LOW_PRIORITY jos_vm_product SET product_in_stock = product_in_stock + $product_quantity WHERE product_sku = $order_item_sku AND STR_TO_DATE($order_item_sku, '%Y-%d-%m') >= CURDATE()"); echo ("$product_quantity tickets released into $order_item_sku \n"); echo ("Affected rows "); echo (mysql_affected_rows()); echo ("\n"); } } mysql_free_result($status_result); mysql_free_result($quantity_result); mysql_close($con); ?> Is the syntax I'm using to add the variables into the UPDATE query correct, because this is where it feels like it's not working. Quote Link to comment https://forums.phpfreaks.com/topic/227203-help-with-using-variables-to-set-mysql-update/#findComment-1172021 Share on other sites More sharing options...
Maq Posted February 9, 2011 Share Posted February 9, 2011 Is the syntax I'm using to add the variables into the UPDATE query correct, because this is where it feels like it's not working. You can check by changing this line to: mysql_query("UPDATE LOW_PRIORITY jos_vm_product SET product_in_stock = product_in_stock + $product_quantity WHERE product_sku = $order_item_sku AND STR_TO_DATE($order_item_sku, '%Y-%d-%m') >= CURDATE()") or die(mysql_error()); *Note: This should be temporary. Errors and exceptions should be handled appropriately, read: http://www.phpfreaks.com/blog/or-die-must-die Quote Link to comment https://forums.phpfreaks.com/topic/227203-help-with-using-variables-to-set-mysql-update/#findComment-1172026 Share on other sites More sharing options...
thameslink Posted February 10, 2011 Author Share Posted February 10, 2011 Ok cool I tracked down that error, it was bailing out due some mismatched STR_TO_DATE strings, now I've removed those only this query mysql_query("UPDATE LOW_PRIORITY jos_vm_product SET product_in_stock = product_in_stock + '$product_quantity' WHERE product_unit = 'e_ticket' AND STR_TO_DATE(product_sku,'%Y-%d-%m') >= DATE_FORMAT(CURDATE(),'%Y-%d-%m') AND product_sku = '$order_item_sku'") or die(mysql_error()); Isn't updating any rows, help it's driving me nuts Quote Link to comment https://forums.phpfreaks.com/topic/227203-help-with-using-variables-to-set-mysql-update/#findComment-1172137 Share on other sites More sharing options...
PFMaBiSmAd Posted February 10, 2011 Share Posted February 10, 2011 You can only do greater-than/less-than comparison of dates when the fields making up the dates are ordered, left to right, most significant digit (year) to least significant digit (day) (month would go in the middle.) Your '%Y-%d-%m' format does not fit that definition and cannot be compared greater-than/less-than. Quote Link to comment https://forums.phpfreaks.com/topic/227203-help-with-using-variables-to-set-mysql-update/#findComment-1172140 Share on other sites More sharing options...
thameslink Posted February 10, 2011 Author Share Posted February 10, 2011 You can only do greater-than/less-than comparison of dates when the fields making up the dates are ordered, left to right, most significant digit (year) to least significant digit (day) (month would go in the middle.) Your '%Y-%d-%m' format does not fit that definition and cannot be compared greater-than/less-than. so this should do it //select all cancelled tickets $status_query = "SELECT * FROM jos_vm_orders where order_status = 'X'"; $status_result = mysql_query($status_query) or die(mysql_error()); while ($newArray = mysql_fetch_array($status_result, MYSQL_ASSOC)) { $order_id = $newArray['order_id']; $quantity_query = "SELECT * FROM jos_vm_order_item where order_id = $order_id"; $quantity_result = mysql_query($quantity_query) or die(mysql_error()); while ($newArray2 = mysql_fetch_array($quantity_result, MYSQL_ASSOC)) { $product_quantity = $newArray2['product_quantity']; $order_item_sku = $newArray2['order_item_sku']; echo ("$order_id cancelled $product_quantity tickets sku was $order_item_sku \n"); } //fetch the product sku $product_query = "SELECT * from jos_vm_product WHERE product_unit = 'e-ticket'"; $product_sku_query = mysql_query($product_query) or die(mysql_error()); while ($newArray3 = mysql_fetch_array($product_sku_query, MYSQL_ASSOC)) { $product_sku = $newArray3['product_sku']; if ($product_sku == $order_item_sku){ mysql_query("UPDATE LOW_PRIORITY jos_vm_product SET product_in_stock = product_in_stock + '$product_quantity' where DATE_FORMAT((STR_TO_DATE($product_sku,'%Y-%d-%m')),'%Y-%m-%d') = DATE_FORMAT(CURDATE(),'%Y-%m-%d')") or die(mysql_error()); echo ("$product_quantity tickets released into $product_sku \n"); echo ("Affected rows \n"); echo (mysql_affected_rows()); echo ("\n"); echo mysql_errno($con) . ": " . mysql_error($con) . "\n"; } } } but returns 2661 cancelled 2 tickets sku was 2011-04-02 2 tickets released into 2011-04-02 Affected rows 0 0: 2662 cancelled 10 tickets sku was 2011-05-02 2664 cancelled 10 tickets sku was 2011-05-02 2677 cancelled 1 tickets sku was 2011-12-02 2734 cancelled 4 tickets sku was 2011-04-02 4 tickets released into 2011-04-02 Affected rows 0 0: 2739 cancelled 3 tickets sku was 2011-26-02 2750 cancelled 2 tickets sku was 2011-12-02 2752 cancelled 1 tickets sku was 2011-12-02 2801 cancelled 5 tickets sku was 2011-12-02 2805 cancelled 2 tickets sku was 2011-12-02 2812 cancelled 1 tickets sku was 2011-10-02 1 tickets released into 2011-10-02 Affected rows 0 0: Quote Link to comment https://forums.phpfreaks.com/topic/227203-help-with-using-variables-to-set-mysql-update/#findComment-1172291 Share on other sites More sharing options...
PFMaBiSmAd Posted February 10, 2011 Share Posted February 10, 2011 CURDATE() returns a mysql DATE value and STR_TO_DATE() returns a mysql DATE value. You can directly compare what STR_TO_DATE($product_sku,'%Y-%d-%m') returns with a CURDATE() value. Quote Link to comment https://forums.phpfreaks.com/topic/227203-help-with-using-variables-to-set-mysql-update/#findComment-1172296 Share on other sites More sharing options...
thameslink Posted February 10, 2011 Author Share Posted February 10, 2011 CURDATE() returns a mysql DATE value and STR_TO_DATE() returns a mysql DATE value. You can directly compare what STR_TO_DATE($product_sku,'%Y-%d-%m') returns with a CURDATE() value. I've tried that as well, I figured that I didn't need to DATE_FORMAT(CURDATE()) both ways the result is 0 updated rows. Quote Link to comment https://forums.phpfreaks.com/topic/227203-help-with-using-variables-to-set-mysql-update/#findComment-1172300 Share on other sites More sharing options...
PFMaBiSmAd Posted February 10, 2011 Share Posted February 10, 2011 Actually, STR_TO_DATE() expects a STRING as the first parameter and since you are providing a literal value in the query, you need single-quotes around it - STR_TO_DATE('$product_sku','%Y-%d-%m') Without the single-quotes, it is taking 2011 - 4 - 2, which is 2005 Quote Link to comment https://forums.phpfreaks.com/topic/227203-help-with-using-variables-to-set-mysql-update/#findComment-1172311 Share on other sites More sharing options...
thameslink Posted February 10, 2011 Author Share Posted February 10, 2011 Without the single-quotes, it is taking 2011 - 4 - 2, which is 2005 Cool, how does it come up with that then? Quote Link to comment https://forums.phpfreaks.com/topic/227203-help-with-using-variables-to-set-mysql-update/#findComment-1172335 Share on other sites More sharing options...
Pikachu2000 Posted February 10, 2011 Share Posted February 10, 2011 Without the single-quotes, it is taking 2011 - 4 - 2, which is 2005 Cool, how does it come up with that then? It subtracts the values. 2011 minus 4 minus 2 . . . Quote Link to comment https://forums.phpfreaks.com/topic/227203-help-with-using-variables-to-set-mysql-update/#findComment-1172493 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.