Quick error, php mysql

[Darnel]

Hi, I've been trying to find the syntax error with no luck. Keep getting error 1064. I'm not used to this format so not sure what goes where. Its driving me crazy :'( can any one point me in the right direction?

 

$sql3= " UPDATE ".PROJECT_TO
  ." SET to_user ="
  ."CONCAT_WS(',','".$post['name']."', to_user),"
  ." count   =   'count +1' "
  ." WHERE project_to_id = '".$project_id." '";

 

Thanks :-\

[Jessica]

Post the error, as well as the final SQL you generate.

 

It looks like you're missing a comma for your CONCAT_WS but you'd be able to see that if you printed out the actual SQL.

[Darnel]

Database error: Invalid SQL: UPDATE project_to_user SET to_user =CONCAT_WS(',','demo1', to_user), count = 'count +1' WHERE project_to_id = '1 ' MySQL Error: 1064 (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 'count = 'count +1' WHERE project_to_id = '1 '' at line 1) Session halted.

Edited February 6, 2013 by Darnel

[Jessica]

Count is a restricted keyword. You can't use it as a column name without using backticks. It's best to change the column name.

 

ALSO: 'count + 1' is literally the string "count + 1".

 

PPS: Don't put numbers in quotes in SQL.

Edited February 6, 2013 by Jessica

[requinix]

There's also a couple other problems: count='count+1' will actually try to set the count to the string "count+1", then to fix that you'll put the quotes around the 'count' but you'll be using the wrong quotes (backticks ` are for names, apostrophes ' and quotes " are for strings), and there shouldn't be quotes around the project_to_id number because numbers don't get quotes.

And that $post value better be escaped.

[Darnel]

Thanks, Iv'e changed the name and removed quotes. But still getting error.

 

$sql3= " UPDATE ".PROJECT_TO
  ." SET to_user ="
  ."CONCAT_WS(',','".$post['name']."', to_user),"
  ." show = show +1 "
  ." WHERE project_to_id = ".$project_id." ";

 

This line causes the error (...syntax to use near 'show = show +1 WHERE project_to_id = 2' at line 1))

." show = show +1 "

 

What am I missing?

[Jessica]

This is the same problem as before.

[Darnel]

Yes it is, but I changed count to show and removed quotes as suggested but nothing happened.

[Jessica]

I'm telling you, you made the same mistake.

 

Go study the mysql list of reserved keywords.

[Darnel]

Thanks Jessica, I didn't know about mysql restricted keywords. Its working now.

 

[DavidAM]

That's one reason I always use compound words for column names, i.e. "ShowCount". It is much more unlikely to be a reserved word (and unlikely to become a reserved word in a future release). And never, never let any tool put backticks in your CREATE TABLE statements (I still build mine by hand), if the CREATE had failed you would have solved this problem hours ago.

[Darnel]

That's one reason I always use compound words for column names, i.e. "ShowCount". It is much more unlikely to be a reserved word (and unlikely to become a reserved word in a future release). And never, never let any tool put backticks in your CREATE TABLE statements (I still build mine by hand), if the CREATE had failed you would have solved this problem hours ago.

 

Thanks for the advice, ill know in the future