Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Apr 2007
    Posts
    15

    Question Unanswered: create database/tables script not working (was "MySQL")

    i got the following MySQL code but its not working when i like compile/execute it:

    PHP Code:
    CREATE DATABASE IF NOT EXISTS KWEADB;
    USE 
    KWEADB;

    CREATE TABLE IF NOT EXISTS staffmember (
    staffid        SMALLINT(4UNSIGNED NOT NULL,
    staff_name    VARCHAR(30NOT NULL,
    service        VARCHAR(30NOT NULL,
    PRIMARY KEY     (staffid)
    TYPE=InnoDB;


    CREATE TABLE IF NOT EXISTS placement (
    openingid    SMALLINT(4UNSIGNED NOT NULL,
    start_date    DATE,
    end_date    DATE,
    total_bonds    INT(6),
    rent        SMALLINT(4),
    PRIMARY KEY     (openingid)
    TYPE=InnoDB;


    CREATE TABLE IF NOT EXISTS tenant (
    tenantid        SMALLINT(4UNSIGNED NOT NULL,
    openingid    SMALLINT(4UNSIGNED NOT NULL,
    tenant_name    VARCHAR(30NOT NULL,
    postal_address    VARCHAR(75NOT NULL,
    DOB        DATE,
    gender        ENUM('m''f'),
    marital_status    VARCHAR(8),
    id_of_people    TINYINT(2),
    tstatus        ENUM('placed''waiting'),
    phone        VARCHAR(10),
    comments    VARCHAR(100),
    PRIMARY KEY     (tenantid),
    FOREIGN KEY    (openingidREFERENCES placement (openingid)
    TYPE=InnoDB;


    CREATE TABLE IF NOT EXISTS property (
    propertyid        SMALLINT(4UNSIGNED NOT NULL,
    ptype            VARCHAR(15),
    id_of_rooms        TINYINT(2),
    address            VARCHAR(75),
    comments        VARCHAR(100),
    PRIMARY KEY         (propertyid)
    TYPE=InnoDB;


    CREATE TABLE IF NOT EXISTS owner (
    ownerid        SMALLINT(4UNSIGNED NOT NULL,
    owner_name    VARCHAR(30NOT NULL,
    owner_address    VARCHAR(30NOT NULL,
    owner_phone    VARCHAR(10),
    PRIMARY KEY     (ownerid)
    TYPE=InnoDB;


    CREATE TABLE IF NOT EXISTS possesion (
    propertyid    SMALLINT(4UNSIGNED NOT NULL,
    ownerid        SMALLINT(4UNSIGNED NOT NULL,
    PRIMARY KEY     (propertyidownerid),
    FOREIGN KEY     (propertyidREFERENCES property (propertyid),
    FOREIGN KEY    (owneridREFERENCES owner (ownerid)
    TYPE=InnoDB;


    CREATE TABLE IF NOT EXISTS report (
    reportid        SMALLINT(4UNSIGNED NOT NULL,
    ownerid        SMALLINT(4UNSIGNED NOT NULL,
    rent        SMALLINT(4UNSIGNED,
    rent_end    DATE,
    service_fee    INT(4UNSIGNED,
    inspection    VARCHAR(100),
    PRIMARY KEY     (reportid),
    FOREIGN KEY    (owneridREFERENCES owner (ownerid)
    TYPE=InnoDB;


    CREATE TABLE IF NOT EXISTS waitinglist (
    waitingid    SMALLINT(4UNSIGNED NOT NULL,
    openingid    SMALLINT(4UNSIGNED NOT NULL,
    start_date    DATE,
    property_type    VARCHAR(15),
    rent_offered    SMALLINT(4UNSIGNED,
    PRIMARY KEY     (waitingid),
    FOREIGN KEY    (openingidREFERENCES placement (openingid)
    TYPE=InnoDB;


    CREATE TABLE IF NOT EXISTS futuretenant (
    propertyid    SMALLINT(4UNSIGNED NOT NULL,
    waitingid    SMALLINT(4UNSIGNED NOT NULL,
    PRIMARY KEY     (propertyidwaitingid),
    FOREIGN KEY     (propertyidREFERENCES property (propertyid),
    FOREIGN KEY    (waitingidREFERENCES waitinglist (waitingid)
    TYPE=InnoDB;


    CREATE TABLE IF NOT EXISTS priority (
    propertyid    SMALLINT(4UNSIGNED NOT NULL,
    staffid        SMALLINT(4UNSIGNED NOT NULL,
    PRIMARY KEY     (propertyidstaffid),
    FOREIGN KEY     (propertyidREFERENCES property (propertyid),
    FOREIGN KEY    (staffidREFERENCES staffmember (staffid)
    TYPE=InnoDB
    Can someone help with it?
    THANKS
    Last edited by philmetz; 05-05-07 at 20:02.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by philmetz
    Can someone help with it?
    probably

    what did you want help with?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by philmetz
    i got the following MySQL code but its not working when i like compile/execute it:.....Can someone help with it?
    THANKS
    what error message do you get?
    on which line?
    how are you subnitting this SQL.. via Query Browser? php MyAdmin, a scrtipt a program?
    on finding the error messaeg have you checked that error message on the MySQL website, and if so does that give you any hint as to what may be wrong?
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    what error message do you get?
    i'm guessing the problems begin with the first column name in the first table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2007
    Posts
    15

    Question ok

    am using MySQL-front
    this is the error:
    PHP Code:
    CREATE TABLE IF NOT EXISTS staff_member (
    staff                                 
    staff_name    VARCHAR
    (30NOT NULL,
    service        VARCHAR(30NOT NULL,
    PRIMARY KEY     (staff    


    CREATE TABLE 
    IF NOT EXISTS placement (
    opening                                
    start_date    DATE
    ,
    end_date    DATE,
    total_bonds    INT(6),
    rent        SMALLINT(4),
    PRIMARY KEY     (opening    


    CREATE TABLE 
    IF NOT EXISTS tenant (
    tenant                                 
    placement              
    tenant_name    VARCHAR
    (30NOT NULL,
    postal_address    VARCHAR(75NOT NULL,
    D.O.B        DATE,
    gender        ENUM('m','f'),
    marital_status    VARCHAR(8),
                           
    status        ENUM(‘placed’, ‘waiting’)
    phone        VARCHAR(10),
    comments    VARCHAR(100),
    PRIMARY KEY     (tenant   
    FOREIGN KEY    
    (placement                                   ))
    Error1064 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 'staff_name    VARCHAR(30) NOT NULL,
    service        VARCHAR(30) NOT NULL
    CREATE TABLE IF NOT EXISTS property (
    property                                 
    type            VARCHAR(15),
                           
    address            VARCHAR(75),
    comments        VARCHAR(100),
    PRIMARY KEY         (property    


    CREATE TABLE IF NOT EXISTS owner (
    owner                                 
    owner_name    VARCHAR(30) NOT NULL,
    owner_address    VARCHAR(30) NOT NULL,
    owner_phone    VARCHAR(10),
    PRIMARY KEY     (owner   
    FOREIGN KEY    (property                                 ))
    Error: 1064 - 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 '
    type            VARCHAR(15),
                           
    address            VARCHAR
    CREATE TABLE 
    IF NOT EXISTS possesion (
    property                                
    owner                                 
    PRIMARY KEY     
    (property         ),
    FOREIGN KEY     (property                                 ),
    FOREIGN KEY    (owner                           ))
    Error1064 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 'owner                                 
    PRIMARY KEY     (property 
    CREATE TABLE IF NOT EXISTS report (
    report                                 
    owner                                 
    rent        SMALLINT(4) UNSIGNED,
    rent_end    DATE,
    service_fee    INT(4) UNSIGNED,
    inspection    VARCHAR(100),
    PRIMARY KEY     (report   
    FOREIGN KEY    (property                                 ),
    FOREIGN KEY    (owner                           ))
    Error: 1064 - 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 '
    owner                                 
    rent        SMALLINT
    (4UNSIG
    CREATE TABLE 
    IF NOT EXISTS waiting_list (
    waiting                                
    opening                                
    start_date    DATE
    ,
    property_type    VARCHAR(15),
    rent_offered    SMALLINT(4UNSIGNED,
    PRIMARY KEY     (waiting   
    FOREIGN KEY    
    (opening                                 ))
    Error1064 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 'opening                                
    start_date    DATE,
    prop
    CREATE TABLE IF NOT EXISTS future_tenant (
    property                                
    waiting                                
    PRIMARY KEY     (property           ),
    FOREIGN KEY     (property                                 ),
    FOREIGN KEY    (waiting                                 ))
    Error: 1064 - 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 '
    waiting                                
    PRIMARY KEY     
    (property
    CREATE TABLE 
    IF NOT EXISTS priority (
    property                                
    staff                                 
    PRIMARY KEY     
    (property         ),
    FOREIGN KEY     (property                                 ),
    FOREIGN KEY    (staff                                  ))
    Error1064 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 'staff 
    How can i fix it?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by philmetz
    Error: 1064 - 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 'staff[/PHP]

    How can i fix it?
    do you understand where this error is occurring?

    first column name in first table

    look up the rules for column names in the mysql manual here --> http://dev.mysql.com/doc/refman/5.0/en/identifiers.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2007
    Posts
    15
    wait is it i cant use underscored ( _ )? or could it be the hash (#)?
    Last edited by philmetz; 05-05-07 at 13:10.

  8. #8
    Join Date
    Mar 2004
    Posts
    480
    I believe you can't use periods in column names.

    also to note when you have fixed your error. None of your foreign keys will work correctly. You must specify ENGINE=innodb for each of your tables for foreign keys to be enforced.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by r937
    i'm guessing the problems begin with the first column name in the first table
    ..........really?
    why is that then?
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Apr 2007
    Posts
    15

    Question

    hmm yea am havvin a problem with InnoDB This is what i changed my code too:
    PHP Code:
    CREATE DATABASE IF NOT EXISTS KWEADB;
    USE 
    KWEADB;

    CREATE TABLE IF NOT EXISTS staffmember (
    staffid        SMALLINT(4UNSIGNED NOT NULL,
    staff_name    VARCHAR(30NOT NULL,
    service        VARCHAR(30NOT NULL,
    PRIMARY KEY     (staffid)
    TYPE=InnoDB;


    CREATE TABLE IF NOT EXISTS placement (
    openingid    SMALLINT(4UNSIGNED NOT NULL,
    start_date    DATE,
    end_date    DATE,
    total_bonds    INT(6),
    rent        SMALLINT(4),
    PRIMARY KEY     (openingid)
    TYPE=InnoDB;


    CREATE TABLE IF NOT EXISTS tenant (
    tenantid        SMALLINT(4UNSIGNED NOT NULL,
    placementid    SMALLINT(4),
    tenant_name    VARCHAR(30NOT NULL,
    postal_address    VARCHAR(75NOT NULL,
    D.O.B        DATE,
    gender        ENUM('m','f'),
    marital_status    VARCHAR(8),
    id_of_people    TINYINT(2),
    status        ENUM('placed''waiting')
    phone        VARCHAR(10),
    comments    VARCHAR(100),
    PRIMARY KEY     (tenantid),
    FOREIGN KEY    (placementidREFERENCES placement (placementid)
    TYPE=InnoDB;


    CREATE TABLE IF NOT EXISTS property (
    propertyid        SMALLINT(4UNSIGNED NOT NULL,
    type            VARCHAR(15),
    id_of_rooms        TINYINT(2),
    address            VARCHAR(75),
    comments        VARCHAR(100),
    PRIMARY KEY         (propertyid)
    TYPE=InnoDB;


    CREATE TABLE IF NOT EXISTS owner (
    ownerid        SMALLINT(4UNSIGNED NOT NULL,
    owner_name    VARCHAR(30NOT NULL,
    owner_address    VARCHAR(30NOT NULL,
    owner_phone    VARCHAR(10),
    PRIMARY KEY     (ownerid),
    FOREIGN KEY    (propertyidREFERENCES property (propertyid)
    TYPE=InnoDB;



    CREATE TABLE IF NOT EXISTS possesion (
    propertyid    SMALLINT(4UNSIGNED NOT NULL,
    ownerid        SMALLINT(4UNSIGNED NOT NULL,
    PRIMARY KEY     (propertyidownerid),
    FOREIGN KEY     (propertyidREFERENCES property (propertyid),
    FOREIGN KEY    (owneridREFERENCES owner (ownerid)
    TYPE=InnoDB;


    CREATE TABLE IF NOT EXISTS report (
    reportid        SMALLINT(4UNSIGNED NOT NULL,
    ownerid        SMALLINT(4UNSIGNED NOT NULL,
    rent        SMALLINT(4UNSIGNED,
    rent_end    DATE,
    service_fee    INT(4UNSIGNED,
    inspection    VARCHAR(100),
    PRIMARY KEY     (reportid),
    FOREIGN KEY    (propertyidREFERENCES property (propertyid),
    FOREIGN KEY    (owneridREFERENCES owner (ownerid)
    TYPE=InnoDB;


    CREATE TABLE IF NOT EXISTS waitinglist (
    waitingid    SMALLINT(4UNSIGNED NOT NULL,
    openingid    SMALLINT(4UNSIGNED NOT NULL,
    start_date    DATE,
    property_type    VARCHAR(15),
    rent_offered    SMALLINT(4UNSIGNED,
    PRIMARY KEY     (waitingid),
    FOREIGN KEY    (openingidREFERENCES placement (openingid)
    TYPE=InnoDB;


    CREATE TABLE IF NOT EXISTS futuretenant (
    propertyid    SMALLINT(4UNSIGNED NOT NULL,
    waitingid    SMALLINT(4UNSIGNED NOT NULL,
    PRIMARY KEY     (propertyidwaitingid),
    FOREIGN KEY     (propertyidREFERENCES property (propertyid),
    FOREIGN KEY    (waitingidREFERENCES placement (waitingid)
    TYPE=InnoDB;


    CREATE TABLE IF NOT EXISTS priority (
    propertyid    SMALLINT(4UNSIGNED NOT NULL,
    staffid        SMALLINT(4UNSIGNED NOT NULL,
    PRIMARY KEY     (propertyidstaffid),
    FOREIGN KEY     (propertyidREFERENCES property (propertyid),
    FOREIGN KEY    (staffidREFERENCES staff_member (staffid)
    TYPE=InnoDB



    what wrong with it. this is the error am getting:


    PHP Code:
    CREATE DATABASE IF NOT EXISTS KWEADB
    USE KWEADB
    CREATE TABLE 
    IF NOT EXISTS staffmember (
    staffid        SMALLINT(4UNSIGNED NOT NULL,
    staff_name    VARCHAR(30NOT NULL,
    service        VARCHAR(30NOT NULL,
    PRIMARY KEY     (staffid)
    TYPE=InnoDB
    CREATE TABLE 
    IF NOT EXISTS placement (
    openingid    SMALLINT(4UNSIGNED NOT NULL,
    start_date    DATE,
    end_date    DATE,
    total_bonds    INT(6),
    rent        SMALLINT(4),
    PRIMARY KEY     (openingid)
    TYPE=InnoDB
    CREATE TABLE 
    IF NOT EXISTS tenant (
    tenantid        SMALLINT(4UNSIGNED NOT NULL,
    placementid    SMALLINT(4),
    tenant_name    VARCHAR(30NOT NULL,
    postal_address    VARCHAR(75NOT NULL,
    D.O.B        DATE,
    gender        ENUM('m','f'),
    marital_status    VARCHAR(8),
    id_of_people    TINYINT(2),
    status        ENUM('placed''waiting')
    phone        VARCHAR(10),
    comments    VARCHAR(100),
    PRIMARY KEY     (tenantid),
    FOREIGN KEY    (placementidREFERENCES placement (placementid)
    TYPE=InnoDB
    Error
    1064 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 '.B        DATE,
    gender        ENUM('
    m','f'),
    marital_status    VARCHAR(8),

    CREATE TABLE IF NOT EXISTS property (
    propertyid        SMALLINT(4) UNSIGNED NOT NULL,
    type            VARCHAR(15),
    id_of_rooms        TINYINT(2),
    address            VARCHAR(75),
    comments        VARCHAR(100),
    PRIMARY KEY         (propertyid)
    ) TYPE=InnoDB
    CREATE TABLE IF NOT EXISTS owner (
    ownerid        SMALLINT(4) UNSIGNED NOT NULL,
    owner_name    VARCHAR(30) NOT NULL,
    owner_address    VARCHAR(30) NOT NULL,
    owner_phone    VARCHAR(10),
    PRIMARY KEY     (ownerid),
    FOREIGN KEY    (propertyid) REFERENCES property (propertyid)
    ) TYPE=InnoDB
    CREATE TABLE IF NOT EXISTS possesion (
    propertyid    SMALLINT(4) UNSIGNED NOT NULL,
    ownerid        SMALLINT(4) UNSIGNED NOT NULL,
    PRIMARY KEY     (propertyid, ownerid),
    FOREIGN KEY     (propertyid) REFERENCES property (propertyid),
    FOREIGN KEY    (ownerid) REFERENCES owner (ownerid)
    ) TYPE=InnoDB
    CREATE TABLE IF NOT EXISTS report (
    reportid        SMALLINT(4) UNSIGNED NOT NULL,
    ownerid        SMALLINT(4) UNSIGNED NOT NULL,
    rent        SMALLINT(4) UNSIGNED,
    rent_end    DATE,
    service_fee    INT(4) UNSIGNED,
    inspection    VARCHAR(100),
    PRIMARY KEY     (reportid),
    FOREIGN KEY    (propertyid) REFERENCES property (propertyid),
    FOREIGN KEY    (ownerid) REFERENCES owner (ownerid)
    ) TYPE=InnoDB
    CREATE TABLE IF NOT EXISTS waitinglist (
    waitingid    SMALLINT(4) UNSIGNED NOT NULL,
    openingid    SMALLINT(4) UNSIGNED NOT NULL,
    start_date    DATE,
    property_type    VARCHAR(15),
    rent_offered    SMALLINT(4) UNSIGNED,
    PRIMARY KEY     (waitingid),
    FOREIGN KEY    (openingid) REFERENCES placement (openingid)
    ) TYPE=InnoDB
    CREATE TABLE IF NOT EXISTS futuretenant (
    propertyid    SMALLINT(4) UNSIGNED NOT NULL,
    waitingid    SMALLINT(4) UNSIGNED NOT NULL,
    PRIMARY KEY     (propertyid, waitingid),
    FOREIGN KEY     (propertyid) REFERENCES property (propertyid),
    FOREIGN KEY    (waitingid) REFERENCES placement (waitingid)
    ) TYPE=InnoDB
    CREATE TABLE IF NOT EXISTS priority (
    propertyid    SMALLINT(4) UNSIGNED NOT NULL,
    staffid        SMALLINT(4) UNSIGNED NOT NULL,
    PRIMARY KEY     (propertyid, staffid),
    FOREIGN KEY     (propertyid) REFERENCES property (propertyid),
    FOREIGN KEY    (staffid) REFERENCES staff_member (staffid)
    ) TYPE=InnoDB 

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by philmetz
    what wrong with it. this is the error am getting:
    you do not seem to understand that the error messages always tell you exactly where the error is occurring

    Error: 1064 - 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 '.B DATE,

    this is pretty explicit

    please have a look through your script and try to find where this is happening

    then let us know what you think the error might mean

    you simply must learn this process of debugging, without it you will not go very far
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2007
    Posts
    15
    am guessing it could be a problem with the ENUM cos its gone green?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by philmetz
    am guessing it could be a problem with the ENUM cos its gone green?
    no, it's before that

    where would you find the two characters .B?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    post #8 also contains a large clue
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Apr 2007
    Posts
    15
    yea i know, iv taken out the periods but still get an error

Posting Permissions

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