#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    77
    Rep Power
    11

    Question Is there a simple PHP alternative to MySQL Triggers?


    PHP Version: 5.1.6-20060908
    MySQL Version: 5.0.51

    I'm working on a project with several layers of related data.

    Parent Table
    |_Child Table
    | |_Grand Child Table
    | |_Great Grand Child Table
    |_Second Child
    |
    etc.

    When I delete an entry in any of these tables I want to delete all associated entries in their sub (child) tables based on a key.

    I belevive MySQL Triggers are the best solution; However, I am on a shared server and I do not have permission to create triggers.

    I could write PHP functions with a series of loops for each table but I'm not sure how "elegant" that will be given the number of tables and potential combinations of tasks (for example, if I delete a Parent entry I would need to delete from child, grand child, great grand child and second child, but if i delete a child i only need to delete from th grand child and great grand child).

    Is there an existing alternative solution to Triggers through PHP? If so, can anyone direct me to a good resource?

    I'm not looking for someone to do the work for me, I just need a nudge in the right direction.

    Many thanks,

    Andrew
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,400
    Rep Power
    1688
    If using InnoDB as table type, with relevent foreign keys defined, an ON DELETE CASCADE should do this for you.

    Comments on this post

    • awaddington agrees
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,917
    Rep Power
    1045
    Hi,

    yes, you should definitely do this with foreign keys. If your hoster doesn't support that, grab a few dollars and switch to a better hoster.

    Trying to reimplement this functionality with a home made script will create a total mess of half deleted data -- simply because of the delay. Not to mention that it's horrible with regard to software architecture.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    77
    Rep Power
    11

    Thumbs up


    Thanks Simon and Jacques,

    This looks like a good solution. I am going to switch to innoDB.

    I'll have to do some more reading though. I set up a couple of test tables with the ON DELETE CASCADE set for the parent table linked to the index on the child table and twoissues occur:

    1) when I delete a parent through phpMyAdmin the children are not deleted

    2) when I try to add a new parent I get an error "Cannot add or update a child row: a foreign key constraint fails "

    I'm chalking this up to my inexperience and will continue to read up on potential solutions. I haven't made it all the way through the link you provided - the answer's probbly there somewhere.

    Thanks again for the direction you provided. It's very helpful.

    Cheers
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,917
    Rep Power
    1045
    The child tables need a foreign key pointing to the parent table, because you want them to refer to a valid record in the parent table. If you do it the other way round, you cannot add new rows in the parent table, because the IDs won't be found in the child tables, of course.

    Comments on this post

    • awaddington agrees
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    77
    Rep Power
    11
    Originally Posted by Jacques1
    The child tables need a foreign key pointing to the parent table, because you want them to refer to a valid record in the parent table. If you do it the other way round, you cannot add new rows in the parent table, because the IDs won't be found in the child tables, of course.
    Brilliant! That was it. I had it backwards.

    Cheers,
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,868
    Rep Power
    368
    one more question about this:

    what happens if i have something like this:

    table1 (id1,.....)
    join_table(id1, id2)
    table2(id2....)

    if i now delete something from table1, will table2 row delete also or just table1 and join_table?
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,400
    Rep Power
    1688
    That's a question probably better suited for the mySQL forum.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  16. #9
  17. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,905
    Rep Power
    3969
    If you don't define table2 as having a foreign key to table 1, then it won't be deleted. The row in the join_table would be due to the id1<->id1 relation, but there is no such relation directly between table1 and table2 so no delete would occur.
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud

IMN logo majestic logo threadwatch logo seochat tools logo