Database Newbie question, about my SQL statemnt

[DeadSeaSquirrels]

I want to do some simple stuff, just entering some data into a database. I am using phpmyadmin provided for by my hosting company.

I am using the following line.

INSERT INTO USERS (First Name) VALUE ('Mal');

I am positive everything is spelled correctly, I do in fact have a field called First name. But it keeps on giving me an error message, and the message is vague just saying something about how there is an error near "Name." At first I thought it might be because field names can't have spaces, but I looked that up and that is not true. So I don't understand at all.

Also I am using TWANG to convert some excel sheets to SQL scripts. And I used one script that TWANG created for me, and it worked fine, but then this one (this one is actually apart of a much larger script), doesn't work. Can somebody give me a heads up as to what it might be.

P.S. I tried putting "First Name" in double quotes, single quotes, and as is. nothing works.

 

[Mitzi]

Originally posted by: DeadSeaSquirrels


INSERT INTO USERS (First Name) VALUE ('Mal');

INSERT INTO USERS (First Name) VALUES ('Mal');

Put an 'S' on values

 

[bunker]

Also you may have to put quotes around 'First Name' because there is a space in the name.

To make life easier on you either don't use spaces in table/column names or use an underscore instead of a space.

 

[DeadSeaSquirrels]

The "s" was my fault, the original SQL line had an S for ValueS, but I guessed I cropped it when I copied it over. Anyway, as the bottom of my post mentioned I've tried it with both single quotes and double quotes, neither have worked. I remember checking with the SQL manual and they said that spaces are allowed for field names. Which is why I'm so confused.

 

[bunker]

Originally posted by: DeadSeaSquirrels
The "s" was my fault, the original SQL line had an S for ValueS, but I guessed I cropped it when I copied it over. Anyway, as the bottom of my post mentioned I've tried it with both single quotes and double quotes, neither have worked. I remember checking with the SQL manual and they said that spaces are allowed for field names. Which is why I'm so confused.

Yes, you are allowed to use spaces for table names, but when you call that name in a select or other statement you have to put it in quotes (at lease for MS SQL)

 

[DeadSeaSquirrels]

Well I guess I have no idea what is going on then, because I put the field name in double quotes and in single quotes neither one working. It is on a mySQL server I believe.

 

[bunker]

I just looked at the MySQL documentation (bookmark it and use it!)

INSERT INTO USERS ('mal');

That's it. You don't specify the column in an insert statement, only an UPDATE statement.

You have to account for each column in the table though, so assuming you have 3 columns in the USERS table (First Name, Last Name, Address) It would look like this:

INSERT INTO USERS ('mal',,);

You need the commas there.

 

[DeadSeaSquirrels]

Thanks, I'll look it up myself. But before I look it up, I am going to have to say that I think that is highly unlikely, and this is why. First I used TWANG to generate the script, the scripts that TWANG generate all have the original INSERT USERS (field names) part of it. I used one of the scripts that TWANG generated already and it worked. Which is another reason it is weird (but in that case, some weird buggy things were happening anyway).

Also, besides the fact that it worked, it is unlikely, I think, that a company would release a application that is so fundamentally screwed up. Free yes, but the reputation of their company rests on that.

Second, when I use phpmyAdmin through my hosting company, and can insert items using the phpmyadmin interface, and after inserting an item, the script that did the inserting shows up at the top. And when I checked it out it does indeed have the original INSERT USERS (field name) part. Again I'll take a look see, but if that is the case, that is just really weird.

 

[bunker]

Originally posted by: DeadSeaSquirrels
Thanks, I'll look it up myself. But before I look it up, I am going to have to say that I think that is highly unlikely, and this is why. First I used TWANG to generate the script, the scripts that TWANG generate all have the original INSERT USERS (field names) part of it. I used one of the scripts that TWANG generated already and it worked. Which is another reason it is weird (but in that case, some weird buggy things were happening anyway).

Also, besides the fact that it worked, it is unlikely, I think, that a company would release a application that is so fundamentally screwed up. Free yes, but the reputation of their company rests on that.

Second, when I use phpmyAdmin through my hosting company, and can insert items using the phpmyadmin interface, and after inserting an item, the script that did the inserting shows up at the top. And when I checked it out it does indeed have the original INSERT USERS (field name) part. Again I'll take a look see, but if that is the case, that is just really weird.

Well, you go ahead and use you're twang.

I'll stop checking in this thread.

 

[Descartes]

Originally posted by: bunker
I just looked at the MySQL documentation (bookmark it and use it!)

INSERT INTO USERS ('mal');

That's it. You don't specify the column in an insert statement, only an UPDATE statement.

You have to account for each column in the table though, so assuming you have 3 columns in the USERS table (First Name, Last Name, Address) It would look like this:

INSERT INTO USERS ('mal',,);

You need the commas there.

In ANSI SQL, and every SQL dialect I've ever used, the specification of columns is optional; if left off, it uses defaults to the schema of the table in the insert. This is from the MySQL documentation:

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ((expression | DEFAULT),...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expression, ... ]
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name
SET col_name=(expression | DEFAULT), ...
[ ON DUPLICATE KEY UPDATE col_name=expression, ... ]

 

[DeadSeaSquirrels]

Descartes, I'm not sure what you mean by that. Are you saying that there is no standard? Or that I shouldn't be able to put spaces as column names? Well I see the column names with spaces in there...whether mySQL understands what is going on, is another questions.

Somebody also suggested doing this
INSERT INTO table_name (table_name.first name) VALUES ('Mal')

Or putting quotes around the word "first name", I've tried all of that, single quotes, double quotes, no quotes. Still nothing. The manual says I can use spaces, and at this point it is easier just to take the spaces out, but I just want to know, for my own sanity sake.

 

[calpha]

Well, the answer isn't quotes.

It's the ` mark. I just tested this statement from the mysql command line:

mysql> insert into mytable (`my name`) values ('testing');
Query OK, 1 row affected (0.00 sec)

mysql> select * from `mytable`;
+---------+
| my name |
+---------+
| testing |
+---------+
1 row in set (0.00 sec)

 

[DeadSeaSquirrels]

Thanks, I just tried it and indeed it is the tick `

I thought I had tried that before, because I suspected that it might be something silly like that, and I did try it, but I guess at that point I was trying so many different things at once, that something else was screwed up. I guess I did not use a vigorous scientific method - and I was punished.

But thanks again, I will spread the gospel now.