CodeIgniter - Delete data from multiple table

Question

I want to delete data from 3 table,i have table user,pemohon,peserta where all table is connected each other with foreign key.

this query work,I've tried on mysql

DELETE user,pemohon,peserta
FROM user,pemohon,peserta
WHERE user.id_user=pemohon.id_pemohon AND
pemohon.id_pemohon=peserta.id_peserta AND pemohon.id_pemohon=1

so i convert to CodeIgniter like this

function delete_data($id)
{
    $this->db->where('pemohon.id_pemohon=user.id_user');
    $this->db->where('pemohon.id_pemohon=peserta.id_peserta');
    $this->db->where('pemohon.id_pemohon',$id);
    $this->db->delete('pemohon','user','peserta');
}

but this code not work, can you fix my code? Thanks

Fine. You can also use $this->db->query("Put your query here which you run in mysql") — Nidhi
– Nidhi, Commented May 29, 2017 at 6:38
@Nidhi i've tried it,$this->db->query("DELETE user,pemohon,peserta FROM user,pemohon,peserta WHERE user.id_user=pemohon.id_pemohon AND pemohon.id_pemohon=peserta.id_peserta AND pemohon.id_pemohon=$id"); but not work — Makku
– Makku, Commented May 29, 2017 at 6:56
but you told above this query work then Why it does not work?? .Try it with give static id pemohon.id_pemohon=1 and also check $id have value or not? — Nidhi
– Nidhi, Commented May 29, 2017 at 7:28
if i use static id is work, but when i change to paramater $id the id not give the value, but when i print $id it give value, i think i just can't Directly insert $id in $this->db->query is there any other way? — Makku
– Makku, Commented May 29, 2017 at 7:52
Are you tried to print $id in delete_data function ?? try this : $this->db->query("DELETE user,pemohon,peserta FROM user,pemohon,peserta WHERE user.id_user=pemohon.id_pemohon AND pemohon.id_pemohon=peserta.id_peserta AND pemohon.id_pemohon='".$id."'");` — Nidhi
– Nidhi, Commented May 29, 2017 at 8:13

Mohammad Hamedani · Accepted Answer · 2017-05-31 03:09:55Z

6

In delete function you must be provide array of tables:

function delete_data($id)
{
    $this->db->where('pemohon.id_pemohon=user.id_user');
    $this->db->where('pemohon.id_pemohon=peserta.id_peserta');
    $this->db->where('pemohon.id_pemohon',$id);
    $this->db->delete(array('pemohon','user','peserta'));
}

If it not worked, execute with query function ($id escaped):

function delete_data($id)
{
    $sql = "DELETE user,pemohon,peserta 
        FROM user,pemohon,peserta 
        WHERE user.id_user=pemohon.id_pemohon 
        AND pemohon.id_pemohon=peserta.id_peserta 
        AND pemohon.id_pemohon= ?";

    $this->db->query($sql, array($id));
}

And without escaping $id:

function delete_data($id)
{
    $this->db->query("DELETE user,pemohon,peserta 
        FROM user,pemohon,peserta 
        WHERE user.id_user=pemohon.id_pemohon 
        AND pemohon.id_pemohon=peserta.id_peserta 
        AND pemohon.id_pemohon= $id";
}

edited May 31, 2017 at 3:09

answered May 29, 2017 at 6:42

Mohammad Hamedani

3,3543 gold badges13 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Mohammad Hamedani Over a year ago

As i see codeigniter documentation, this query cannot be execute with query builder and you can use $this->db->query() function.

Makku Over a year ago

Sorry i'm still newbie in codeigniterAND pemohon.id_pemohon= ?

Mohammad Hamedani Over a year ago

Question marks filling by second parameter in $this->db->query() function, but it escaped for preventing sql injection.

Makku Over a year ago

if i insert parameter $id like this pemohon.id_pemohon=$id' it not give value and if i insert parameter $id` like this `pemohon.id_pemohon=array($id)' it's give the value array(7) but in array format. any other way?

Mohammad Hamedani Over a year ago

pemohon.id_pemohon=$id' cannot replace $id with value, must be use double quotes start and end of string. at the end: pemohon.id_pemohon=$id"

|

Shihas · Accepted Answer · 2017-05-29 07:26:36Z

2

you can try this process

function delete_data($id)
{
   $this->db->delete('user', array('id_user' => $id)); 
   $this->db->delete('pemohon', array('id_pemohon' => $id));
   $this->db->delete('peserta', array('id_peserta' => $id));
}

edited May 29, 2017 at 7:26

Shihas

81415 silver badges45 bronze badges

answered May 29, 2017 at 6:49

Masud_ma

1043 bronze badges

1 Comment

Makku Over a year ago

if i use this code, it just deleted data from table pemohon but not deleted in other table

user10088618 · Accepted Answer · 2018-11-16 17:08:38Z

0

You can save table name within an array and pass that array as parameter in delete(). As you can read full example : https://tutorialpace.com/CodeIgniter-Tutorials/CodeIgniter-Database-Delete

answered Nov 16, 2018 at 17:08

user10088618

1

Collectives™ on Stack Overflow

CodeIgniter - Delete data from multiple table

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related