Adding dollar values in a Mysql Query?

netfrugal

I'm trying to add dollar values to a column in a table using a mysql query, like this:

$new_sale = 8.51;

UPDATE table SET 
total_sales = total_sales+'$new_sale'
WHERE product_id = '$product_id'

So far, I'm not having any error free return.

Am I using the wrong grammar here?

help!

bradgrafelman

What column type is total_sales? Can you show us the actual PHP code you used to perform the query? Also, remove the quotes around the $new_sale value - numeric values shouldn't be quoted.

netfrugal

You make a good point to ask the column type.

I've got it set at: decimal(15,2)
Here's what I a doing exactly:

$amount = 8.50;

$sql = "UPDATE sum_table SET total = total+'$amount' WHERE ID = 5;
mysql_query($sql,$connection) or die(mysql_error());

Ive tried taking out the quotes, but I need $amount to be a variable, as the amount changes per sale. How would you do it?

bradgrafelman

While the quotes around the amount won't hurt, they definitely aren't necessary/proper.

As for the code you posted, it has a syntax error - could this perhaps be the problem? If not, and I'm assuming you're not getting any errors, are you positive there is a record that has "ID = 5" ? Also, check [man]mysql_affected_rows/man - does MySQL claim to have changed anything?

Finally, try echo'ing out your query and pasting it here.

netfrugal

I hope you know what I am trying to accomplish.

I use phpmyadmin, and utilize the sql query box often.

If I enter this:

UPDATE counter_table SET count = count+1 WHERE countID = 56

It works great!

But that's not a dollar value, with a decimal point.

When I attempt this query (with a dollar value):

UPDATE sum_table SET total = total+8.50 WHERE ID = 5

I get a syntax error, so it seems. I'm asking if there's a way to add a dollar amount to a column. And the column (total) may already have a pre-existing dollar value, like 560.91. So, if I add 8.50 to the column: total, then it should read: 569.41.

Hope this is clearer.

bradgrafelman

What do you mean "so it seems." Either the query runs fine or MySQL returns an error message - which is it?

The same query works fine for me.

netfrugal

Yes, the query works for me too. But I'm attempting to do it with php, and placing the 8.50, or any dollar value, inside a variable. Since the $amount changes per product. That's where I'm getting caught up.

Here's what I've attempted:

With single quotes around $amount

$amount = 1.25;

$sql = "UPDATE sum_table SET total = total+'$amount' WHERE ID = 5";
mysql_query($sql,$connection) or die(mysql_error());

With no quotes around $amount

$amount = 1.25;

$sql = "UPDATE sum_table SET total = total+$amount WHERE ID = 5";
mysql_query($sql,$connection) or die(mysql_error());

Separating $amount from the query, slightly.

$amount = 1.25;

$sql = "UPDATE sum_table SET total = total+".$amount." WHERE ID = 5";
mysql_query($sql,$connection) or die(mysql_error());

Not quite sure what's left to try. But here's the error I keep getting back:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+1.25 WHERE ID = 5 at line 1

bradgrafelman

Weird... can you echo out $sql and paste it here?

I'm a bit clueless... works fine for me.