How to link table "implode array in one cell" with foreign key?

[IQBAL_13]

Thank you for looking at this problem, details are all below.

Below is the code

<form style="overflow: hidden;" method="post">
    <input type="checkbox" aria-label="Checkbox for following text input" 
    name="kursus[]" value="<?php echo $row['IDKursus']; ?>">
    <div class="d-flex justify-content-between mt-3">
        <button type="submit" class="btn btn-success" name="daftar_kursus">Daftar Kursus</button>
        <button type="reset" class="btn btn-primary">Reset</button>
    </div>
</form>

if (isset($_REQUEST['daftar_kursus'])) {
    $IDKursus = implode(',', $_POST['kursus']);
    try {
        //Kursus
        $user = $_SESSION['pensyarah_login'];

        $query = "UPDATE pensyarah SET IDKursus = '$IDKursus' WHERE IDUser = '$user';";

        $sth = $db->prepare($query);
        $sth->execute();

        echo "nice";
    } catch (PDOException $e) {
        $e->getMessage();
    }
}

Screenshot Table Pensyarah

Screenshot Table Kursus

I hope to solve this problem soon. Thank you

[Dani]

I'm sorry but I'm confused by your question. If I look at your PHP code, I see that you are pulling in from the form daftar_kursus, which is the submit button ... Then, I see you are using implode to make the variable $IDKursus a PHP array of all of the checked boxes separated by commas. You then have PHP code t update the table pensyarah. What I don't understand is what isn't working the way you're expecting?

[AndrisP]

I think You need normalize DB-tables for first.

create table Pensyarah(
    IDPensyarah integer not null primary key auto_increment
  , IDUser varchar(50) not null
  , NamaPensyarah varchar(100) not null
  , Email varchar(50) not null
  , NoTel varchar(11) not null
);

create table Kursus(
    IDKursus integer not null primary key auto_increment
  , Semester integer not null
  , KodKursus varchar(50) not null
  , NamaKursus varchar(50) not null
);

create table PensyarahKursusRefs(
    PensyarahKursus integer not null primary key auto_increment
  , IDPensyarah integer not null
  , IDKursus integer not null
  , constraint IDPens_IDKurs_Sem_UQ unique key (Semester,IDPensyarah,IDKursus)
  , constraint Pensyarah_FK foreign key (IDPensyarah) references Pensyarah(IDPensyarah)
  , constraint Kursus_FK foreign key (IDKursus) references Kursus(IDKursus)
);

Then creat view for convenient select list of "Kursus" in one column
like in sample:

create or replace view PensyarahKursus as
select
    p.IDUser
  , p.NamaPensyarah
  , p.Email
  , p.NoTel
  , k.Semester
  , group_concat(k.KodKursus separator ', ') kursusList
from Pensyarah p
left join PensyarahKursusRefs pkr using(IDPensyarah)
left join Kursus k using(IDKursus)
group by
    p.IDUser
  , p.NamaPensyarah
  , p.Email
  , p.NoTel
  , k.Semester
;

You can create procedure for convenient reference inserting:

delimiter $$
create procedure set_Kursus_ref(
    in p_IDUser varchar(50)
  , in p_KodKursus varchar(50)
)
begin
  insert into PensyarahKursusRefs(IDPensyarah,IDKursus) values (
      (select p.IDPensyarah from Pensyarah p where p.IDUser = p_IDUser)
    , (select k.IDKursus from Kursus k where k.KodKursus = p_KodKursus)
  );
end; $$
delimiter ;

Then sample insert and select:

insert into Pensyarah(IDUser,NamaPensyarah,Email,NoTel)
  values ('sample_ID_user_1','sample_Nama_1','[e-mail]','11111111111');
insert into Pensyarah(IDUser,NamaPensyarah,Email,NoTel)
  values ('sample_ID_user_2','sample_Nama_2','[e-mail]','22222222222');
insert into Pensyarah(IDUser,NamaPensyarah,Email,NoTel)
  values ('sample_ID_user_3','sample_Nama_3','[e-mail]','33333333333');

insert into Kursus(Semester,KodKursus,NamaKursus) values (1,'KK-1','KursusNama-1');
insert into Kursus(Semester,KodKursus,NamaKursus) values (1,'KK-2','KursusNama-2');
insert into Kursus(Semester,KodKursus,NamaKursus) values (1,'KK-3','KursusNama-3');
insert into Kursus(Semester,KodKursus,NamaKursus) values (2,'KK-4','KursusNama-4');
insert into Kursus(Semester,KodKursus,NamaKursus) values (2,'KK-5','KursusNama-5');
insert into Kursus(Semester,KodKursus,NamaKursus) values (2,'KK-6','KursusNama-6');

call set_Kursus_ref('sample_ID_user_1','KK-1');
call set_Kursus_ref('sample_ID_user_1','KK-2');
call set_Kursus_ref('sample_ID_user_2','KK-3');
call set_Kursus_ref('sample_ID_user_2','KK-4');
call set_Kursus_ref('sample_ID_user_3','KK-5');
call set_Kursus_ref('sample_ID_user_3','KK-6');

select * from PensyarahKursus;

And dont't insert list of "Kursus" into the one cell! Use reference table instead!

[Dani]

I second that you need to normalize your tables. The same problem you're running into here is what is making your MySQL query not work in this other thread of yours.

Good luck!!