query issue

[darkcarnival]

hey there,

 

I got a new host for a new site i made and i have a bit of issue with the mysql system on the new host.

 

first off they have register_globals set to off which i have made adjustment to but when i try to enter a query like this:

 

insert into ebb_category (Name, Order) values ('$name', '$order')

 

i get this error:

 

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]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 'Order) values ('General', '2')' at line 1

 

now that table has 3 columes but that colume is the id which i set to auto incredment, so any idea on how to make this work.

 

much thanks.

[fenway]

Well, there's nothing wrong the SQL syntax, but you're using a reserved keyword -- Order -- as one of your column names. Very, very bad idea, since you have have to enclose any reference to this column in backticks:

 

insert into ebb_category (Name, `Order`) values ('$name', '$order')

 

I STRONGLY urge you to change you field name at the earliest opportunity -- this is going to be a hassle FOREVER.

[LazyJones]

It's a pity that using reserved words is even allowed in mysql (maybe other database engines too). Does it even give a warning about it? or do I need to test it to find out

 

[fenway]

No warnings, just errors -- though it you use a decent GUI with syntax highlighting, it won't happen ever. Furthermore, it just indicates that you've chosen very poor column names that aren't very descriptive. BTW, you can usually get away with reserved keywords in most languages with enough escaping/scoping.

 

BTW, LazyJones, speaking of name choices, your sig has an mistake in it... ;-)

[darkcarnival]

your kidding?

 

ok ill have to rename that then.

 

i thought order by was just not allowed

 

but yes a warning should appear to let me know since ive been ripping my hair out over this all week.

 

thanks a bunch.

[LazyJones]

[!--quoteo(post=332944:date=Jan 3 2006, 07:37 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 3 2006, 07:37 PM) 332944[/snapback][/div][div class=\'quotemain\'][!--quotec--]

 

BTW, LazyJones, speaking of name choices, your sig has an mistake in it... ;-)

 

OMG, thank you for that

 

And I've been having it whole day...

 

Added this after answering the same way for 30+ times...

[fenway]

[!--quoteo(post=332956:date=Jan 3 2006, 08:06 PM:name=darkcarnival)--][div class=\'quotetop\']QUOTE(darkcarnival @ Jan 3 2006, 08:06 PM) 332956[/snapback][/div][div class=\'quotemain\'][!--quotec--]

i thought order by was just not allowed

but yes a warning should appear to let me know since ive been ripping my hair out over this all week.

 

The problem really is the when the parser hits the word "order", it expects it to be followed by "by" -- it has no idea that you _wanted_ it to be your field name. Sure, it could, but that would make the parser incredibly slower.