How to update 2 tables using sql in php?

Question

I have 2 tables but when updated, the data from both tables doesn't work. I also have 2 tables where the first table does not have a foreign key, while the second table has a foreign key. for example the skb table as the first table does not have a foreign key, while the pengikut_skb table as the second table has a foreign key.

skb table:

id_skb (PK)

pengikut_skb table:

id_pengikut_skb (PK)

id_skb (FK)

$koneksi= mysqli_connect("localhost", "root", "","test");

    $query1 = ("UPDATE skb SET nl='$nl', ttl='$ttl', jk='$jk', pekerjaan='$pekerjaan', status='$status', agama='$agama', nktp='$nktp', kewa='$kewa', aktp='$aktp', aa='$aa', at='$at', mbp='$mbp', dk='$dk', lb='$lb', jp='$jp', nskl='$nskl', dp='$dp', jenis_kep='$jenis_kep', mb='$mb', syarat_lampiran='$syarat_lampiran', kode_surat1='$kode_surat1', kode_surat2='$kode_surat2', kode_surat3='$kode_surat3', buku_tamu='$buku_tamu', kode_surat4='$kode_surat4', tahun_kode_surat='$tahun_kode_surat', riwayat_kelola='$riwayat_kelola' WHERE id_skb ='$id_skb'");
    $oke1=mysqli_query($koneksi,$query1);

    $query2 = (" UPDATE pengikut_skb SET nama_atau_jumlah_pengikut='$nama_atau_jumlah_pengikut', umur_pengikut='$umur_pengikut', pekerjaan_pengikut='$pekerjaan_pengikut', keterangan_pengikut='$keterangan_pengikut', nama_pengikut='$nama_pengikut', umur_pengikut2='umur_pengikut2', pekerjaan_pengikut2='$pekerjaan_pengikut2', keterangan_pengikut2='$keterangan_pengikut2', nama_pengikut2='$nama_pengikut2', umur_pengikut3='$umur_pengikut3', pekerjaan_pengikut3='$pekerjaan_pengikut3', keterangan_pengikut3='$keterangan_pengikut3', WHERE id_pengikut_skb = '$id_pengikut_skb'");
    $oke2=mysqli_query($koneksi,$query2);

What exactly doesn't work? Are you getting an error message? — Citizen, Jul 02 '20 at 03:21
Also if you're not going to use a framework that handles this for you, please check this out for safer queries: https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Citizen, Jul 02 '20 at 03:23
@Citizen sorry "doesnt work" i mean my data nothing updated in the tables. maybe my query doesnt right. — azem loka, Jul 02 '20 at 03:32
Is there an error message? Please post the output of "echo $query1". Seeing the actual query would be helpful. Any number of things in your variables could be creating a problem since you're not securely creating them. For instance, if there are any single quotes in your variables it can break your query. — Citizen, Jul 02 '20 at 03:38
See about sql injection and the importance of prepared and bound queries — Strawberry, Jul 02 '20 at 05:26

score 0 · Answer 1 · answered Jul 02 '20 at 05:03

In the second query, you are not updating the value of id_skb which is the foreign key of your table. It should have some value in the record which is present in the skb table. It might be one reason that your query is not updating the table. Or if you have set the foreign key as null by default then some other reason can be there.

Hope this helps..

score 0 · Accepted Answer · answered Jul 02 '20 at 05:06

UPDATE skb 
  JOIN pengikut_skb USING (id_skb)
SET skb.column1 = '$value1'
  , pengikut_skb.column2 = '$value2'
-- and so on for each column in each table which must be updated
WHERE pengikut_skb.id_pengikut_skb = '$id_pengikut_skb'
-- and so on - all conditions which defines what record(s) must be updated in each table
--  AND skb.column3 = $value3
--  AND pengikut_skb.column4 = $value4

You do not need to specify skb.id_skb value as in your 1st query because it is defined by $id_pengikut_skb value which is used in 2nd query.

How to update 2 tables using sql in php?

2 Answers2