我試圖在 2 個 sql 表中給出相同的 id，我的代碼如下所示：$field1=$_POST['field1'];$field2=$_POST['field2'];$field3=$_POST['field3']; $query=mysqli_query($con,"insert into employees(name,position,salary) value('$field1','$field2','$field3')");此代碼從我的HTML輸入框中獲取值并將值正確添加到表中，現在我需要一個與上表具有相同 id 的表（以便我可以連接兩個表），我嘗試了類似下面的方法第一次查詢后的代碼，但由于我無法獲得任何可以給我正確 id 的條件，沒有任何效果，示例如下$query=mysqli_query($con,"insert into employees(name,position,salary,empid) value('$field1','$field2','$field3')");$query=mysqli_query($con,"insert into date(id) value(select id from employees where)");誰能告訴我有沒有辦法使用php為sql中的兩個表提供相同的id。
2 回答
繁花不似錦
TA貢獻1851條經驗 獲得超4個贊
正如評論中提到并隨后要求的那樣 - 一個trigger似乎是處理問題的好選擇，因為您只需要關心初始插入 - 然后觸發器會自動處理重復的 ID（或其他字段）。
給定兩個基本表來從問題中復制這些
mysql> describe employees;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | 0 | |
| position | varchar(50) | NO | | 0 | |
| salary | decimal(10,2) | NO | | 0.00 | |
+----------+------------------+------+-----+---------+----------------+
mysql> describe date;
+-----------+------------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+-------------------+-------+
| id | int(10) unsigned | NO | PRI | NULL | |
| timestamp | timestamp | NO | | CURRENT_TIMESTAMP | |
+-----------+------------------+------+-----+-------------------+-------+
一個簡單trigger的綁定到表并在添加新行時employees插入到表中。date
CREATE TRIGGER `tr_employee_inserts` AFTER INSERT ON `employees` FOR EACH ROW BEGIN
insert into `date` set `id`=new.id;
END
去測試
insert into employees (`name`,`position`,`salary` ) values ( 'Peter', 'Porcupine Pickler', 75000 );
insert into employees (`name`,`position`,`salary` ) values ( 'Roger', 'Rabitt Rustler', 25000 );
insert into employees (`name`,`position`,`salary` ) values ( 'Michael', 'Mouse Mauler', 15000 );
select * from `employees`;
select * from `date`;
結果
mysql> select * from employees;
+----+---------+-------------------+----------+
| id | name | position | salary |
+----+---------+-------------------+----------+
| 1 | Peter | Porcupine Pickler | 75000.00 |
| 2 | Roger | Rabitt Rustler | 25000.00 |
| 3 | Michael | Mouse Mauler | 15000.00 |
+----+---------+-------------------+----------+
mysql> select * from date;
+----+---------------------+
| id | timestamp |
+----+---------------------+
| 1 | 2020-01-16 10:11:15 |
| 2 | 2020-01-16 10:11:15 |
| 3 | 2020-01-16 10:11:15 |
+----+---------------------+
函數式編程
TA貢獻1807條經驗 獲得超9個贊
用于$last_id = $con->insert_id;獲取最后插入的 ID。
請嘗試以下代碼。它為你工作。
$field1 = 'name';
$field2 = 'position';
$field3 = '10000';
$field4 = 'SOF01';
// insert employees data
$stmt = $con->prepare("INSERT INTO employees (name,position,salary,empid) VALUES (?, ?, ?, ?)");
$stmt->bind_param("ssss", $field1, $field2, $field3, $field4);
$stmt->execute();
// get last insert id
$last_id = $con->insert_id;
// insert last id in date table
$stmt = $con->prepare("INSERT INTO date (id) VALUES (?)");
$stmt->bind_param("s", $last_id);
$stmt->execute();
- 2 回答
- 0 關注
- 144 瀏覽
添加回答
舉報
0/150
提交
取消