Welcome to WebmasterWorld Guest from

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Using PHP and MySQL for family tree

How to find parents of parents

7:49 pm on Sep 21, 2004 (gmt 0)

New User

10+ Year Member

joined:Mar 2, 2004
votes: 0

I am fairly new to PHP and MySQL and learning by making a site and experimenting.

The site is a database of horses with colunms of ID, Horse Name, DOB, Sex, Sire & Dam.

Now for each horse i would like a page that shows the sires and dams back a few generations - like a family tree.

Now i have no problem be able to display the Sire and Dam of a horse but i am not sure how i go about finding the sire and dam of this sire and so on to create the family tree. Much like this page - <removed> I know that this uses PHP Ged view but i dont want anything quite that complicated.

All horses are contained in the same table and for some there might not even be enough data to create the full family tree back say 3 generations so i would need to print a '?' or something.

How would it be best to achive this - would it involve multiple queries?

[edited by: jatar_k at 8:25 pm (utc) on Sep. 21, 2004]
[edit reason] removed url [/edit]

8:03 pm on Sept 21, 2004 (gmt 0)

Preferred Member

10+ Year Member

joined:Apr 17, 2002
votes: 0

I would say multiple queries would be the way to go.
My over the top unoptimized style would say
query1 = data sire1 dam1
query2 = SELECT * FROM horses WHERE name='$sire1'"
Something to that effect. That is if Sire and Dam are actually text fields and not numbers. If it is a number change name to id or what ever the number represents. I am sure if you give it a little bit someone may have a more optimized way to do this.
8:17 pm on Sept 21, 2004 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member 10+ Year Member

joined:Aug 1, 2003
votes: 0

Multiple queries would definitely be simpler than some extended self-join. Querying a database for one record based on primary key is pretty quick and efficient.

I'd suggest creating a function that grabs the information about the horse, including the Mommy & Daddy horses id's, when supplied the id. (Then, of course, just call the function again with the ancestor's id.)

A "real" programmer might bring up that this is a good job for recursion but it'd be a little tricky presenting the data attractively.

8:33 pm on Sept 21, 2004 (gmt 0)

New User

10+ Year Member

joined:Mar 2, 2004
votes: 0

Any ideas what this function would look like or perhaps an example web page that shows such a function and how it works. I'd rather learn than simply copy and paste some code.

Also apologies for posting the URL.

2:16 am on Sept 22, 2004 (gmt 0)


WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
votes: 2

Somebody say self join?
t2.name AS daddy,
t3.name AS mommy
FROM horses AS t1,
horses AS t2,
horses AS t3
WHERE t1.sire = t2.id
AND t1.dam = t3.id
2:27 pm on Sept 22, 2004 (gmt 0)

New User

10+ Year Member

joined:Mar 2, 2004
votes: 0

Ok so i had a go using the little knowledge that i have and came up with something that works but would be very long winded and i am sure it could be but down. Of the sample code below only produces the horse and then its Sire - i could then carry on to build up the horses but like i say i think its long winded and there must be a easier way.

Here is code, ive included num_row because when i make it up properly i will use this in a if else statement to print something when there is no results:

@mysql_select_db($database) or die( "Unable to select database");
$query0 = "select sire,dam,dob,horse from horse where id=$id";
$result0 = mysql_query($query0) or die("Error - Results 0");


echo "$dob0";
echo "$horse0";
echo "<br><br>";

$query1 = "select id from horse where horse=\"$sire1\"";
$result1 = mysql_query($query1) or die("Result1");

$query2 = "select sire,dam,dob,horse from horse where id=$id1";
$result2 = mysql_query($query2) or die("Bad Error");


echo "$dob1";
echo "$horse1";



Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members