Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1265
  • Last Modified:

Recursive MYSQL Select

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
P_ID
P_FIRSTNAME
P_BIRTHDAY
P_PARENT_ID
P_SPOUSE_ID

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
FROM PERSON
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
0
definitivecoder
Asked:
definitivecoder
2 Solutions
 
VGRCommented:
sorry, I can't help you, I'm doing exactly this and I haven't finished myself sorting things out 8-)
I'm building a family site ;

I'm using currently a "users" table, and "relationship tables" for relations "ischildof","ismarriedwith" and those two are the only ones necessary to build a complete family tree. (althought I may add "issiblingof", etc to ease data analysis)

FYI, I solved this problem of birthdays (and other thingies) by offering people logged-in to subscribe to birthdays, either individual per individual, or by surname, or by family.

best regards,
0
 
Howler_FishCommented:
Hi,

I think you need to create a second table that holds the relationship between people.  Effectively I have many relationships, just as many people have many relationships to me.  This new table could look something like

From_P_ID  
To_P_ID
Relationship

Assuming I had a P_ID of 1, and my two children were P_ID 2 and 3.   The table data would look something like:

1 2 Father
1 3 Father
2 1 Son
3 1 Daughter

You should then be able to run a select along the lines of

SELECT A.P_ID, A.P_FIRSTNAME, A.P_BIRTHDAY
FROM PERSON A, RELATIONSHIP B
WHERE B.FROM_P_ID = $myID
AND A.P_ID = B.TO_P_ID
AND B.RELATIONSHIP IN ('Father','Brother','Sister')

You'd want to refine the details a bit better, but it should give you roughly what you are after.  The other problem is that if your cousin ever became your step-father you would potentially return him twice, but at that point I think that would be the least of your worries.... :P


HF
0
 
snoyes_jwCommented:
Here's one (kinda kludgy) way:

$myParent = SELECT p_parent_id FROM person WHERE p_id = $myId;

SELECT p2.* FROM person p1
LEFT JOIN person p2 on p1.p_id = p2.p_parent_id
WHERE p2.p_id IS NOT NULL
AND (p1.p_parent_id = $myId OR p1.p_id = $myId OR p1.id = $myParent OR p1.p_parent_id = $myParent);

If you just want the children and grandchildren and not siblings and niece/nephew, take out the (p1.id = $myParent OR p1.p_parent_id = $myParent)

You might have to add a DINSTINCT or a GROUP BY to avoid duplicate entries.  How do you deal with the fact that everybody has two parents, but you have only one parent id?  You might create a father_id and a mother_id.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
snoyes_jwCommented:
ping
0
 
snoyes_jwCommented:
0
 
snoyes_jwCommented:
No comments in a while.  Did you find an answer?  If you solved it yourself, be sure to post in the Community Support area for a delete/refund.
0
 
snoyes_jwCommented:
It seems Howler_Fish contributed more than VGR...
0
 
periwinkleCommented:
It would appear that I seleted the wrong answer - good catch;  I'll modify my recommendation (thanks).
0
 
snoyes_jwCommented:
Thanks, and thanks for your cleanup work!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now