homepage Welcome to WebmasterWorld Guest from 54.166.123.2
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Using PHP and MySQL for family tree
How to find parents of parents
mn1dbp




msg:1290623
 7:49 pm on Sep 21, 2004 (gmt 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]

 

Knowles




msg:1290624
 8:03 pm on Sep 21, 2004 (gmt 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.

timster




msg:1290625
 8:17 pm on Sep 21, 2004 (gmt 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.

mn1dbp




msg:1290626
 8:33 pm on Sep 21, 2004 (gmt 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.

coopster




msg:1290627
 2:16 am on Sep 22, 2004 (gmt 0)

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




msg:1290628
 2:27 pm on Sep 22, 2004 (gmt 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:

<?
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";

?>

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved