Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2008
    Posts
    6

    Question Unanswered: What's wrong with this code?

    DROP DATABASE IF EXISTS dbjoinourteam;

    CREATE DATABASE dbjoinourteam;

    USE dbjoinourteam;

    CREATE TABLE tbl_admin (
    adminid tinyint(3) unsigned NOT NULL auto_increment,
    adminusername varchar(15) NOT NULL,
    adminpassword varchar(15) NOT NULL,
    PRIMARY KEY (adminid)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    INSERT INTO tbl_admin (adminid, adminusername, adminpassword)
    VALUES (1,'Smooby','#1Sm00bie#1');

    CREATE TABLE tbl_jobopenings (
    jobid int(10) unsigned NOT NULL auto_increment,
    jobname varchar(30) NOT NULL,
    PRIMARY KEY USING BTREE (jobid)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    CREATE TABLE tbl_applicantinfo (
    applicantid int(10) unsigned NOT NULL auto_increment,
    lastname varchar(255) NOT NULL,
    middleinitial varchar(1) NULL,
    firstname varchar(255) NOT NULL,
    email varchar(100) default NOT NULL,
    emailapproved enum('n','y') NOT NULL default 'n',
    cellphone varchar(14) NULL,
    workphone varchar(14) NULL,
    homephone varchar(14) NULL,
    resume varchar(MAX) NOT NULL,
    positionapplyingfor int(10) unsigned NOT NULL,
    applicationdate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    PRIMARY KEY (applicantid),
    KEY FK_tbl_applicantinfo_1 USING BTREE (positionapplyingfor),
    CONSTRAINT FK_tbl_applicantinfo_1 FOREIGN KEY (positionapplyingfor) REFERENCES tbl_jobopenings (jobid) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You know what would really help..?

    If you ran each statement separately and told us which one(s), if any, are causing you the problem.

    The needle is found much easier if you know which haystack it is in.
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there's an index missing on the foreign key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Aww Rudy, where's the fun in that
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what happens if Todd applies for a second position?

    this design is gonna make him enter all his name and address crap again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2008
    Posts
    6
    Quote Originally Posted by r937
    what happens if Todd applies for a second position?

    this design is gonna make him enter all his name and address crap again
    Please tell me how to make it better. This is the first SQL database I've attempted to create. I have no experience with SQL at all.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    remove positionapplyingfor from the applicants table, and add a positionapplyingfor table, consisting of two columns: jobid and applicantid

    and please remove the tbl_ prefixes from your table names

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2008
    Posts
    6
    Quote Originally Posted by r937
    remove positionapplyingfor from the applicants table, and add a positionapplyingfor table, consisting of two columns: jobid and applicantid
    OK. How then do I need to write the script so that the respective tables get called to retrieve the data?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which data are you interested in retrieving?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Apr 2008
    Posts
    6
    I have a web page that I'm writing in PHP and I want it to be able to accept applications for job openings, so I'd like applicants to be able to choose a job opening from a drop down list and store which jobs they have applied for with their personal info and resume.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's rather an open-ended question, don't you think?

    good luck with your project

    there are many, many queries involved in building an application like that

    please don't hesitate to post again when you have a specific problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2008
    Posts
    6
    My specific question is how do I relate the applicants' personal info with the job(s) they've applied for? In other words, how do I reference the jobid and the applicantid in the positionapplyingfor table to the jobid in the jobopenings table and the applicantid to the applicantinfo table?
    Last edited by Apoztel; 04-18-08 at 17:14.

  13. #13
    Join Date
    Apr 2008
    Posts
    6
    OK. I changed the code and got the following errors

    ERROR 1064 (42000): 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 ') ENG
    INE=InnoDB DEFAULT CHARSET=latin1' at line 14
    ERROR 1064 (42000): 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 'KEY F
    K_jobapplyingfor_2 USING BTREE (applicantid),
    CONSTRAINT FK_jobapplyingfor_' at line 6

    DROP DATABASE IF EXISTS dbjoinourteam;

    CREATE DATABASE dbjoinourteam;

    USE dbjoinourteam;

    CREATE TABLE admin (
    adminid tinyint(3) unsigned NOT NULL auto_increment,
    adminusername varchar(15) NOT NULL,
    adminpassword varchar(15) NOT NULL,
    PRIMARY KEY (adminid)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    INSERT INTO admin (adminid, adminusername, adminpassword)
    VALUES (1,'Smooby','#1Sm00bie#1');

    CREATE TABLE jobopenings (
    jobid int(10) unsigned NOT NULL auto_increment,
    jobname varchar(30) NOT NULL,
    PRIMARY KEY USING BTREE (jobid)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    CREATE TABLE applicantinfo (
    applicantid int(10) unsigned NOT NULL auto_increment,
    lastname varchar(255) NOT NULL,
    middleinitial varchar(1) NULL,
    firstname varchar(255) NOT NULL,
    email varchar(100) NOT NULL,
    emailapproved enum('n','y') NOT NULL default 'n',
    cellphone varchar(14) NULL,
    workphone varchar(14) NULL,
    homephone varchar(14) NULL,
    resume varchar(65000) NOT NULL,
    applicationdate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    PRIMARY KEY (applicantid),
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    CREATE TABLE jobapplyingfor (
    jobid int(10) unsigned NOT NULL,
    applicantid int(10) unsigned NOT NULL,
    KEY FK_jobapplyingfor_1 USING BTREE (jobid),
    CONSTRAINT FK_jopapplyingfor_1 FOREIGN KEY (jobid) REFERENCES jobopenings (jobid) ON DELETE CASCADE ON UPDATE CASCADE
    KEY FK_jobapplyingfor_2 USING BTREE (applicantid),
    CONSTRAINT FK_jobapplyingfor_2 FOREIGN KEY (applicantid) REFERENCES applicantinfo (applicantid) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    What is the correct syntax for the lines that produced the errors? Also indexing was mentioned, what does that do and how do I do it?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have a dangling comma here: PRIMARY KEY (applicantid),

    for indexing, see here: optimizing mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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