virtual pedigree

[Tanja]

I´m using this script to show pedigrees, works fine.

Now i want to show a virtual pedigree (for testmating or puppys).

For puppys i add a new table in database with id, father_id, mother_id. All other (living) dogs are in table dog (again with father and mother id). So this is working for dogs who are already in table dog:

             $sql = "SELECT a.father_id, a.mother_id, s.dogname, d.dogname
                     FROM dog a 
                        INNER JOIN dog s ON a.father_id = s.id
                        INNER JOIN dog d ON a.mother_id = d.id
                     WHERE a.id = '$dogid' ";

But my puppy isn´t in table dog... Is there a way to say get father and mother from puppy and then get all other generation from dog?

[ignace]

Selects the puppy, and it's mother and father:

SELECT c.dogname as child, m.dogame as mother, f.dogname as father

FROM dog c

JOIN dog m ON c.mother_id = m.id

JOIN dog f ON c.father_id = f.id

WHERE c.id = '$dogid';

[Tanja]

The puppy is not in table dog ;-)

 

In Table puppy are:id, mother_id, father_id for a dog which should born in future

in table dog are all living dogs with id, father_id, mother_id

So i must do an virtual pedigree for a dog who is not already in database

[ignace]

FROM puppy c

[Tanja]

SELECT a.father_id, a.mother_id, s.dogname, d.dogname
 FROM puppy a 
JOIN dog s ON a.father_id = s.id
JOIN dog d ON a.mother_id = d.id
 WHERE a.id = '$dogid'

all variations won´t work... With this statement i get father an mother (at right position) but no grandparents; for all empty ancestors i get resource(xx) of type (mysql result) 0.

With your postet statement i get Unknown column 'm.dogame' in 'field list'.

I think the problem is to switch between puppy and dog. First get mother_id(=dog.id) and father_id(=dog.id) from puppy and then switch to dog to get all other information and puppy.id can´t use in table dog, because maybe there is another dog with same id

Edited March 17, 2013 by Tanja

[ignace]

If you also need grandparents you need to do extra joins for both parents:

SELECT

  c.dogname as child, m.dogame as mother, f.dogname as father,

  mm.dogname as mother_mother, mm.dogname as mother_father

  ..

FROM puppy c

JOIN dog m ON c.mother_id = m.id

JOIN dog f ON c.father_id = f.id

LEFT JOIN dog mm ON m.mother_id = mm.id

LEFT JOIN dog mf ON m.father_id = mf.id

LEFT JOIN dog fm ON f.mother_id = fm.id

LEFT JOIN dog ff ON f.father_id = ff.id

WHERE c.id = '$dogid';

Edited March 17, 2013 by ignace

[Barand]

You could try this

 

Add an extra parameter to the PrintTree() function

 

 

function printTree($dogid, $name, $N, $max, $virtual=0) {
 
    ...
    $tablename =  ($virtual && $N==0) ? 'puppy' : 'dog';
 
    $sql = "SELECT a.father_id, a.mother_id, s.dogname, d.dogname
    FROM $tablename a 
    JOIN dog s ON a.father_id = s.id
    JOIN dog d ON a.mother_id = d.id
    WHERE a.id = '$dogid' ";
 
    ...
}

 

Call function with virtual set to 1 for the puppies

[Tanja]

Version of ignace : can I use this solution in my script? I don´t think so...

Version of Barand: will give me id of puppy, but all other id´s are empty....

 

What about call this script two times (one for father, one for mother)?

All we be shown, but i have two tables with unequal witdhs. I comment out the pictures. Can I put them together in one table?

$maleid = $row['fatherid'];
$malename=$row['father'];


function printTreemale($maleid, $malename, $N, $max) {

         if ($malename == '') $malename = ' ';

         // calculate how many rows the cell should span

         $rspan = pow(2, $max-$N);
			//start	 für bild existiert
		// if(file_exists("photo/".$maleid."/".$maleid.".jpg"))
		// $img = "photo/".$maleid."/".$maleid.".jpg";
			// else
		// $img = "platzhalter.png";
		//ende	
		$malename = "<a href='dog.php?id=$maleid'>$malename</a>";  
         if ($rspan > 1)
             echo "\t<td rowspan='$rspan' >$malename</td>\n";
         else
             echo "\t<td>$malename</td>\n";

         // check for last cell in row
         if ($N == $max) echo "</tr>\n<tr>\n";

         // print parent trees, sire then dam
         if ($N < $max) {
             $sql = "SELECT a.father_id, a.mother_id, s.dogname, d.dogname
                     FROM dog a 
                        INNER JOIN dog s ON a.father_id = s.id
                        INNER JOIN dog d ON a.mother_id = d.id
                     WHERE a.id = '$maleid' ";
             $res = mysql_query($sql);
             list($s, $d, $sn, $dn) = mysql_fetch_row($res);
             printTreemale($s, $sn, $N+1, $max);
             printTreemale($d, $dn, $N+1, $max);
         }
}

function pedigreemale($maleid, $malename) {
//$generation = (int)($_GET['gens']);
  if(!isset($_GET['gens'])) {
    $generation = "3";  } 

  else {
    $generation = (int)($_GET['gens']);  }	
	
    echo "<TABLE border='1' width='100%'>\n";
    echo "<tr>\n";
    echo "</tr>\n<tr>\n";

    printTreemale($maleid, $malename, 0, $generation);

    echo "</tr>\n</TABLE>\n";

}


$femaleid = $row['motherid'];
$femalename=$row['mother'];


function printTreefemale($femaleid, $femalename, $N, $max) {

         if ($femalename == '') $femalename = ' ';

         // calculate how many rows the cell should span

         $rspan = pow(2, $max-$N);
			//start	 für bild existiert
		// if(file_exists("photo/".$femaleid."/".$femaleid.".jpg"))
		// $img = "photo/".$femaleid."/".$femaleid.".jpg";
			// else
		// $img = "platzhalter.png";
		//ende	
		$femalename = "<a href='dog.php?id=$femaleid'>$femalename</a>
		
		";  
         if ($rspan > 1)
             echo "\t<td rowspan='$rspan' >$femalename</td>\n";
         else
             echo "\t<td>$femalename</td>\n";

         // check for last cell in row
         if ($N == $max) echo "</tr>\n<tr>\n";

         // print parent trees, sire then dam
         if ($N < $max) {
             $sql = "SELECT a.father_id, a.mother_id, s.dogname, d.dogname
                     FROM dog a 
                        INNER JOIN dog s ON a.father_id = s.id
                        INNER JOIN dog d ON a.mother_id = d.id
                     WHERE a.id = '$femaleid' ";
             $res = mysql_query($sql);
             list($s, $d, $sn, $dn) = mysql_fetch_row($res);
             printTreefemale($s, $sn, $N+1, $max);
             printTreefemale($d, $dn, $N+1, $max);
         }
}

function pedigreefemale($femaleid, $femalename) {
//$generation = (int)($_GET['gens']);
  if(!isset($_GET['gens'])) {
    $generation = "3";  } 

  else {
    $generation = (int)($_GET['gens']);  }	
	
    echo "<TABLE border='1' width='100%'>\n";
    echo "<tr>\n";
    echo "</tr>\n<tr>\n";

    printTreefemale($femaleid, $femalename, 0, $generation);

    echo "</tr>\n</TABLE>\n";

}



pedigreemale($maleid, $malename);
pedigreemale($femaleid, $femalename);