Jump to content


Photo

wrox data INSERT tutorial - need help


  • Please log in to reply
4 replies to this topic

#1 fatthippo

fatthippo
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 14 December 2005 - 03:47 AM

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

#2 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 14 December 2005 - 04:21 AM

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

#3 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 14 December 2005 - 05:30 AM

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--]
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#4 tjhilder

tjhilder
  • Members
  • PipPipPip
  • Advanced Member
  • 136 posts
  • LocationKent, UK

Posted 14 December 2005 - 10:09 AM

[!--quoteo(post=327296:date=Dec 14 2005, 05:30 AM:name=ryanlwh)--][div class=\'quotetop\']QUOTE(ryanlwh @ Dec 14 2005, 05:30 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
he's just concatenating, which is totally fine there.
[/quote]
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) View Post[/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--]
[/quote]
didn't even notice that, oops.

#5 fatthippo

fatthippo
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 14 December 2005 - 02:00 PM

[!--quoteo(post=327355:date=Dec 14 2005, 06:09 AM:name=tjhilder)--][div class=\'quotetop\']QUOTE(tjhilder @ Dec 14 2005, 06:09 AM) View Post[/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.
[/quote]


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) View Post[/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--]
[/quote]


ryanlwh,
Thanks for the hint!





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users