help with using variables to SET mysql UPDATE

[thameslink]

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);
?>

 

[Maq]

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.

[thameslink]

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()");

[Maq]

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";

[thameslink]

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.

[Maq]

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

[thameslink]

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

 

[PFMaBiSmAd]

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.

[thameslink]

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: 

 

 

 

[PFMaBiSmAd]

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.

[thameslink]

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.

[PFMaBiSmAd]

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

[thameslink]

 

Without the single-quotes, it is taking 2011 - 4 - 2, which is 2005

 

Cool, how  does it come up with that then?

[Pikachu2000]

 

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