我有一個如下所示的陣列：
$staffs = array("john","mark","belly","rick", "tom", "harry");
我有一個名為候選人的 mysql 表：
Id name email date_signup staff
1 candidat1 [email protected] 2016/09/01
2 candidat2 [email protected] 2021/05/22
3 candidat3 [email protected] 2021/05/22
4 candidat4 [email protected] 2021/05/22
5 candidat5 [email protected] 2021/05/22
6 candidat6 [email protected] 2021/05/22
7 candidat7 [email protected] 2021/05/22
8 candidat8 [email protected] 2021/05/22
9 candidat9 [email protected] 2021/05/22
10 candidat10 [email protected] 2021/05/22
如何為我的員工陣列中的每個員工更新候選人表
我試圖實作的最終輸出是 6 名員工除外：
Id name email date_signup staff
1 candidat1 [email protected] 2016/09/01 john
2 candidat2 [email protected] 2021/05/22 mark
3 candidat3 [email protected] 2021/05/22 belly
4 candidat4 [email protected] 2021/05/22 rick
5 candidat5 [email protected] 2021/05/22 tom
6 candidat6 [email protected] 2021/05/22 harry
7 candidat7 [email protected] 2021/05/22 john
8 candidat8 [email protected] 2021/05/22 mark
9 candidat9 [email protected] 2021/05/22 belly
10 candidat10 [email protected] 2021/05/22 rick
.....
我開始的一些代碼：
$candidat = "INSERT INTO `candidates`(`name`,`email`,date_sign_up) VALUES ('$name','$email','$date_signup') ";
mysqli_query($con, $candidat );
$id_can = mysqli_insert_id($con);
$Query = "SELECT * from candidates WHERE ORDER BY id DESC limit 0,1";
$Records = mysqli_query($con, $Query);
$row = mysqli_fetch_array($Records );
$user_st = $row['status_par'];
if($user_st == 'john'){
$staff= 'mark';
}
if($user_st == 'mark'){
$staff= 'belly';
}....
mysqli_query($con, "UPDATE candidates SET staff= '$staff' where id='$id_can' ");
我閱讀了從候選人中選擇并為每個員工使用 if 條件或僅使用 foreach 鍵值回圈的任何幫助，非常感謝。
uj5u.com熱心網友回復：
我的意思是你可以通過單個查詢來做到這一點：
update `candidates`
join (
select 1 as id, 'John' as name union
select 2, 'Bruce'
) staff on mod(candidates.id, 2) 1 = staff.id -- where 2 is count of staff
set candidates.staff = staff.name, candidates.date_signup = curdate();
測驗 SQL 小提琴
MySQL 8.0 解決方案：
with staff (id, name) as (
select 1, 'John' union
select 2, 'Bruce'
) update staff
join `candidates` c on mod(c.id, 2) 1 = staff.id -- where 2 is count of staff
set c.staff = staff.name, date_signup = curdate();
select * from candidates;
更喜歡的方式是將員工存盤在單獨的 SQL 表中，從而能夠進行復雜的更新
清除PHP解決方案：
<?php
$staffs = array("john","mark","belly","rick", "tom", "harry");
$query = "UPDATE candidates SET staff = ?, date_signup = CURDATE() WHERE mod(id, ?) = ?";
$stmt = $pdo->prepare($query);
foreach($staffs as $i=>$name) {
$staff_count = count($staffs);
$stmt->execute([$name, $staff_count, $i]);
}
PHP & MySQL 在線沙箱
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/348180.html
上一篇：使用ES6動態更新影像SRC