SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Mar 2006
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    A game of where's the error!!

    I keep getting this error message for the following code: "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 ')' at line 10".

    PHP Code:
    <?php
    //connect to MySQL
    $connect mysql_connect("localhost""bp5am""bp5ampass") or
        die (
    "Hey loser, check your server connection!!");

    //create the main database if it doesn’t already exist
    $create mysql_query("CREATE DATABASE IF NOT EXISTS moviesite")
    or die(
    mysql_error());

    //make sure our recently created database 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());
        
    //Tell me it worked
    echo "Movie Database successfully created!";

    ?>
    I have tried and tried to figure this one out, but I am at a loss. Any thoughts?

  2. #2
    Non-Member melancholic's Avatar
    Join Date
    Nov 2004
    Location
    Australia
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Quote Originally Posted by bhazzard
    I keep getting this error message for the following code: "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 ')' at line 10".

    PHP Code:
    <?php
     
    //connect to MySQL
     
    $connect mysql_connect("localhost""bp5am""bp5ampass") or
         die (
    "Hey loser, check your server connection!!");
     
     
    //create the main database if it doesn’t already exist
     
    $create mysql_query("CREATE DATABASE IF NOT EXISTS moviesite")
     or die(
    mysql_error());
     
     
    //make sure our recently created database 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());
         
     
    //Tell me it worked
     
    echo "Movie Database successfully created!";
     
     
    ?>
    I have tried and tried to figure this one out, but I am at a loss. Any thoughts?
    The create SQL statement has an extra parentheses. ) <---

    PHP Code:
     //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)
     )"

    Hope this helps.


    regards,

  3. #3
    SitePoint Wizard
    Join Date
    Mar 2001
    Posts
    3,537
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    $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))   <----
    )";
    If you had output your own error messages using die(), you would have found the error yourself, e.g.:

    Code:
    $create = mysql_query("CREATE DATABASE IF NOT EXISTS moviesite") or die( "no query" );
    
    //make sure our recently created database is the active one
    mysql_select_db("moviesite") or die("no db");
    
    //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 ("no table query");
    The last one will display, which shows the error is in your query--not on line 10.

  4. #4
    SitePoint Member
    Join Date
    Mar 2006
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Awesome

    Thanks to the both of you. I am relatively new... I have been programming really basic php stuff for a while, and decided to finally work on learning databases with php. Thanks alot!!!

  5. #5
    Non-Member melancholic's Avatar
    Join Date
    Nov 2004
    Location
    Australia
    Posts
    447
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Quote Originally Posted by bhazzard
    Thanks to the both of you. I am relatively new... I have been programming really basic php stuff for a while, and decided to finally work on learning databases with php. Thanks alot!!!
    No problem at all, you'll find that as you gain more experience in developing with php/mysql that you get used to these errors and track them down faster.

    Quote Originally Posted by 7stud
    If you had output your own error messages using die(), you would have found the error yourself, e.g.:.....
    Not necessarily. Using custom error messages instead of using the message that the server puts out could be too general and not really give you the solution to the problem. Of course if you're using this in a production server youd mute your errors and log them instead... (but that's outside the scope of this discussion).

    Granted that the mysql_error() message did not say where you were, but what I usually do is display the SQL in the error in the die statement.

    so Instead of:

    PHP Code:
     $create mysql_query("CREATE DATABASE IF NOT EXISTS moviesite") or die( "no query" ); 
    Which only tells you that there's "no query"

    To get a more meaningful error message, I would do this:

    PHP Code:
     $sql "CREATE DATABASE IF NOT EXISTS moviesite";
     
    mysql_query($sql) or die('Error in Query: '.$sql.'<br/>'.mysql_error()); 
    Which will tell you the error and the SQL statement that gave you the error.

    PHP Code:
      //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 (
    "no table query"); 
    Again... nothing meaningful here.
    I'd rewrite the last two lines to be this.

    PHP Code:
     $results mysql_query($movie) or die('Error in Query: '.$movie.'<br/>'.mysql_error()); 
    Plus don't forget to remove the extra ) at the end of your $movie query.

    To clear up some confusion...

    Quote Originally Posted by 7stud
    The last one will display, which shows the error is in your
    query--not on line 10.
    The line number provided by mysql_error() is talking about the query... not the php script.

    So if you read it word for word and counted the lines in your query you'd find it....

    Code:
     1	CREATE TABLE movie (
     2		movie_id int(11) NOT NULL auto_increment,
     3		movie_name varchar(255) NOT NULL,
     4		movie_type tinyint(2) NOT NULL default 0,
     5		movie_year int(4) NOT NULL default 0,
     6		movie_leadactor int(11) NOT NULL default 0,
     7		movie_director int(11) NOT NULL default 0,
     8		Primary KEY (movie_id),
     9		KEY movie_type (movie_type,movie_year))   
     10	)<---- HERE!


    Regards,


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •