mySQL database, only a certain tables were created

[husslela03]

I have this initialization script, which is attached.

After I run it,

Only two of the tables are created, not the others...

what's wrong here?

 

<?php

 

$con=mysql_connect("localhost","xxxx","xxxx");

if(!$con)

{

die('Could not connect: ' .mysql_error());

}

 

if(mysql_query("CREATE DATABASE my_sasi", $con))

{

  echo "Database created";

}

 

else

{

  echo "Error creating database: ".mysql_error();

}

 

//Create the tables

 

 

mysql_select_db("my_sasi", $con);

   

$sql= "CREATE TABLE Logins

(

  loginID int NOT NULL AUTO_INCREMENT,

  PRIMARY KEY(loginID),

  Email_Address varchar(15),

  password varchar(15)

)";

mysql_query($sql,$con);

 

mysql_select_db("my_sasi", $con);

$sql= "CREATE TABLE Users

  (

    userID int NOT NULL AUTO_INCREMENT,

    PRIMARY KEY(userID),

    FOREIGN KEY(login_ID) references Logins(loginID),

    LastName varchar(15),

    FirstName varchar(15),

    accessLevel int,

    status int

  )";

mysql_query($sql,$con);

 

mysql_select_db("my_sasi", $con);

$sql= "CREATE TABLE students

(

  studentID int NOT NULL AUTO_INCREMENT,

  PRIMARY KEY(studentID),

  FOREIGN KEY(Course_ID) REFERENCES courses(courseID)

)";

mysql_query($sql, $con);

 

mysql_select_db("my_sasi", $con);

$sql= "CREATE TABLE courses

(

  courseID int NOT NULL AUTO_INCREMENT,

  PRIMARY KEY(courseID),

  courseName varchar(15)

)";

mysql_query($sql, $con);

 

mysql_select_db("my_sasi", $con);

$sql="CREATE TABLE teachers

(

  teacherID int NOT NULL AUTO_INCREMENT,

  PRIMARY KEY(teacherID),

  FOREIGN KEY(user_ID) references Users(userID)

)";

mysql_query($sql, $con);

 

 

mysql_select_db("my_sasi", $con);

$sql="CREATE TABLE admins

(

  adminID int NOT NULL AUTO_INCREMENT,

  PRIMARY KEY(adminID),

  FOREIGN KEY(user_ID) references Users(userID)

)";

mysql_query($sql, $con);

 

mysql_select_db("my_sasi", $con);

$sql="CREATE TABLE Roster

(

  RosterID int NOT NULL AUTO_INCREMENT,

  PRIMARY KEY(RosterID),

  FOREIGN KEY(student_ID) references students(studentID)

)";

mysql_query($sql, $con);

 

$sql="CREATE TABLE Assignments

(

  AssignmentID int NOT NULL AUTO_INCREMEMENT,

  PRIMARY KEY(AssignmentID),

  FOREIGN KEY(course_ID) references(courseID),

  AssignmentName varchar(15),

  AssignmentMaxValue int,

  AssignmentWeight int

)";

mysql_query($sql, $con);

 

 

mysql_close($con);

?>

 

[premiso]

For one, you do not need to keep re-selecting the DB. If it is the same DB doing it up top by the connection function is fine. For two, you do not have an error catching / displaying. Chances are your SQL has a en error in it.

 

You can add error showing by doing:

mysql_query($sql, $con) or trigger_error("Query Failed: " . mysql_error());

 

Three, please post all code in

tags.

 

Four, as for why your code is not working you have some weird stuff I have not seen before for example the users table

 

$sql= "CREATE TABLE Users
  (
    userID int NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(userID),
    FOREIGN KEY(login_ID) references Logins(loginID),
    LastName varchar(15),
    FirstName varchar(15),
    accessLevel int,
    status int
  )";

I do not know what that references does but you have login_ID and loginID, which is it? I do not see either in the users table, which I think you would need for a foreign key definition.

 

So yea, see what errors are coming up and fix it from there.