php database table creation fails

Question

I use the following gist to make an OOP attempt to create a database connection:

https://gist.github.com/jonashansen229/4534794

It seems to work so far.

But the creation of the database table passed_exams fails.

Edit:

After recent comments and suggestions i updated my code:

require_once 'Database.php'; // the gist 4534794

class DatabaseSchema {

  public function createStudents() {
    $db = Database::getInstance();
    $mysqli = $db->getConnection();
    $create_students = 'CREATE TABLE IF NOT EXISTS students (
      id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      firstname VARCHAR(40) NOT NULL,
      lastname VARCHAR(40) NOT NULL,
      university VARCHAR(50)
    )';
    $result = $mysqli->query($create_students);
  }

  public function createPassedExams() {
    $db = Database::getInstance();
    $mysqli = $db->getConnection();
    $create_passed_exams = 'CREATE TABLE IF NOT EXISTS passed_exams (
      id INT(6) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(40) NOT NULL,
      student_id INT(6),
      FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
    )';
    $result = $mysqli->query($create_passed_exams);
  }

}

$db_student = new DatabaseSchema();

$db_student->createStudents();
$db_student->createPassedExams();

When i look in the mysql console, only table students is created. Why is table passed_exams missing?

$db_student = new DatabaseSchema() there is `;` missing at the end. — Arsh Singh, May 15 '16 at 11:42
SERIOUSLY?! None can see he never runs the query?! Read the code again! — Itay Moav -Malimovka, May 15 '16 at 11:47
@Arsh, i don't get any error messages. the tables aren't just created.. — StandardNerd, May 15 '16 at 11:48
@ItayMoav-Malimovka yes you are right , he never run the select query , and the $query_exams is not empty as there is a text in string. — Arsh Singh, May 15 '16 at 11:49
Turn on [PHP error logging](http://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display) too . — Martin, May 15 '16 at 12:08

Itay Moav -Malimovka · Answer 1 · 2016-05-15T11:51:36.887

5

You create the string to check $query_students = 'SELECT ID FROM STUDENTS'; but you never actually run this. Then u check the string if it is Empty, it will never be empty in your code. What you should do is use the CREATE ... IF NOT EXISTS syntax of mysql, and not what you do here.

First example show the syntax https://dev.mysql.com/doc/refman/5.5/en/create-table.html

edited May 15 '16 at 11:51

answered May 15 '16 at 11:50

Itay Moav -Malimovka

48,785
58
182
262

Totally agree with you. as the string has a text it is never empty and he is checking if it's empty or not if empty then run query . – Arsh Singh May 15 '16 at 11:50
Good call to reference the `CREATE ... IF NOT EXISTS` too. – Martin May 15 '16 at 11:53
i just updated my code. now the table student will be created but the table passed_exams is still missing. – StandardNerd May 15 '16 at 12:02
for the last issue - the table passed_exams creation - the answer Matt provides was the solution. but is there a way to give you some credits for the answers you gave? – StandardNerd May 15 '16 at 12:29
i guess, i should have closed the question and opened a new one. Sorry Malimovka, next time i will. – StandardNerd May 15 '16 at 12:30

score 1 · Accepted Answer · answered May 15 '16 at 12:21

The id column on your students table is INT(6) UNSIGNED but the student_id column on the passed_exams table is a signed INT(6). Therefore the FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE clause will fail with "Error Code: 1215. Cannot add foreign key constraint".

I advise you to implement some error handling so that you would see this error message rather than blindly continue executing code.

php database table creation fails

2 Answers2