$sql = "CREATE TABLE IF NOT EXISTS questions_$username(".
"question_id INT NOT NULL AUTO_INCREMENT, ".
"question MEDIUMTEXT, ".
"answerswer 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";
前 2 个表已成功创建,但第三个表未成功创建。它甚至没有给出任何错误。最后一行被执行。我的数据库中的表数没有限制。
我认为你需要重新考虑你的一般模式。
您不应该为每个用户创建新表!
您应该有测试、问题、test_questions、答案和用户等表。
像这样:
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
然后,您知道哪个测试,哪个用户通过一个查询轻松回答了哪个问题。