Error Inserting Into mysql database using cmd prompt

[infinite411]

I created a database and a table to hold dvd information. My table schema looks like this
[code]
| dvds  | CREATE TABLE `dvds` (
  `DVD_Title` varchar(255) default NULL,
  `Studio` varchar(100) default NULL,
  `Released` varchar(25) default NULL,
  `Sound` varchar(10) default NULL,
  `Versions` varchar(25) default NULL,
  `Price` float(6,2) NOT NULL default '0.00',
  `Rating` varchar(25) default NULL,
  `Year` varchar(10) default NULL,
  `Genre` varchar(25) default NULL,
  `Aspect` varchar(10) default NULL,
  `UPC` varchar(25) default NULL,
  `DVD_ReleaseDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `ID` bigint(20) unsigned NOT NULL,
  `Timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
  `imageFrontURL` varchar(255) default NULL,
  `imageBackURL` varchar(255) default NULL,
  `imageThumbURL` varchar(255) default NULL,
  PRIMARY KEY  (`ID`)
[/code]

Now when I try to insert a row with the following information:
[code]
"8 Mile (Widescreen/ Unedited Supplement) / Empire","Universal",,"Discontinued","5.1/DTS","LBX, 16:9",29.99,"VAR","2002","Drama","VAR","025192355424",2003-07-01 00:00:00,30175,2005-01-23 00:00:00
[/code]

Running the Query using the CMD Prompt I get the following ERROR:
[code]
mysql> insert into dvds values("8 Mile (Widescreen/ Unedited Supplement) / Empir
e","Universal",,"Discontinued","5.1/DTS","LBX, 16:9",29.99,"VAR","2002","Drama",
"VAR","025192355424",2003-07-01 00:00:00,30175,2005-01-23 00:00:00);
ERROR 1064 (42000): 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 '"Disc
ontinued","5.1/DTS","LBX, 16:9",29.99,"VAR","2002","Drama","VAR","025192355' at
line 1
[/code]

Could there be something wrong with my schema? I really would appreciate any help from any mysql gurus. I've been at this for several days now.. And i'm clueless [img src=\"style_emoticons/[#EMO_DIR#]/huh.gif\" style=\"vertical-align:middle\" emoid=\":huh:\" border=\"0\" alt=\"huh.gif\" /]

[fenway]

Well now.... a whole series of minor errors in that INSERT statment:

1) You're not specifying which columns correspond to which values -- which is already bad -- but more than that, they don't match! That is, you have more columns than values.

2) While you can pass numeric values without quotes (though you shouldn't really), the same cannot be said for your datetime fields, which need to be string literals.

3) You have a missing value -- you have ", ," in your VALUES() clause -- which is a syntax error.

Other stylistic comments:

- You should be using DECIMAL and not FLOAT.

- 'Year' and 'Timestamp' are reserved keywords -- you should change the name of these columns ASAP, otherwise you're stuck backticking them forever.

- You shouldn't be storing a comma-separated list in any single column (cf. Versions).

Once you've got these sorted out, it shouldn't complain anymore; it should look something like:

[code]insert into dvds ( DVD_Title, Studio, Released, Sound, Versions, Price, Rating, `Year`, Genre, Aspect, UPC, DVD_ReleaseDate, ID, `Timestamp` ) values("8 Mile (Widescreen/ Unedited Supplement) / Empir
e","Universal","Discontinued","5.1/DTS","LBX, 16:9",29.99,"VAR","2002","Drama",
"VAR","025192355424",2003-07-01 00:00:00,30175,2005-01-23 00:00:00);[/code]

Hope that helps.

[infinite411]

Hi thank you for your response I have looked into your suggestions and hopefully made some alterations to my database design. THough I still have someways to go I'd like to ask you how I can go about doing the following

I'm in the process of creating a database to hold dvd information. ie.. something like imdb right now i'm in the process of designing the tables and such but not having a good book on database design is turning out to be a headache. So i've had to search all over the internet and i'm coming up with very few resources. Anyway here goes

I was able to rework my table structure to the following below.
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
CREATE TABLE dvd(
dvd_id int(11) not null primary key,
title varchar(100),
plot_outline varchar(255) NOT NULL default '',
synopsis text NOT NULL default '',
runtime int(11) NOT NULL default '0',
studio varchar(100),
sound varchar(50),
video varchar(50),
rating enum ('','G','PG','PG-13','R','NC-17','XXX'),
prod_year year(4) NOT NULL default '0000',
upc varchar(100) NOT NULL default '',
asin varchar(100) NOT NULL default '',
release_date datetime NOT NULL default '0000-00-00 00:00:00',
front_url varchar(255),
back_url varchar(255),
thumb_url varchar(255),
colour enum('c','b') NOT NULL default 'c',
region enum('0','1','2','3','4','5','6','7','8') NOT NULL default '1',
features varchar(255) not null default '',
includes varchar(255) not null default '',
subtitles varchar(255) not null default '',
category varchar(255) not null default 'Movies',
genre varchar(255) not null default '',
content_keywords text not null default '',
producers text not null default ''
);
[/quote]

Now I was wondering how I may go about inserting multiple values in a single column. For instance certain dvds are multiple genres ie..
Action/Adventure How would I modify my current table above to be able to accomodate this because at the moment it's using a varchar field.

Another thing I'm curious about is it smart to insert all records some thousands into a single table like above or should I create a separate database and split up the records according to genres or some other value???

Do you know of any good database design book i can purchase??

[fenway]

If you need to establish a 1-to-many relationship -- e.g. one dvd having multiple genres -- you need to add some tables to your design. Ideally, you would have a genres table, and add a lookup table table that has genre_uid and dvd_uid. This allows you to store all types of relationships between dvds and genres.

You should keep everything in a single DB/table -- 1000s of records is not a problem, but 100s of tables is.

Lastly, I have no idea regarding books (I never use them) -- the MySQL site has an excellent devArticle of DB normalization, though, which is a good starting point.