How to Delete the parent node, and child nodes/leafs for a db/tbl in php

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



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: How to Delete the parent node, and child nodes/leafs fora db/tbl in php

--=-75HcNjqA8LSxkae7wtGh
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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



--=-75HcNjqA8LSxkae7wtGh--

RE: 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:ash@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: How to Delete the parent node, and child nodes/leafs for a

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-co nstraints.html

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: How to Delete the parent node, and child nodes/leafs fora 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