Delete record in 3 tables

[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é

[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

[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');

[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

[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.

[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));

[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

[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));

[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

[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/downl...&mode=view

I hope someone can help me.

Greetz