I have an array like the following :
$staffs = array("john","mark","belly","rick", "tom", "harry");
I have a mysql table called candidates :
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
How can I update candidates table for each staff in my staffs array
My final output I trying to achieve is except its for 6's of staffs:
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
.....
Some code that I start with :
$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' ");
I read about select from candidates and use if condition for every staff or just using foreach key value loop any help much appreciated.
CodePudding user response：
I mean you can do this by single query:
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();
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;
By prefer way is store staff in separate SQL table that give ability to mere complicate updates
Clear PHP solution:
<?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]);
}