CodeIgniter Forums
[SOLVED] Deleting data from two tables simultaneously - 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: [SOLVED] Deleting data from two tables simultaneously (/showthread.php?tid=37809)

Pages: 1 2


[SOLVED] Deleting data from two tables simultaneously - El Forum - 01-21-2011

[eluser]theshiftexchange[/eluser]
Hi everyone,

How do I use active records to delete multiple records across two tables with a one to many relationship in the following scenario?

I have table 1 with a list of "ads". Each ad has a unique "ad_ID" and a "date".

I have table 2 with a list of stuff for the ad, with it having many items for one "ad_ID".

So if I decide to delete all "ads" from table 1 older than a certain date, I need it to delete any corresponding items from table 2 with all the "ad_ID" that just got deleted.

I'm thinking I need some sort of join function? Do joins allow you to delete data across tables?


[SOLVED] Deleting data from two tables simultaneously - El Forum - 01-21-2011

[eluser]Victor Michnowicz[/eluser]
If you setup foreign key constraints in your MySQL database this would be taken care of auto-magically. You will need the InnoDB storage engine for this. MyISAM just aint cool enough.


[SOLVED] Deleting data from two tables simultaneously - El Forum - 01-22-2011

[eluser]theshiftexchange[/eluser]
Hmmm... I'm running myISAM, so for the moment that wont work... but that would be a nice solution Smile


[SOLVED] Deleting data from two tables simultaneously - El Forum - 01-24-2011

[eluser]Victor Michnowicz[/eluser]
Converting a table to InnoDB should be pretty simple if you decide to go that route.

It seems like you can delete from two tables too.

And if you use the mysqli extension you should be able to do multiple statements:
- http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html
- http://us3.php.net/manual/en/mysqli.overview.php


[SOLVED] Deleting data from two tables simultaneously - El Forum - 01-24-2011

[eluser]theshiftexchange[/eluser]
Ok - I've worked it out using a normal SQL query. I tried to convert it to an active record query but it doesnt work for some reason.

function delete_id ($my_ID_to_delete)
{
return $this->db->query("
DELETE t1.*, t2.*
FROM table1 t1, table2 t2
WHERE t1.id = t2.id
AND t1.id = '".$my_ID_to_delete."'");
}


[SOLVED] Deleting data from two tables simultaneously - El Forum - 01-25-2011

[eluser]Victor Michnowicz[/eluser]
Nice. It seems like that should be able to be converted to active records... You may have to pass FALSE as the second parameter to where(). I haven't tested this though.


[SOLVED] Deleting data from two tables simultaneously - El Forum - 01-25-2011

[eluser]Nick_MyShuitings[/eluser]
If you are able to write SQL, then there is almost 0 benefit to using Active Record. you can write a query using Query Bindings (http://ellislab.com/codeigniter/user-guide/database/queries.html) that receive all the security of AR and allow you to write complex queries.

AR looks fancy and satisfies our inner nerd-rage for using object oriented chaining to do our work... but it is a completely useless abstraction layer if your brain is able to handle actual SQL syntax.

(there are numerous threads around AR, and I wrote a large rant a while ago in which I also provided a code modification that lets people add fancy complex nested Where clauses...)


[SOLVED] Deleting data from two tables simultaneously - El Forum - 02-17-2011

[eluser]Unknown[/eluser]
[quote author="theshiftexchange" date="1295943163"]Ok - I've worked it out using a normal SQL query. I tried to convert it to an active record query but it doesnt work for some reason.

function delete_id ($my_ID_to_delete)
{
return $this->db->query("
DELETE t1.*, t2.*
FROM table1 t1, table2 t2
ON t1.id = t2.id
WHERE t1.id = '".$my_ID_to_delete."'");
}[/quote]


[SOLVED] Deleting data from two tables simultaneously - El Forum - 03-14-2012

[eluser]Unknown[/eluser]
I used the above code but get database error

Code:
A Database Error Occurred

Error Number: 1109

Unknown table 't2' in MULTI DELETE

DELETE t1, t2 FROM tech_static_pages t1 tech_navigation t2 ON t1.id = t2.page_id WHERE t1.id = '22'

Filename: E:\wamp\www\techinfi\system\database\DB_driver.php

Line Number: 330


But I resolved it by using JOIN keyword as

Code:
function delete_page($page_id)
{
  return $this->db->query("
    DELETE t1, t2
    FROM tech_static_pages t1 JOIN tech_navigation t2
    ON t1.id = t2.page_id
    WHERE t1.id = '".$page_id."'");
}

My code is working fine using JOIN keyword.

Thanks,
Tikendra Maitry
Tiks.in


[SOLVED] Deleting data from two tables simultaneously - El Forum - 03-14-2012

[eluser]Krystian[/eluser]
If I where you I would create a trigger for DELETE