wrox data INSERT tutorial - need help

[fatthippo]

Newbie needs help!

I'm getting this error when trying to insert data into my newly created DB:

 

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 ' VALUES (1, 'Bruce Almighty', 5, 2003, 1, 2), (2, 'Office

Space', 5, 1999, 5, 6)' at line 1

 

My code for creating the DB is this:

 

<?php

 

//hostname user and password

$connect = mysql_connect("localhost", "root", "password") or die ("Sorry looser, check your server

connection!");

 

//create the main database if it doesn't exist already

$create = mysql_query("CREATE DATABASE IF NOT EXISTS moviesite") or

die (mysql_error());

 

//make sure the recently created DB is the active one

mysql_select_db("moviesite");

 

//create "movie" table

$movie = "CREATE TABLE movie (

movie_id int(11) NOT NULL auto_increment,

movie_name varchar(255) NOT NULL,

movie_type tinyint(2) NOT NULL default 0,

movie_year int(4) NOT NULL default 0,

movie_leadactor int(11) NOT NULL default 0,

movie_director int(11) NOT NULL default 0,

PRIMARY KEY (movie_id),

KEY movie_type (movie_type, movie_year)

)";

 

$results = mysql_query($movie) or die (mysql_error());

 

//create "movietype" table

$movietype = "CREATE TABLE movietype (

movietype_id int(11) NOT NULL auto_increment,

movietype_label varchar(100) NOT NULL,

PRIMARY KEY (movietype_id)

)";

 

$results = mysql_query($movietype) or die (mysql_error());

 

//create "people" table

$people = "CREATE TABLE people (

people_id int(11) NOT NULL auto_increment,

people_fullname varchar(255) NOT NULL,

people_isactor tinyint(1) NOT NULL default 0,

people_isdirector tinyint(1) NOT NULL default 0,

PRIMARY KEY (people_id)

)";

 

$results = mysql_query($people) or die (mysql_error());

 

 

echo "Movie Database successfully created!";

 

 

?>

 

And the code to insert data is this:

 

<?php

 

//connect to database

$connect = mysql_connect("localhost or server name", "root or admin

name", "ashley's password") or die ("Hey loser, check your server

connection");

 

//make sure we're using the right database

mysql_select_db("moviesite");

 

//Insert data into "movie" table

$insert = "INSERT INTO movie (movie_id, movie_name, movie_type, " .

"movie_year, movie_leadactor, movie_director), " .

"VALUES (1, 'Bruce Almighty', 5, 2003, 1, 2), " .

"(2, 'Office Space', 5, 1999, 5, 6), " .

"(3, 'Grand Canyon', 2, 1991, 4, 3)";

 

$results = mysql_query($insert) or die (mysql_error());

 

//insert data into "movietype" table

$type = "INSERT INTO movietype (movietype_id, movietype_label) " .

"VALUES (1, 'Sci Fi'), " .

"(2, 'Drama'), " .

"(3, 'Adventure'), " .

"(4, 'War'), " .

"(5, 'Comedy'), " .

"(6, 'Horror'), " .

"(7, 'Action'), " .

"(8, 'Kids')";

 

$results = mysql_query($type) or die (mysql_error());

 

//insert data into "people" table

$people = "INSERT INTO people (people_id, people_fullname, " .

"people_isactor, people_isdirector), " .

"VALUE (1, 'Jim Carey', 1, 0), " .

"(2, 'Tom Shadyac', 0, 1), " .

"(3, 'Lawrence Kasden', 0, 1), " .

"(4, 'Kevin Kline', 1, 0), " .

"(5, 'Ron Livingston', 1, 0), " .

"(6, 'Mike Judge', 0, 1)";

 

$result = mysql_query($people) or die (mysql_error());

 

 

echo "Data inserted Succefully!";

echo __LINE__;

 

?>

 

What ever assistance you can give me is appreciated.

Thanks,

fatthippo

[tjhilder]

I'm a newbie myself, so i could be wrong, but shouldn't it be something like..

 

$insert = "INSERT INTO movie (movie_id, movie_name, movie_type, movie_year, movie_leadactor, movie_director), " .

 

and not

 

$insert = "INSERT INTO movie (movie_id, movie_name, movie_type, " .
"movie_year, movie_leadactor, movie_director), " .

 

not sure if thats right but to me it looks like you're splitting up your list of fields in table 'movie'

 

hope it helps, if not maybe someone else can help you

 

--

TJ

[ryanlwh]

he's just concatenating, which is totally fine there. and for your info, you don't need to concatenate like that. php supports multi-line strings.

$string = "This is a ".
              "long string";

$string = "This is a
               long string"; //same result as above

 

The actual problem is the comma after the field lists:

[!--fonto:Century Gothic--][span style=\"font-family:Century Gothic\"][!--/fonto--]$insert = "INSERT INTO movie (movie_id, movie_name, movie_type, " .

"movie_year, movie_leadactor, movie_director)[!--sizeo:6--][span style=\"font-size:24pt;line-height:100%\"][!--/sizeo--][!--coloro:#FF0000--][span style=\"color:#FF0000\"][!--/coloro--],[!--colorc--][/span][!--/colorc--][!--sizec--][/span][!--/sizec--] " .[!--fontc--][/span][!--/fontc--]

[tjhilder]

[!--quoteo(post=327296:date=Dec 14 2005, 05:30 AM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Dec 14 2005, 05:30 AM) 327296[/snapback][/div][div class=\'quotemain\'][!--quotec--]

he's just concatenating, which is totally fine there.

ah, then I was wrong, i'm a newbie so i didn't see that one.

 

[!--quoteo(post=327296:date=Dec 14 2005, 05:30 AM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Dec 14 2005, 05:30 AM) 327296[/snapback][/div][div class=\'quotemain\'][!--quotec--]The actual problem is the comma after the field lists:

[!--fonto:Century Gothic--][span style=\"font-family:Century Gothic\"][!--/fonto--]$insert = "INSERT INTO movie (movie_id, movie_name, movie_type, " .

"movie_year, movie_leadactor, movie_director)[!--sizeo:6--][span style=\"font-size:24pt;line-height:100%\"][!--/sizeo--][!--coloro:#FF0000--][span style=\"color:#FF0000\"][!--/coloro--],[!--colorc--][/span][!--/colorc--][!--sizec--][/span][!--/sizec--] " .[!--fontc--][/span][!--/fontc--]

didn't even notice that, oops.

[fatthippo]

[!--quoteo(post=327355:date=Dec 14 2005, 06:09 AM:name=tjhilder)--][div class=\'quotetop\']QUOTE(tjhilder @ Dec 14 2005, 06:09 AM) 327355[/snapback][/div][div class=\'quotemain\'][!--quotec--]

ah, then I was wrong, i'm a newbie so i didn't see that one.

didn't even notice that, oops.

 

 

Lol. It's amazing how much trouble a comma can give you. I'll revise tonight and hopefully all will be well.

Appreciate your help!!!

Thanks

Fatthippo

 

[!--quoteo(post=327296:date=Dec 14 2005, 01:30 AM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Dec 14 2005, 01:30 AM) 327296[/snapback][/div][div class=\'quotemain\'][!--quotec--]

he's just concatenating, which is totally fine there. and for your info, you don't need to concatenate like that. php supports multi-line strings.

$string = "This is a ".
              "long string";

$string = "This is a
               long string"; //same result as above

 

The actual problem is the comma after the field lists:

[!--fonto:Century Gothic--][span style=\"font-family:Century Gothic\"][!--/fonto--]$insert = "INSERT INTO movie (movie_id, movie_name, movie_type, " .

"movie_year, movie_leadactor, movie_director)[!--sizeo:6--][span style=\"font-size:24pt;line-height:100%\"][!--/sizeo--][!--coloro:#FF0000--][span style=\"color:#FF0000\"][!--/coloro--],[!--colorc--][/span][!--/colorc--][!--sizec--][/span][!--/sizec--] " .[!--fontc--][/span][!--/fontc--]

 

 

ryanlwh,

Thanks for the hint!