Jump to content

Archived

This topic is now archived and is closed to further replies.

fatthippo

wrox data INSERT tutorial - need help

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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--]

Share this post


Link to post
Share on other sites

[!--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.

Share this post


Link to post
Share on other sites

[!--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!

 

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.