我有3个表。Tbl_info、tbl_training和tbl_infotraining,其中tbl_infotraining用于连接Tbl_info和tbl_training。
tbl_training接受多个复选框值。tbl_trainings包含:
+------+-----------+
| id | training |
+------+-----------+
tbl_infotrainings包含:
+------+-----------+---------------+
| id | info_id | training_id |
+------+-----------+---------------+
我的问题是在插入新信息时。以下是我使用的代码:
用于插入tbl_info的id(工作正常)
$sql3 = "INSERT INTO tbl_infotrainings (info_id) (SELECT id FROM tbl_info)";
插入tbl_training(has errors)的id
$sql4 = "INSERT INTO tbl_infotrainings (training_id) (SELECT id FROM tbl_trainings)";
数据库中期望的输出是:
+------+-----------+------------+
| id | info_id | training |
+------+-----------+------------+
| 1 | 1 | 1 |
+------+-----------+------------+
| 2 | 1 | 2 |
+------+-----------+------------+
| 3 | 1 | 3 |
+------+-----------+------------+
下面是完整的代码:
if($_POST["Submit"]=="Submit"){
$sql1="INSERT INTO tbl_info VALUES ('NULL', '$fname', '$mname', '$lname', '$street', '$barangay', '$city', '$number', '$month', '$day', '$year', '$status' , '$spouse', '$dependent')";
$result1=mysql_query($sql1);
$sql3 = "INSERT INTO tbl_infotrainings (info_id) (SELECT id FROM tbl_info)";
$result3=mysql_query($sql3);
$sql4 = "INSERT INTO tbl_infotrainings (training_id) (SELECT id FROM tbl_trainings)";
$result4=mysql_query($sql4);
for ($i=0; $i<sizeof($checkbox);$i++){
$sql2="INSERT INTO tbl_trainings VALUES ('NULL', '".$checkbox[$i]."')";
$result2=mysql_query($sql2);
}
}
不要使用'NULL'
作为(我假定)AUTO_INCREMENT
id的文本值-只需使用NULL
关键字。
...
$sql1 = "INSERT INTO tbl_info VALUES (NULL, '$fname', '$mname', '$lname', '$street', '$barangay', '$city', '$number', '$month', '$day', '$year', '$status' , '$spouse', '$dependent')"; // NULL not 'NULL'
...
$sql2 = "INSERT INTO tbl_trainings VALUES (NULL, '".$checkbox[$i]."')"; // NULL not 'NULL'
...