infinite411
-
Posts
2 -
Joined
-
Last visited
Never
Posts posted by 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\" /]
Error Inserting Into mysql database using cmd prompt
in MySQL Help
Posted
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??