mysql update record error?

I have the following code to update a field in my mySQL database and can't seem to figure out the problem please help?


$stockname = "Copper Stocks";

mysql_query("UPDATE stocks SET '$stockname'='$stockname'+1 WHERE username='Moki'") or die(mysql_error());


The mySQL Error that I recieve is:
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 'Copper Stocks' = 'Copper Stocks'+1 WHERE username = 'Moki'' at line 1

EDIT: The table stocks has a column names Copper Stocks with the value set to int(12)

 

You should have avoided the quotes.

$stockname = "Copper Stocks";

mysql_query("UPDATE `stocks` SET '$stockname' = $stockname+1 WHERE `username` = 'Moki'") or die(mysql_error());

PHP:

 

How can you add 1 to a string..does it make sense?

 

ok fixed that but now it still shows the same error just without the quotes:

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 'Copper Stocks' = Copper Stocks+1 WHERE username = 'Moki'' at line 1

 

Sorry my mistake.

$stockname = "Copper Stocks";

mysql_query("UPDATE `stocks` SET `$stockname` = $stockname+1 WHERE `username` = 'Moki'") or die(mysql_error());

PHP:

 

Do you have "Copper Stocks" as a column in database table?

 

The stocks table has a column names Copper Stocks that has a default value of 0 and is set as int(12). Therefore the Copper Stocks+1 should increase the 0 to 1 and 1 to 2 etc.


username | Copper Stocks | Silver Stocks
Moki | 0 | 0

 

mysql_query("UPDATE stocks SET $stockname = $stockname+1 WHERE username='Moki'") or die(mysql_error());

You can try this...

 

If I don't have ' ' around the first $stockname then the error shows:

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 Stocks = Copper Stocks+1 WHERE username = 'Moki'' at line 1

 

why do you use spaced column names for tables why can't you use "_" instead of spaces

 

I figure the space may have something to do with my problem but its refering to another table that has all the information about each stock.

Example 1 table has copper stock id, name, value, increase daily, last 6 months, etc

When a stock is purchased it saves the number you own into a table that just has your name and all the differents stocks you can own with the amounts in each column.

 

I dint understand your words exactly friend...

 

When you select a stock it will update the users table by adding a stock to what you already have saved in that column. When the stock values are looked up though it looks at a completely different table for values and multiplies that value by the amount of each stock you have.

The other table that holds the stock information has the stock names values plugged with spaces. So since the 2 tables refer to each other based on the Stocks name there has to be a space in my table instead of a _ .

 

I guess an easier question is:

why is the update trying to save Copper Stocks+1 into the field Copper Stocks instead of finding its value and adding 1 to it?

now: Copper Stocks = Copper Stocks + 1
need: Copper Stocks = (Copper Stocks current value in table) + 1


IF ALL ELSE FAILS: I can just create another query that pulls current amount of copper stocks and then another to update adding that +1

 

Uhm... if I understand you correctly, you are trying to add a value of "Copper Stocks+1" to an int-field? If the above code is correct, you should do it like this:

$stockname = "Copper Stocks";
$get_value = mysql_fetch_row(mysql_query("SELECT $stockname FROM `stocks` WHERE `username` = 'Moki'"),MYSQL_BOTH);
mysql_query("UPDATE `stocks` SET $stockname = $get_value+1 WHERE `username` = 'Moki'") or die(mysql_error());

That should work...