Recursive MYSQL Select
Posted on 2003-03-27
I have a MYSQL db that holds all of my family information. In the db, I'm tracking parent/child relationships as well as husband/wife relationships. Here are the relevant columns in my table for this question:
TABLE NAME - PERSON
I've created a PHP calendar on which I want to show family member's birthdays. The thing is, I only want to see birthdays for family members where the relation is not too distant (as much as I love my third and fourth cousins, I don't think I care much about their b-days). The site requires a login so at any given time I have the P_ID for the person logged in.
I want to select the following relationships for any person that logs in.
1.) My birthday
2.) My children's birthdays
3.) My children's children's birthdays (grandkids)
4.) My parents
5.) My parent's children (my siblings)
6.) My parent's children's children (my nieces & nephews)
I know I could jimmy-rig this and just run one select to get my kids. Then in PHP, loop through those results and for each child run another select to get their children. Then do the same thing using my parents as the starting point. I am looking for a better MYSQL query than this. I think I can be done in one query, or two at the most (one for me, my kids, and grandkids; and one for parents, their kids and grandkids).
To simplify matters, really all I need to find out is given a P_ID, how can I find that P_ID's children and grandchildren?
If I start my select
SELECT P_ID, P_FIRSTNAME, P_BIRTHDAY
WHERE P_PARENT_ID = $myId
(this gets all my kids. now I need to do some kind of 'foreach' of my kids, find their kids)
I hope this is enough details. Thanks in advance