Jump to content

help with using variables to SET mysql UPDATE


thameslink

Recommended Posts

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

 

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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: 

 

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.