idh63
Posts: 76
|
| Posted: 01/27/2008, 1:44 AM |
|
Hi,
I have two tables with a one-to-many relationnship (one myisam and the other innodb).
The one side table is the table source for my editable grid.
When I delete a record on the one table I need to delete data in the many side also.
How do I retrieve the primary key field value on an editable grid for use in a custom mysql delete query i want to create in After ExcecuteDelete?
The primary key field is called growthid.
thanks
|
 |
 |
mamboBROWN
Posts: 1713
|
| Posted: 01/27/2008, 6:59 AM |
|
idh63
I am assuming that you are using MySQL. What version of MySQL are you using? If you are using MySQL 3.x then you will need to add code in the event BEFORE DELETE or BEFORE EXECUTE DELETE that will take the key field from the current record (that you are trying to delete) and delete any related records in the second table. You will have to use a custom delete query that pulls in the key value. Hopefully this helps you.
|
|
|
|
idh63
Posts: 76
|
| Posted: 01/27/2008, 8:46 AM |
|
Hi manboBROWN,
Thanks for the reply.
I am using mysql 5.2.x and php 5.2.4
I am tempted to just change the one side table to innodb and create a foreign key.
What i am not getting is.. Where is the primary key field stored? In my case growthid. I dont see a hidden field called growthid.
I tried your beforeExecuteDelete suggestion and still, I am not finding the primary key field.
It is obviously there because the editable grids delete works and with debug on, i see the query has WHERE growthid = 100
So do i get the rows key field by $Component->??->GetValue(); ? Because I sure cant seem to find it.
Thanks
|
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 01/27/2008, 10:33 AM |
|
Yes idh63,
as mentioned in your last statement you will need to use $Componet->... to get the key field id of the record that you are about to delete.
|
|
|
|
idh63
Posts: 76
|
| Posted: 01/28/2008, 7:37 AM |
|
Hi,
Thanks mamboBROWN, i was hoping someone knew what came after the $Component->.
I did make a little headway yesterday be re-reading the manual regarding editable grid and custom inserts.
I created a delete query that works in navicat, but not in CCS (no errors, but not deleting either). I worked out that what i probably was looking for to use in the custom code is 'Datasource column ' and the colum is growthid.
using custom insert and debug on, I can see that using custom insert is building the delete query correctly.
DELETE FROM post,transaction USING post,transaction WHERE post.growthid = {gid} AND transaction.growthid = {gid}
the variable {gid} exists. I see the value in the screen output.
also, there is the potential for an error in doing via custom sql. if there are no records in the related table (which is possible) the 'one side' wont get deleted.
thanks
|
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 01/28/2008, 8:43 PM |
|
idh63
Maybe these links may also assist you:
http://docs.codecharge.com/studio32/html/ProgrammingTec...tomSQL.html?toc
http://docs.codecharge.com/studio32/html/ProgrammingTec...Insert.html?toc
|
|
|
|
|
