使用MySql和phpmyadmin，我有两个表person和report，它们使用字段person_id作为report表中的外键进行链接。使用一个 php 表单，我正在尝试将人员详细信息添加到person表中。使用相同的表单，我希望它将日期发布到report表中，然后生成一个自动递增report_id并使用person_id外键将该人与该日期的特定报告链接。
这是我当前的代码
<?php
if (isset($_POST['submitted'])) {
include('dbcon.php'); //link to connection file
$pid = "SELECT person_id FROM person'n"
. "ORDER BY person_id DESC'n"
. "LIMIT 1"; //variable finds last generated person_id
$sqlinsert1 = "INSERT INTO person (person.title, person.first_name, person.last_name, person.address, person.contact_no, person.email, person.ha_id) VALUES ('$_POST[title]' , '$_POST[first_name]' , '$_POST[last_name]' , '$_POST[address]' , '$_POST[contact_no]' , '$_POST[email]' , '$_POST[ha_id]')";
$sqlinsert2 = "INSERT INTO report (report.date) VALUES ('$_POST[date]') WHERE ($pid = report.person_id)";
if (!mysqli_query($dbcon, $sqlinsert1)) {
die('Error inserting record');
} //end of nested if statement
if (!mysqli_query($dbcon, $sqlinsert2)) {
die('Error inserting record');
} //end of nested if statement
$newrecord = "new record added to database";
} //end of if statement
?>
我已经创建了变量$pid它将找到person表中生成的最后一个person_id，我已经对此进行了测试，它可以在phpmyadmin中工作。然后我想使用此变量将日期与person_id链接，并将其放入report表中。
这听起来可能很复杂，但我相信有一个简单的答案。
我认为错误是在 $_POST[日期] 中使用 ' 引号。 你的代码像下面这样..
插入人员后获取人员 ID。
，然后插入报表
$sqlinsert2 = "INSERT INTO report (report.pid,report.date) VALUES ('$pid',"$_POST['date']");//changed check it
然后，您的两个表将使用 PID（参考键）链接在一起。
在与我的讲师交谈并花了几个小时回显所有结果后，我们发现连接 sqli 语句 （'mysqli_query'） 正在连接到服务器，但实际上并没有连接到数据库本身，因此不允许使用 select 语句从 person 表中提取person_id。
我们输入了一个标准的 sql 连接语句，并单独指定了数据库名称。现在这似乎有效，我从另一个表（'defect_id'）添加了另一个外键，并为缺陷表添加了另一个插入语句。我现在可以运行此代码并且工作正常。
<?php
if (isset($_POST['submitted'])) {
include('dbcon.php');
//insert statement 1
$sqlinsert1 = "INSERT INTO person (title, first_name, last_name, address, contact_no, email, ha_id) VALUES ('$_POST[title]' , '$_POST[first_name]' , '$_POST[last_name]' , '$_POST[address]' , '$_POST[contact_no]' , '$_POST[email]' , '$_POST[ha_id]')";
if (!mysqli_query($dbcon, $sqlinsert1)) {
die('Error inserting record1');
} //end of nested if statement1
// connect to database
$dbcon2 = mysql_connect('localhost' , 'root' , '' ); //mysqli query would not connect to db so using mysql connection
if (!$dbcon2){
die('error connecting to database');
}
$dbselect = @mysql_select_db('potholes_v2');
if (!$dbselect){
die('error connecting database');
}
//insert statement 2
$sqlinsert2 = "INSERT INTO defect (road, location, carriageway, lane, diameter, depth, speed, description) VALUES ('$_POST[road]' , '$_POST[location]' , '$_POST[carriageway]' , '$_POST[lane]' , '$_POST[diameter]' , '$_POST[depth]' , '$_POST[speed]' , '$_POST[description]')";
if (!mysqli_query($dbcon, $sqlinsert2)) {
die('Error inserting record2');
} //end of nested if statement1
mysql_close(); //close database connection
//connect to database
$dbcon2 = mysql_connect('localhost' , 'root' , '' ); //mysqli query would not connect to db so using mysql connection
if (!$dbcon2){
die('error connecting to host');
}
$dbselect = @mysql_select_db('potholes_v2');
if (!$dbselect){
die('error connecting database');
}
//select person_id value
$value = mysql_query("SELECT person_id FROM person ORDER BY person_id DESC" , $dbcon2); //selects last entry of person_id in person table
if (!$value) {
die ('error, no values'); //error if no value selected
}
$value2 = mysql_result($value,0); //specifies new value to be inserted into report table as person_id foreign key
//select defect_id value
$defect = mysql_query("SELECT defect_id FROM defect ORDER BY defect_id DESC" , $dbcon2); //selects last defect_id entry from defect table
if (!$defect) {
die ('Error, no values for defect'); //error if no value selected
}
$defect2 = mysql_result($defect,0); //specifies new defect value to be placed in report table
//insert statement 3
$sqlinsert3 = "INSERT INTO report (date, person_id, defect_id) VALUES ('$_POST[date]' , $value2 , $defect2)"; //inserts date, person_id and defect_id values into report table
if (!mysqli_query($dbcon, $sqlinsert3)) {
die('Error inserting record 3');
} //end of nested if statement1
mysql_close(); //close database connection
$newrecord = "new record added to database"; //gives feedback for successful submission
} //end of initial if statement
?>