1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Error while creating tables.

Discussion in 'MySQL' started by X.Homer.X, Mar 12, 2008.

  1. #1
    When i try to execute a script to install 3 tables in my database, i get the following error

    Error: 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 ' icon_name varchar(20) NOT NULL default, icon_url varchar(20) NOT NULL default' at line 3

    this is my script

    
    <?php
    $con = mysql_connect("localhost","soldiers_forum","password");
    if (!$con)
      {
      die('Could not connect: ' . mysql_error());
      }
    // Create table in my_db database
    mysql_select_db("soldiers_content", $con);
    $sql = "CREATE TABLE icons
    (
    icon_id int unsigned not null auto_increment, primary key,
    icon_name varchar(20) NOT NULL default,
    icon_url varchar(20) NOT NULL default,
    )
    CREATE TABLE articles
    (
    article_id int unsigned not null auto_increment, primary key,
    article_title tinytext,
    article_sub tinytext,
    article_content mediumtext,
    article_date date,
    )
    CREATE_TABLE matches
    (
    match_id int unsigned not null auto_increment, primary key,
    auto_increment primary key(match_id),
    PRIMARY KEY(match_id),
    match_winner varchar(7),
    match_w_score int(2),
    match_loser carchar(7),
    match_l_score int(2),
    match_date date,
    )";
    
    if (mysql_query($sql,$con))
      {
      echo "Success!";
      }
    else
      {
      echo "Error: " . mysql_error();
      }
    
    mysql_close($con);
    ?>
    
    Code (markup):
    Any help is greatly appreciated :)

    Thanks in advance for any help you may provide.
     
    X.Homer.X, Mar 12, 2008 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    My guess is the comma before primary key.

    Try formatting them like this:

    
    "CREATE TABLE icons
    (
    icon_id int unsigned not null auto_increment,
    icon_name varchar(20) NOT NULL default,
    icon_url varchar(20) NOT NULL default,
    PRIMARY KEY (icon_id)
    )
    
    Code (markup):
    The other tables are probably going to be the same.
     
    jestep, Mar 12, 2008 IP
  3. X.Homer.X

    X.Homer.X Peon

    Messages:
    290
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #3
    thanks, ill try this on my server in a bit and let you know the outcome. I can't right now because im on my wii, and cannot connect to ftp.
     
    X.Homer.X, Mar 12, 2008 IP
  4. X.Homer.X

    X.Homer.X Peon

    Messages:
    290
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #4
    nope, this creates another error message as follows

    Error: 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 ' icon_url varchar(20) NOT NULL default, PRIMARY KEY (icon_id) ) CREATE TABLE' at line 4

    updated install.php
    
    <?php
    $con = mysql_connect("localhost","soldiers_forum","password");
    if (!$con)
      {
      die('Could not connect: ' . mysql_error());
      }
    // Create table in my_db database
    mysql_select_db("soldiers_content", $con);
    $sql = "CREATE TABLE icons
    (
    icon_id int unsigned not null auto_increment,
    icon_name varchar(20) NOT NULL default,
    icon_url varchar(20) NOT NULL default,
    PRIMARY KEY (icon_id)
    )
    CREATE TABLE articles
    (
    article_id int unsigned not null auto_increment,
    article_title tinytext,
    article_sub tinytext,
    article_content mediumtext,
    article_date date,
    PRIMARY KEY (article_id)
    )
    CREATE_TABLE matches
    (
    match_id int unsigned not null auto_increment,
    match_winner varchar(7) not null default,
    match_w_score int(2),
    match_loser varchar(7) not null default,
    match_l_score int(2),
    match_date date,
    PRIMARY KEY (match_id)
    )";
    
    if (mysql_query($sql,$con))
      {
      echo "Success!";
      }
    else
      {
      echo "Error: " . mysql_error();
      }
    
    mysql_close($con);
    ?>
    
    Code (markup):
     
    X.Homer.X, Mar 12, 2008 IP
  5. X.Homer.X

    X.Homer.X Peon

    Messages:
    290
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #5
    sorry for multiple replies in a row, but the problem here is trying to create more than one table at once. If i do them one at a time, (whether in install.php or in phpmyadmin) it works without a hitch. Is there anyway to get them all to create out of one install.php file (for future reference) ive searched numerous times on google, and found nothing.

    Thanks in advance. :)
     
    X.Homer.X, Mar 12, 2008 IP
  6. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #6
    Try putting a ; after each statement.

    article_content mediumtext,
    article_date date,
    PRIMARY KEY (article_id)
    );
     
    jestep, Mar 12, 2008 IP
    X.Homer.X likes this.
  7. X.Homer.X

    X.Homer.X Peon

    Messages:
    290
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #7
    would i need a new $sql variable before each one now? or not?
     
    X.Homer.X, Mar 12, 2008 IP
  8. X.Homer.X

    X.Homer.X Peon

    Messages:
    290
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #8
    nah didnt work, i unno whats wrong. its being dumb :p ill poke around through some install files for other systems that use nysql, tahnks for your help :) +rep'd
     
    X.Homer.X, Mar 12, 2008 IP
  9. X.Homer.X

    X.Homer.X Peon

    Messages:
    290
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #9
    i know this is old, but i still dont understand why its not working, ive installed them all seperately, but i would like to know how i could install them all at once :S
     
    X.Homer.X, Apr 18, 2008 IP