Re: [PHP] How to Delete the parent node, and child nodes/leafs for a db/tbl in php
Ashley Sheridan wrote: > On Fri, 2009-10-16 at 08:34 -0700, bruce wrote: > >> Hi. >> >> I've got a situation where I have a couple of tables. The relationship >> between the tables is one of parent/child. I'm trying to figure out the best >> approach to being able to delete the associated children in the child tbls, >> of a given parentID in the parentTBL... >> >> I've checked into various sites/articles on the 'net.. but i'm not sure how >> best to accomplish this... >> >> I'm using php as the interface language to the test tbls.. >> >> Any pointers/articles/test code (code/schema) would be helpful... >> >> Thanks >> >> >> > > > Well if the tables truly have a relationship, then no PHP is necessary. > > I assume that to connect the 'parent' row with the 'children' rows of > the secondary table you've used an identifying field. For example: > > users table: > user_id > forename > surname > > emails table: > email_id > user_id > email_address > > So in the above example, a user on the users table could have several > entries in the emails table for each email address they have. > emails.user_id will match up users.user_id so you can run a query like > > DELETE FROM emails WHERE user_id=id > > Is this any help? > > > Thanks, > Ash > http://www.ashleysheridan.co.uk > > > Or, if you want to be a little trickier... users table: user_id forename surname DELETE FROM emails_table WHERE user_id = ( SELECT user_id FROM users_table WHERE forename = '$forename' AND surname = '$surname' ) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] How to Delete the parent node, and child nodes/leafs for a db/tbl in php
2009/10/16 bruce : > I've got a situation where I have a couple of tables. The relationship > between the tables is one of parent/child. I'm trying to figure out the best > approach to being able to delete the associated children in the child tbls, > of a given parentID in the parentTBL... > > I've checked into various sites/articles on the 'net.. but i'm not sure how > best to accomplish this... > > I'm using php as the interface language to the test tbls.. > > Any pointers/articles/test code (code/schema) would be helpful... This is a DB question not a PHP question, plus you haven't told us what database and table type you're using. However, you can either delete the relationship yourself (using PHP code), write a trigger to do it semi-manually, or set your database up properly with a foreign key constraint. Assuming you're using MySQL and your table type supports foreign keys, see http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] How to Delete the parent node, and child nodes/leafs for a db/tbl in php
hi ash... thanks for getting back to me. my situation isn't that complex, but i'm looking for an efficient method. i've got a start tbl universityTBL the universityTBL can have either schoolTBL, or deptTBL as the next child tbl the schoolTBL will have deptTBL as the child tbl the deptTBL wil have classTBL as the childTBL... to get around some of the headaches of this approach, i maintain a mapTBL where i have the parent_uuid,child_uuid as entries mapTBL -parent_uuid -child_uuid so my issue is that if i have a given parent_uuid, that i want to delete, as well as the associated leaf/nodes of the parent.. how can this be accomplished? you can't just start to delete the parent_uuid, as you completely miss the underlying/associated nodes/leafs for the parent_uuid... thoughts/comments/etc... -Original Message- From: Ashley Sheridan [mailto:a...@ashleysheridan.co.uk] Sent: Friday, October 16, 2009 8:38 AM To: bruce Cc: php-general@lists.php.net Subject: Re: [PHP] How to Delete the parent node, and child nodes/leafs for a db/tbl in php On Fri, 2009-10-16 at 08:34 -0700, bruce wrote: > Hi. > > I've got a situation where I have a couple of tables. The relationship > between the tables is one of parent/child. I'm trying to figure out the best > approach to being able to delete the associated children in the child tbls, > of a given parentID in the parentTBL... > > I've checked into various sites/articles on the 'net.. but i'm not sure how > best to accomplish this... > > I'm using php as the interface language to the test tbls.. > > Any pointers/articles/test code (code/schema) would be helpful... > > Thanks > > > Well if the tables truly have a relationship, then no PHP is necessary. I assume that to connect the 'parent' row with the 'children' rows of the secondary table you've used an identifying field. For example: users table: user_id forename surname emails table: email_id user_id email_address So in the above example, a user on the users table could have several entries in the emails table for each email address they have. emails.user_id will match up users.user_id so you can run a query like DELETE FROM emails WHERE user_id=id Is this any help? Thanks, Ash http://www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] How to Delete the parent node, and child nodes/leafs for a db/tbl in php
On Fri, 2009-10-16 at 08:34 -0700, bruce wrote: > Hi. > > I've got a situation where I have a couple of tables. The relationship > between the tables is one of parent/child. I'm trying to figure out the best > approach to being able to delete the associated children in the child tbls, > of a given parentID in the parentTBL... > > I've checked into various sites/articles on the 'net.. but i'm not sure how > best to accomplish this... > > I'm using php as the interface language to the test tbls.. > > Any pointers/articles/test code (code/schema) would be helpful... > > Thanks > > > Well if the tables truly have a relationship, then no PHP is necessary. I assume that to connect the 'parent' row with the 'children' rows of the secondary table you've used an identifying field. For example: users table: user_id forename surname emails table: email_id user_id email_address So in the above example, a user on the users table could have several entries in the emails table for each email address they have. emails.user_id will match up users.user_id so you can run a query like DELETE FROM emails WHERE user_id=id Is this any help? Thanks, Ash http://www.ashleysheridan.co.uk