Using PHP and MySQL for family tree

How to find parents of parents

[mn1dbp]

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]

[Knowles]

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.

[timster]

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.

[mn1dbp]

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.

[coopster]

Somebody say self join?

SELECT 
 t1.*, 
 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 
;

[mn1dbp]

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:

<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query0 = "select sire,dam,dob,horse from horse where id=$id";
$numresults0=mysql_query($query0);
$numrows0=mysql_num_rows($numresults0);
$result0 = mysql_query($query0) or die("Error - Results 0");

$sire1=mysql_result($result0,$i,"sire");
$dam1=mysql_result($result0,$i,"dam");
$horse0=mysql_result($result0,$i,"horse");
$dob0=mysql_result($result0,$i,"dob");

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

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

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

$sire2=mysql_result($result2,$i,"sire");
$dam2=mysql_result($result2,$i,"dam");
$horse1=mysql_result($result2,$i,"horse");
$dob1=mysql_result($result0,$i,"dob");

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

?>