Delete record in 3 tables - Printable Version

Delete record in 3 tables - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Delete record in 3 tables (/showthread.php?tid=19108)

Delete record in 3 tables - El Forum - 05-28-2009

[eluser]René Tuin[/eluser]
Hi all,

I have the following question.
I want to delete a record in 3 different tables.
But it won't work with the code I use.

This is my code

Code:
$this->db->from('pages, pages_module, content');

$this->db->where('pages.id_Pages', $id);

$this->db->where('pages_module.Pages_id_Pages', $id);

$this->db->where('pages_module.Content_id_Content = content.id_Content');

$this->db->delete();

Explain what i want:

I have the following tables

-pages
-pages_module
-content

I want to delete in pages all fields where id_Pages = $id.

In pages_module I want to delete all fields where Pages_id_Pages = $id.

Now I have in pages_module also a field Content_id_Content that is linked to id_Content in content table.
All fields where Content_id_Content = id_Content must also be deleted.

So can some help me to get the right syntax.

Greetz René

Delete record in 3 tables - El Forum - 05-28-2009

[eluser]Wuushu[/eluser]

Code:
$this->db->where('FIELD', $value);    // What condition(s)?

$this->db->delete('TABLE1');          // From what table    

$this->db->where('FIELD', $value);    // What condition(s)?

$this->db->delete('TABLE2');          // From what table    

$this->db->where('FIELD', $value);    // What condition(s)?

$this->db->delete('TABLE3');          // From what table

I usually do like this, works for me, but not sure how it works for your last equal

Delete record in 3 tables - El Forum - 05-28-2009

[eluser]xwero[/eluser]
You use a silent join but i think you have to use an explicit join to get it working

Code:
$this->db->join('content','pages_module.Content_id_Content = content.id_Content','left');

$this->db->join('pages_module','pages_module.Pages_id_Pages = pages.id_Pages','left');

$this->db->where('pages.id_Pages', $id);

$this->db->delete('pages');

Delete record in 3 tables - El Forum - 05-28-2009

[eluser]René Tuin[/eluser]
[quote author="xwero" date="1243540732"]You use a silent join but i think you have to use an explicit join to get it working

Code:
$this->db->join('content','pages_module.Content_id_Content = content.id_Content','left');

$this->db->join('pages_module','pages_module.Pages_id_Pages = pages.id_Pages','left');

$this->db->where('pages.id_Pages', $id);

$this->db->delete('pages');

[/quote]

If i use your code, the only think that is deleted is the row where $id = id_Pages.
But also the row with Pages_id_Pages = $id must be deleted and the row id_Content where pages_module.Content_id_Content = content.id_Content.

Greetz

Delete record in 3 tables - El Forum - 05-28-2009

[eluser]Evil Wizard[/eluser]
looks like you'll need 3 delete queries to do that, however, there is a Doctrine function that allows you to set up relations between records giving the ability to cascade delete statements so only one delete command is issued and all other records in other tables that rely on the main record are transparently deleted, but the learning curve with Doctrine is quite steep.

Delete record in 3 tables - El Forum - 05-28-2009

[eluser]xwero[/eluser]
It's the delete method that doesn't add the the joins so you have to do

Code:
$this->db->query('DELETE pages, pages_module, content 

                        FROM pages 

                        LEFT JOIN content ON pages_module.Content_id_Content = content.id_Content

                        LEFT JOIN pages_module ON pages_module.Pages_id_Pages = pages.id_Pages

                        WHERE pages.id_Pages = '.$this->db->escape($id));

Delete record in 3 tables - El Forum - 05-29-2009

[eluser]René Tuin[/eluser]
[quote author="xwero" date="1243594797"]It's the delete method that doesn't add the the joins so you have to do

Code:
$this->db->query('DELETE pages, pages_module, content 

                        FROM pages 

                        LEFT JOIN content ON pages_module.Content_id_Content = content.id_Content

                        LEFT JOIN pages_module ON pages_module.Pages_id_Pages = pages.id_Pages

                        WHERE pages.id_Pages = '.$this->db->escape($id));

[/quote]

I have try your code but i get then the following error.

A Database Error Occurred

Error Number: 1054

Unknown column 'pages_module.Content_id_Content' in 'on clause'

DELETE pages, pages_module, content FROM pages LEFT JOIN content ON pages_module.Content_id_Content = content.id_Content LEFT JOIN pages_module ON pages_module.Pages_id_Pages = pages.id_Pages WHERE pages.id_Pages = '3'

and if i change the code to this:

Code:
$this->db->query('DELETE pages, pages_module, content

                        FROM pages, pages_module, content

                        LEFT JOIN content ON pages_module.Content_id_Content = content.id_Content

                        LEFT JOIN pages_module ON pages_module.Pages_id_Pages = pages.id_Pages

                        WHERE pages.id_Pages = '.$this->db->escape($id));

I get the following error:

A Database Error Occurred

Error Number: 1066

Not unique table/alias: 'content'

DELETE pages, pages_module, content FROM pages, pages_module, content LEFT JOIN content ON pages_module.Content_id_Content = content.id_Content LEFT JOIN pages_module ON pages_module.Pages_id_Pages = pages.id_Pages WHERE pages.id_Pages = '3'

How do i fix it.

Greetz

Delete record in 3 tables - El Forum - 05-29-2009

[eluser]xwero[/eluser]
The left joins need to be switched. If the pages_module table isn't known how can it connect to another table based on a page_module field.

Code:
$this->db->query('DELETE pages, pages_module, content

                  FROM pages

                  LEFT JOIN pages_module ON pages_module.Pages_id_Pages = pages.id_Pages      

                  LEFT JOIN content ON pages_module.Content_id_Content = content.id_Content

                  WHERE pages.id_Pages = '.$this->db->escape($id));

Delete record in 3 tables - El Forum - 05-29-2009

[eluser]René Tuin[/eluser]
[quote author="xwero" date="1243602663"]The left joins need to be switched. If the pages_module table isn't known how can it connect to another table based on a page_module field.

Code:
$this->db->query('DELETE pages, pages_module, content

                  FROM pages

                  LEFT JOIN pages_module ON pages_module.Pages_id_Pages = pages.id_Pages      

                  LEFT JOIN content ON pages_module.Content_id_Content = content.id_Content

                  WHERE pages.id_Pages = '.$this->db->escape($id));

[/quote]

It works.
Thank you so must, I almost lost hope.
But you fix it.

Greetz

Delete record in 3 tables - El Forum - 06-05-2009

[eluser]René Tuin[/eluser]
Hi,

On some kind off way, my query is not working anymore.

This is my query:

Code:
$this->db->query('DELETE pages, pages_module, content

                  FROM pages

              LEFT JOIN pages_module ON pages_module.Pages_id_Pages = pages.id_Pages

                  LEFT JOIN content ON pages_module.Content_id_Content = Content.id_Content                        

                  WHERE pages.id_Pages = '.$this->db->escape($id));

My db is this (see link):

http://www.diovisuals.com/irsforum/download/file.php?id=2&mode=view

I hope someone can help me.

Greetz