please help really strange mysql/php error!

[jakx12]

ok so the code inserts into the database a default status when the user sign ups. But I get an error saying: 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 ''status' ) Values(`asd.asd`, `new to the site!` )' at line 2
But if i dont use the apostrophe's instead i use ` on status i get this error: Unknown column 'status' in 'field list'.
here is the code:

mysql_query("INSERT INTO user_notifications
(`username`, `status` ) Values(`$username`, `new to the site!` )")
or die(mysql_error());

Whats wrong!? Any help will be much appreciated! Thanks.

[TommyBs]

Do you really need the ` on the field names? What if you just had

mysql_query("INSERT INTO user_notifications
(username, status ) Values(`$username`, `new to the site!` )")
or die(mysql_error());

[death_oclock]

Backticks are not the same as single quotes. read

[jakx12]

TOMMY - i need it because without them i get this error: Unknown column 'status' in 'field list'

[TommyBs]

Have you tried using ' instead of `

[jakx12]

yup it then says there is an error in mysql syntax.

[death_oclock]

TommyBs is right. You don't need 's around your field names, and definitely don't use `. Is there in fact a field named status? And try doing this query directly in MySQL (via phpMyAdmin if you have it) and see what error you get.

[jakx12]

ok

[Andrieux]

Why do you have backticks in the VALUES()? Backticks around the fields doesn't hurt anything, your query should run with them. In response to the above posts: You can definitely use backticks with fields. (Like INSERT INTO `table`(`field1`, `field2`), but you can't use them in VALUES. Use this character: ' for variables and numbers, and use " for plain text.

Try:

mysql_query("INSERT INTO user_notifications(`username`, `status` ) VALUES('$username', 'New to the site!' )") or die(mysql_error());

Or even better:

$new = "New to the Site!"

mysql_query("INSERT INTO user_notifications(`username`, `status` ) VALUES('$username', '$new' )") or die(mysql_error());

[cwarn23]

TommyBs is right. You don't need 's around your field names, and definitely don't use `. Is there in fact a field named status? And try doing this query directly in MySQL (via phpMyAdmin if you have it) and see what error you get.

From my experience, some servers have a compatibility error when not using the apostrophie ( ` ) for some reason. Probably something to the with the apachie config file. But anyways, if you are planning to share your script around, due to this rare compatibility error, it is best to use the apostrophie around column names. Also note the difference between apostrophies and quotations. The apostrophie key can be found at the top left corner of the keyboard beside the number 1 key.

Also when doing an insert query, I generally prefer to use the old fasion technique of column=value. So you can also try the following:

$new = "New to the Site!"
 
mysql_query("INSERT INTO `user_notifications` SET `username`='".mysql_escape_string($username)."', `status`='".mysql_escape_string($new)."'") OR die(mysql_error());

May be very old fashion the way I did it but it seems to do the job (well for me anyway).

[nav33n]

Column names which are mysql keywords have to be wrapped in ` or else, it would give the above error. date, status, from etc are all keywords.

[mschroeder]

$query = 'INSERT INTO user_notifications (`username`, `status` ) VALUES ("'.$username.'", "new to the site!" )';

mysql_query( $query ) or die( mysql_error() );

That is how I would handle the query, although the column names are not reserved words so they really dont need the backticks around them.

Here is the list of mysql 5.0 reserved keywords

[jakx12]

I still get the same problem!!!

[mschroeder]

You're positive you have a column named status in your table?

[jakx12]

You're positive you have a column named status in your table?

1000000000000000000000% sure!

[mschroeder]

Could you post an export of your table structure?