Page 1 of 1

getting messages from nested categories

Posted: Mon Feb 16, 2009 3:44 am
by rami
well
i sought and read and tested many queries from many tutorials but could not succeded...
well what i have
message_category
cat_id
cat_name
pcat_id


messages
mess_id
category
message



eg
cat_id-------name-------pcat_id
1 -------general----------0
2-------Notice-------------0
3-------Entertainment-------1
4-------Sports ------- ---1
5-------Football ----------4
6-------men footbal-------5
7-------film ----------------3

so graphically


category

general(1) ___________________________________________________________________________ Notice(2)
--------- entertainment(3)
----------------------film(7)
----------Sports (4)
----------------------Football(5)
------------------------------------menfootball(6)

i have decided the level will be 4 level deep only

message can reside in any where means
message_id ----- category ----- message
1 ______ 6_________ men football message
2 ______ 5 _________general football message
3 ______ 4 _________sports message
4 ______ 7 _________ film message


now i need serious help for some sql
1)i have page all_messages.php
suppose cat_id=1 is passed from GET
how i can get all the messages (to the deepest level categories) that belongs to general(parent) ie cat_id 1 in this case

2)suppose the user now sends cat_id 4 ...that is sports ...i need messages that is from categories below sports...

3)what can i do on delete ...so that user cannot delete suppose sports before deleting football...and menfootball....


i saw lft and rgt implementation of it as well,but found little complictaed and the database design is already made....would be very very grateful if somebody can write these 3 queries for me
please dont show vauge hierachical relations tutorials...
thanks