SQL syntax

[hermes]

I have a MySQL database created on my server, and have permissions all set up and nice. SO, to the next step - I want to use phpmyadmin to IMPORT my SQL code to create a single table with a single column and the data populating the rows. As a test of this import functionality (I have never used it before) - I tried to import some very simple code and see if it all works. It does not! PhpmyAdmin says there something wrong with my SQL code. I am new to SQL and this is probably the weakest Q that has ever been asked on this forum. BUt would be so, so grateful if someone could help me out.

CREATE TABLE mikey (
frampton VARCHAR
);
insert into mikey
(frampton)
values ('firstvalue');

PS. I am intent on getting the import working because once I can get my code right - I can use copy and paste macros in a text document to get the thousands of rows of data in that I need very easily.

[FalseDawn]

A varchar column needs a defined length (max 255)

eg:
CREATE TABLE mikey (frampton VARCHAR(50));

[hermes]

This should work?:

CREATE TABLE mikey (
frampton VARCHAR (10000)
);
insert into mikey
(frampton)
values ('firstvalue');

[hermes]

ah. i just seen the max 255. So this should work:

CREATE TABLE mikey (
frampton VARCHAR (255)
);
insert into mikey
(frampton)
values ('firstvalue');

[FalseDawn]

No, a varchar is 255 characters maximum, before mysql 5.0.3 that is.

If you need more, use a TEXT column

Edit: Yes your previous attempt should work ok

[edited by: FalseDawn at 11:17 pm (utc) on June 28, 2006]

[hermes]

Just tried it. im still getting an error.

[hermes]

im using the SQL interpreter on this page to check code (scroll to bottom):

[sqlcourse.com...]

But the code doesnt work. Have you any idea why it could be? Is the code wrong or the interpreter.

(PS. I hope the url can stay - i have no affiliation with the website and just list it here to best illustrate my problem and what i am trying)

[FalseDawn]

The interpreter is wrong. The SQL is fine.

Perhaps it can't handle multiple SQL commands at once. In any case, the best way to test SQL commands is to execute them directly in a (test) database directly in phpMyAdmin.

[hermes]

PhpMyadmin is not accepting this code either when I put it in a text file (.SQL) and IMPORT it using the import functionality. HELP!

[hermes]

My versions:

MySQL - 4.1.19-standard
phpMyAdmin - 2.8.0.2

[hermes]

When I put it in as an SQL command. can get it working actually. so good news! But then why doesn't it work when i try and import it? any1 any ideas.

[john_k]

Separate your two commands and execute them one at a time. They should work in the interpreter then.

It is possible you will get an error saying that the table "mikey" already exists. So drop it (drop table mikey) and then start over.

I would guess that the table is not available for inserting right away (or the interpreter doesn't think it is). But, as was said previously, your sql is okay.

[john_k]

But then why doesn't it work when i try and import it?

Same as in the interpreter - the table CREATE statement apparently hasn't been committed, or the table is not available for some other reason.

[hermes]

So I have got it all working! If I cut and paste it into the Phpmyadmin/MySQL interpreter - it all works! (doesnt work when I import - but dont care so much anymore)

I have got the following working as well. SO I have figured out how to add more rows to my single column.

CREATE TABLE mikey (
frampton VARCHAR (255)
);
insert into mikey
(frampton)
values ('firstvalue');
insert into mikey
(frampton)
values ('secondvalue');

[hermes]

run into another problem - my SQL code sets up thousands of rows (about 7000). I cut and paste this SQL code into PhPmyadmin - and it gets through the interpreter without errors, BUT it only seems to set up the first 800 or so rows. Howcome? Is there a limit to the number of rows I can have?