Table does not exist error

Question

$sql = "CREATE TABLE IF NOT EXISTS questions_$username(".
        "question_id INT NOT NULL AUTO_INCREMENT, ".
        "question MEDIUMTEXT, ".
        "answer CHAR(1), ".
        "PRIMARY KEY (question_id))";
$retval = mysql_query($sql, $conn) or die(mysql_error());

$sql = "CREATE TABLE IF NOT EXISTS tests_$username(".
        "test_id INT NOT NULL AUTO_INCREMENT, ".
        "name VARCHAR(30) NOT NULL, ".
        "duration INT NOT NULL, ".
        "PRIMARY KEY (test_id))";
$retval = mysql_query($sql, $conn) or die(mysql_error());

$sql = "CREATE TABLE IF NOT EXISTS questions_tests_$username(".
        "test_id INT NOT NULL, ".
        "question_id INT NOT NULL, ".
        "FOREIGN KEY (test_id) REFERENCES tests_$username(test_id), ".
        "FOREIGN KEY (question_id) REFERENCES questions_$username(question_id), ".
        "PRIMARY KEY (test_id, question_id))".
$retval = mysql_query($sql, $conn) or die(mysql_error());

echo "debug";

The first 2 tables are created successfully, but the third one doesn't. It doesn't even give any error. The last line gets executed. There is no limit on number of tables in my database.

Answer 1

I think you need to rethink you general pattern.

You should not be creating a new table per user!

You should have tables like tests, questions, test_questions, answers, and users.

Something like this:

tests:
   id, name, duration
questions:
   id, question
test_questions:
   id, test_id, question_id
users:
   id, name
answers:
   id, test_questions_id, user_id, answer

Then you know for which test, which user answered which question easily with one query.