How to delete related records

[eshelman.carl]

My application has a master "Batch" with several one to many relationships. I would like to allow my users to delete Batches if they so desire but dont want to wind up with a bunch of orphan records in the related tables. I assume this woudl be accomplished via JS run on deletion of a Batch record but i have no idea where to start in writing this. Any assistance would be appreciated.

Thanks, Carl.

 

[rob]

Hi Carl

No easy way to do this currently - its on our to-do list though

In the meantime you have a couple of solutions

1) Let mySQL deal with it. If you have mySQL 5 installed you have the ability to create triggers. Triggers are secondary sql statements that are run when an sql statement occurs. details of how to make these are here - http://dev.mysql.com/doc/refman/5.0/en/triggers.html

- So in your case you could create a "delete related records" query that was attached to a trigger which runs when a master record is deleted.

2) Create a custom php script to delete the rows. Observe in javascript the delete button and trigger via ajax the php script -
For this you can use the user_ajax.php script - details of the principals of getting this to work are in this FAQ
http://fabrikar.com/index.php?option=com_smf&Itemid=9&topic=1878.0

Rob

 

[eshelman.carl]

Any chance I could talk you guys into helping with this? This appears to be a bit beyond my capibilities. Hacking is one thing, creating from scratch is quite another. How much would you charge to create a basic script that would delete the related records from 1 table that I could then modify to meet my exact requirements?

Thanks, Carl.

 

[cheesegrits]

What version of MySQL do you have?

If you are running MySQL 5, you are in luck. As Rob, says, this could very easily be done with triggers in MySQL 5, and I'd be happy to help you with it for free.

If you are still on MySQL 4, and need to use option 2), it'd be a couple of hours work. I have some folk who have been waiting for me to do stuff for them for ages, so I'd have to get them sorted out first.

A third option might be that you "sponsor" us to add this features to Fabrik as standard, so we add a "Delete joined table rows" option to Fabrik tables. I'd have to talk to Rob to get a better idea of how long this would take us to code and test. We've talked about it before, but never really looked closely at how easy / hard it'll be to do.

-- hugh

 

[eshelman.carl]

Unfortunately my hosting provider will not step up to 5. Says it is not stable enough. That is a load of... lets just say I was not happy about that response.

I would be willing to sponsor having the functionality added to Fabrik if its not too expensive. Let me know what you think it will run. If I can afford it we'll do it. If not, I will hack something together. ;-)

 

[cheesegrits]

Sounds like time to move hosts. Vote with your feet

Just out of interest, do they provide PHP5 yet?

I'll talk to Rob and give you an estimate. Won't be too much, as this is something we were planning to do anyway (eventually!).

-- hugh