[SOLVED] Why doesn't this pedigree work?

forTheDogs · August 11, 2007

Greetings! Most of my database pages are generated through a program but it does not generate the pedigree for me.

Until around May 15, I had never heard of PHP or mySql so, as you can probably guess, I am having a difficult time trying to get this to work.

With the help of several people and a great deal of reading on my part, here is what I have so far - I do not get errors any more but it does not print out a pedigree either.

[You can look at the current result, if it would help, at fortheloveoflabradors.com/labradors/login.php and log in as 'guest'. To get a good example, search for BrookHill Mike - he has 8 generations entered].

Thanks so much even if you just took the time to look at this!!! For me, it is very daunting!!

global $conn;

$message =

"<table cellspacing=1 cellpading=1><tr><td colspan=4 align=center class=blackshade>Pedigree Table</td></tr>";

$sql = "SELECT RegName, SireID, DamID FROM Pedigrees WHERE ID=";

$DogQ = $sql . $values["ID"]; //appends ID to this query

$DogR = db_query($DogQ,$conn);

if ($DogR) {

while ($row = mysql_fetch_array ($DogR, MYSQL_ASSOC)) {

$pedigree = array (

'RegName'=>$row['RegName'],

'SireID'=>$row['SireID'],

'DamID'=>$row['DamID']);

//$message.= "<tr><td>regname - ".$row['RegName']."</td><td>sireid - ".$row['SireID']."</td><td>damid - ".$row['DamID']."</td>";

$message.= "<tr><td rowspan=8 class=shade>RegName - ".$row['RegName']."</td>";

// get Sire info

$SireQ = $sql . $row['SireID'];

$SireR = mysql_query ($SireQ);

if ($SireR) {

$SireRow = mysql_fetch_array ($SireR, MYSQL_ASSOC);

$pedigree['Sire'] = $SireRow['RegName'];

$pedigree['SireGrandSireID'] = $SireRow['SireID'];

$pedigree['SireGrandDamID'] = $SireRow['DamID'];

$message.= "<td rowspan=4 class=shade>Sire - ".$SireRow['RegName']."</td>";

//$message.= "<tr><td rowspan=2>Sire - ".$SireRow['RegName']."</td><td>SireGrandSireID - ".$SireRow['SireID']."</td><td>SireGrandDamID - ".$SireRow['DamID'].". </td>";

//Get Sire's GrandSire info

$SireGrandSireQ = $sql . $SireRow['SireID'];

$SireGrandSireR = mysql_query ($SireGrandSireQ);

if ($SireGrandSireR ) {

$SireGrandSireRow = mysql_fetch_array ($SireGrandSireR, MYSQL_ASSOC);

$pedigree['Sire'] = $SireGrandSireRow['RegName'];

$pedigree['SireGrandSireID'] = $SireGrandSireRow['SireID'];

$pedigree['SireGrandDamID'] = $SireGrandSireRow['DamID'];

// $message.= "<td>Sire - ".$SireGrandSireRow['RegName']."</td><td>SireGrandSireID - ".$SireGrandSireRow['SireID']."</td><td>SireGrandDamID - ".$SireGrandSireRow['DamID']."</td>";

$message.= "<td class=shade rowspan=2>GrandSire - ".$SireGrandSireRow['RegName']."</td>";

//Get Sire's GGrandSire info

$SireGGrandSireQ = $sql . $SireGrandSireRow['SireID'];

$SireGGrandSireR = mysql_query ($SireGrandSireQ);

if ($SireGGrandSireR) {

$SireGGrandSireRow = mysql_fetch_array ($SireGrandSireR, MYSQL_ASSOC);

$pedigree['Sire'] = $SireGGrandSireRow['RegName'];

$pedigree['SireGrandSireID'] = $SireGGrandSireRow['SireID'];

$pedigree['SireGrandDamID'] =$SireGGrandSireRow['DamID'];

$message.= "<td class=shade>GrandGrandSire - ".$SireGrandSireRow['RegName']."</td>";

}//End if ($GGrandSireR)

//Get Sire's GGrandDam info

$SireGGrandSireQ = $sql . $SireGrandSireRow['DamID'];

$SireGGrandSireR = mysql_query ($SireGrandSireQ);

if ($SireGGrandSireR) {

$SireGGrandSireRow = mysql_fetch_array ($SireGrandSireR, MYSQL_ASSOC);

$pedigree['Sire'] = $SireGGrandSireRow['RegName'];

$pedigree['SireGrandSireID'] = $SireGGrandSireRow['SireID'];

$pedigree['SireGrandDamID'] =$SireGGrandSireRow['DamID'];

$message.= "<tr><td class=shade>GrandGrandDam - ".$SireGrandSireRow['RegName']."</td>";

}//End if ($GGrandSireR)

}//end if ($SireGrandSireR)

gurroa · August 11, 2007

Shouldn't you be using $SireGGrandSireRow in the last two levels?

And from middle of your code you are writing data into pedigree array under the same key values.

if ($SireGGrandSireR) {
  $SireGGrandSireRow = mysql_fetch_array ($SireGrandSireR, MYSQL_ASSOC);
  $pedigree['DamGrandGrand'] = $SireGGrandSireRow['RegName'];
  $pedigree['DamGrandGrandSireID'] = $SireGGrandSireRow['SireID'];
  $pedigree['DamGrandGrandDamID'] =$SireGGrandSireRow['DamID'];
  $message.= "<td class=shade>GrandGrandDam - ".$SireGGrandSireRow ['RegName']."</td>";   
}

I would use other way to get those data.

Split this task into two parts - get the data, output data.

  $pedigree = array();
  
  $sql    = "SELECT RegName, SireID, DamID FROM Pedigrees WHERE ID in (%s)";
  
  $result = mysql_query(sprintf($sql, $values['ID']));

  if ($result) {
    $row = mysql_fetch_assoc($result);
    $pedigree[$row['ID']] = $row;
    
    $newIDs = array();
    if (!empty($row['SireID'])) $newIDs[] = $row['SireID'];
    if (!empty($row['DamID'])) $newIDs[]  = $row['DamID'];
    
    $level = 1;
    $maxlevel = 3;
    while (count($newIDs) > 0 && $level <= $maxlevel) {
      
      // select all current anc.
      $newIDs = array();
      $ancres = mysql_query(sprintf($sql, Implode(", ", $newIDs)));
      if ($ancres) {
        while ($row = mysql_fetch_assoc($ancres)) {
          $pedigree[$row['ID']] = $row;
          if (!empty($row['SireID'])) $newIDs[] = $row['SireID'];
          if (!empty($row['DamID'])) $newIDs[]  = $row['DamID'];
        }
      }
      
      ++$level;
    }

    $row = $pedigree[$values['ID']];
    // prepare empty output assoc array with first Reg value.
    $outped = array('Reg' => $row['RegName'], 
            'Sire' => '', 'Dam' => '', 'SireSire' => '', 'SireDam' => '', 'DamSire' => '', 'DamDam' => ''
            ....
            );
    if (!empty($row['SireID']) && isset($pedigree[$row['SireID']])) {
      $sirerow = $pedigree[$row['SireID']];
      $outped['Sire'] = $sirerow['RegName'];
      
      if (!empty($sirerow['SireID']) && isset($pedigree[$sirerow['SireID'])) {
        $SireSire = $pedigree[$sirerow['SireID']];
        $outped['SireSire'] = $SireSire['RegName'];
        if (!empty(......
          // another anc.
        }          
      }
      if (!empty($sirerow['DamID']) && isset($pedigree[$sirerow['DamID']])) {
        $SireDam = $pedigree[$sirerow['DamID']];
        $outped['SireDam'] = $SireDam['RegName'];
        if (!empty(...
          // another anc.
        }
      }  
      
    } 
    
    if (!empty($row['DamID']) && isset($pedigree[$row['SireID']])) {
      $damrow = $pedigree[$row['SireID']];
      $outped['Dam'] = $damrow['RegName'];
      
      if (!empty($damrow['SireID']) && isset($pedigree[$damrow['SireID']])) {
        $DamSire = $pedigree[$damrow['SireID']];
        $outped['DamSire'] = $DamSire['RegName'];
        if (!empty(....
          // another anc.
        }
      }
      
      ....
    }
  
    // output data
    $message  = "<table cellspacing=1 cellpading=1><tr><td colspan=4 align=center class=blackshade>Pedigree Table</td></tr>";
    $message .= "<tr><td colspan=8 class=shade>RegName - ".$outped['Reg']."</td></tr>";
    $message .= "<tr><td rowspan=4 class=shade>Sire - ".$outped['Sire']."</td><td rowspan=4 class=shade>Dam - ".$outped['Dam']."</td></td>";
    $message .= "<tr><td rowspan=2 class=shade>SireSire - ".$outped['SireSire']."</td>".
                    "<td rowspan=2 class=shade>SireDam - ".$outped['SireDam']."</td>".
                    "<td rowspan=2 class=shade>DamSire - ".$outped['DamDam']."</td>".
                    "<td rowspan=2 class=shade>DamDam - ".$outped['DamDam']."</td>".
                    "</tr>";
    ....

  }

Barand · August 11, 2007

Here's something I wrote a couple of years ago which may be of use

My data

CREATE TABLE dogtable (
`id` int(11)  NOT NULL auto_increment,
`dogname` varchar(20)  NOT NULL ,
`sire` int,
`dam` int ,
PRIMARY KEY(id)
);

INSERT INTO dogtable VALUES ('1', 'dog A', '2', '3');
INSERT INTO dogtable VALUES ('2', 'dog B', '4', '5');
INSERT INTO dogtable VALUES ('3', 'dog C', '6', '7');
INSERT INTO dogtable VALUES ('4', 'dog E', '8', '9');
INSERT INTO dogtable VALUES ('5', 'dog F', '10', '11');
INSERT INTO dogtable VALUES ('6', 'dog G', '12', '13');
INSERT INTO dogtable VALUES ('7', 'dog H', '14', '15');
INSERT INTO dogtable VALUES ('8', 'dog I', '16', '17');
INSERT INTO dogtable VALUES ('9', 'dog J', '18', '19');
INSERT INTO dogtable VALUES ('10', 'dog K', '20', '21');
INSERT INTO dogtable VALUES ('11', 'dog L', '22', '23');
INSERT INTO dogtable VALUES ('12', 'dog M', '24', '25');
INSERT INTO dogtable VALUES ('13', 'dog N', '', '');
INSERT INTO dogtable VALUES ('14', 'dog O', '', '');
INSERT INTO dogtable VALUES ('15', 'dog P', '', '');
INSERT INTO dogtable VALUES ('16', 'dog Q', '', '');
INSERT INTO dogtable VALUES ('17', 'dog R', '', '');
INSERT INTO dogtable VALUES ('18', 'dog S', '', '');
INSERT INTO dogtable VALUES ('19', 'dog T', '', '');
INSERT INTO dogtable VALUES ('20', 'dog U', '', '');
INSERT INTO dogtable VALUES ('21', 'dog V', '', '');
INSERT INTO dogtable VALUES ('22', 'dog W', '', '');
INSERT INTO dogtable VALUES ('23', 'dog X', '', '');
INSERT INTO dogtable VALUES ('24', 'dog Y', '', '');
INSERT INTO dogtable VALUES ('25', 'dog Z', '', '');

The code to print pedigree chart

<?php
require 'db.php';

function printTree($dogid, $name, $N, $max) {
         ##########################################
         # recursive routine to print cells in
         # pedigree chart
         #
         # Parameters
         #    - id
         #    - name
         #    - generation number
         #    - max previous generations to display
         ##########################################

         if ($name == '') $name = ' ';

         // calculate how many rows the cell should span

         $rspan = pow(2, $max-$N);

         if ($rspan > 1)
             echo "\t<td rowspan='$rspan' >$name</td>\n";
         else
             echo "\t<td>$name</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.sire, a.dam, s.dogname, d.dogname
                     FROM dogtable a 
                        INNER JOIN dogtable s ON a.sire = s.id
                        INNER JOIN dogtable d ON a.dam = d.id
                     WHERE a.id = '$dogid' ";
             $res = mysql_query($sql);
             list($s, $d, $sn, $dn) = mysql_fetch_row($res);
             printTree($s, $sn, $N+1, $max);
             printTree($d, $dn, $N+1, $max);
         }
}

function pedigree($id, $name) {

    echo "<TABLE border='1' width='100%'>\n";
    echo "<tr>\n";
    echo "<th width='20%'>Dog</th>\n";
    echo "<th width='20%'>Parents</th>\n";
    echo "<th width='20%'>Grand-Parents</th>\n";
    echo "<th width='20%'>Great<br>Grand-Parents</th>\n";
    echo "<th width='20%'>Great-Great<br>Grand-Parents</th>\n";
    echo "</tr>\n<tr>\n";

    printTree($id, $name, 0, 4);

    echo "<td colspan='5'>Produced by Barand</td></tr>\n</TABLE>\n";

}

?>
<HTML>
<HEAD>
<META HTTP-EQUIV="Pragma" CONTENT="no-cache">
<META HTTP-EQUIV="Cache-Control" CONTENT="no-cache">
<meta http-equiv="content-language" content="en">
<meta Name="generator" content="PHPEd Version 3.1.2 (Build 3165)">
<title>Pedigree Chart</title>
<META Name="keywords" CONTENT="Pedigree, chart">
<meta Name="author" content="B A Andrew">
<META Name="Creation_Date" CONTENT="11/09/2004">
</HEAD>
<BODY>

<?php

     pedigree(1,'dog A');

?>
</BODY>
</HTML>

forTheDogs · August 12, 2007

Thanks to both of you for your input!!!

I am having a problem with fitting suggestions into the files that were generated by the database program I used [i suppose I should not call it a db program as I did the db myself, it just does the pages on the web site that allows viewing and access - another words - the really hard parts!!!]. So, I have gone through and done my best to understand everyone's suggestions so that what I have works with what I have. I came up with the following but it has one huge problem - it does not know what to do when it reaches a dog without a sire ID # and it's probably about 45,000 times longer than it needs to be.

Here is enough of it so you can see what I am doing. I ended up with a whole big bunch of arrays - each one taking the sire or dam from the previous one and gathering its name, ID's etc.

Is there some way I could make one big array that includes all the little ones I have and then include a 'while' statement that if there is not a SireID [or DamID], it just puts 'unknown' in the name of that ancestor and then goes on to the next one? Almost all my arrays are eventually going to hit a NULL value and I just want them to continue on to the next one.

By the way, this is all included in an 'event' file that runs before the pedigree page is viewed.

////////

global $conn;
$message = "<table><tr><td>Pedigree Table</td></tr>";
$sql = "SELECT ID,RegName, SireID, DamID FROM Pedigrees WHERE ID=";

$DogQ = $sql . $values["ID"];
$DogR = db_query($DogQ,$conn);

$row = mysql_fetch_array ($DogR, MYSQL_ASSOC);
    $row = array (
'RegName' => $row['RegName'],           
'SireID' => $row['SireID'],           
'DamID' => $row['DamID']);	

//Get Sire
$SireQ = $sql . $row['SireID'];    
$SireR = mysql_query ($SireQ);
           
$SireRow = mysql_fetch_array ($SireR, MYSQL_ASSOC);         
	$SireRow = array (
	'RegName' => $SireRow['RegName'],           
	'SireID' => $SireRow['SireID'],           
	'DamID' => $SireRow['DamID']);	

//Get PatGrandSire info
	$PatGrandSireQ = $sql . $SireRow['SireID'];
	$PatGrandSireR = mysql_query ($PatGrandSireQ);

	$PatGrandSireRow = mysql_fetch_array ($PatGrandSireR, MYSQL_ASSOC);         
		$PatGrandSireRow = array (
		'RegName' => $PatGrandSireRow['RegName'],           
		'SireID' => $PatGrandSireRow['SireID'],           
		'DamID' => $PatGrandSireRow['DamID']);	

//Get Pat1GGrandSire info
		$Pat1GGrandSireQ = $sql . $PatGrandSireRow['SireID'];
		$Pat1GGrandSireR = mysql_query ($Pat1GGrandSireQ);

            $Pat1GGrandSireRow = mysql_fetch_array ($Pat1GGrandSireR, MYSQL_ASSOC);         
			$Pat1GGrandSireRow = array (
			'RegName' => $Pat1GGrandSireRow['RegName'],           
			'SireID' => $Pat1GGrandSireRow['SireID'],           
			'DamID' => $Pat1GGrandSireRow['DamID']);

And to print:

$message.="<tr style=color: rgb(102, 153, 0); background-color: rgb(255, 255, 204); align=left><td class=shade valign=middle rowspan=16 colspan=1 width=150><small><small><small>
Sire<br /><b>".$SireRow['RegName']."</b></small></small></small></td>
		<td class=shade valign=middle rowspan=8 colspan=1 width=170><small><small><small>
PatGrandsire<br /><b>".$PatGrandSireRow['RegName']."</b><br>
		</small></small></small></td>
		<td class=shade valign=middle rowspan=4 colspan=1 width=180><small><small><small>
Pat1GGrandSire<br /><b>".$Pat1GGrandSireRow['RegName']."</b><br>
		</small></small></small></td>
		<td class=shade valign=middle rowspan=2 colspan=1 width=190><small><small><small>
Pat1GGGrandSire<br /><b>".$Pat1GGGrandSireRow['RegName']."</b>
		</small></small></small></td>
		<td class=shade valign=middle><small><small><small>
Pat1GGGGrandSire<br>
		</small></small></small></td>
	</tr>";
$message.="<tr style=color: rgb(102, 153, 0); background-color: rgb(255, 255, 204); align=left><td class=shade valign=middle width=221><small><small><small>
Pat1GGGGrandDam<br>
		</small></small></small></td>
	</tr>";

Etc, etc, etc.

Thank you so much for looking at this!!! I hope one can understand what I am trying to say!!

gurroa · August 13, 2007

I think all you need to add is few if statements

//Get Sire
  if (is_numeric($row['SireID'])) {
   $SireQ = $sql . $row['SireID']; 
   ....  
//Get PatGrandSire info
    if (is_numeric($SireRow['SireID'])) {
      $PatGrandSireQ = $sql . $SireRow['SireID'];
      ....
   //Get Pat1GGrandSire info
      if (is_numeric($PatGrandSireRow['SireID'])) {
         $Pat1GGrandSireQ = $sql . $PatGrandSireRow['SireID'];
         ....

      }//PatGrandSireRow[sireID]

    }//SireRow[sireID]

  }//row[sireID]

///////////

// in outputting data you just check wheter is your array set
<small>
Sire
".(isset($SireRow) ? $SireRow['RegName'] : '')."</small>

forTheDogs · August 13, 2007

WOW Thank you so much!!!! I got an error the first time I tried it but then I added an

else{

'RegName' = 'Unknown'

}

So now if the Sire is available, it works. But if he is not, then I get a message that says undeclared or undefined variable or something like that.

Any suggestions? I thought I was getting better at figuring out what was wrong, but I have a very tenous hold on my understanding of how this is working and that error does not seem to be what my mind is focused on right now ??? ???

Also, I don't understand the last part of the code you gave me the ISSET . . .

Is that just to check the arrays or is it important in some other way?

Take a look though at how close I am:

fortheloveoflabradors.com/labradors/login.php - log in as "guest".

Thank you somuch for your help!!!!

Barand · August 13, 2007

Tried the link - just got a parse error

gurroa · August 13, 2007

This construct

echo (isset($SireRow) ? $SireRow['RegName'] : '');

is the same as

  if (isset($SireRow)) echo $SireRow['RegName'];
  else echo '';

php function isset() returns true/false if the variable is set or not.

see http://www.php.net/manual/en/function.isset.php

forTheDogs · August 14, 2007

Hi!! It should work. You probably just got there at the wrong time [i have been trying to get the table right and keep making little mistakes].

http://fortheloveoflabradors.com/labradors/menu.php

I lost my cell formatting so it is not easy to ready but it WORKS!!! As long as there is a sire, that is!!

Still have not figured out the "undefined variable" message I am getting when there is no sire!!

Thanks!!!

Barand · August 14, 2007

OK, that works. I clicked the "view" link for a couple of dogs - one ok, one gave an error message.

Incidentaly, that pedigree chart is just like the one my code (above) creates.

I did a search for one of my labradors (registered name "Loopy Lupus") but no success

forTheDogs · August 14, 2007

This is what I ended up with so that it works now even if the Sire or Dam ID are blank:

///////

if (isset($PatGrandSireRow['SireID']))

$message.="<td valign=middle class=shade rowspan=8 colspan=1 width=170>

PatGrandsire ".$PatGrandSireRow['RegName']."</td>";

else $message.="<td valign=middle class=shade rowspan=8 colspan=1 width=170>Unknown</td>";

/////

Thank you so much for all your help!!

I am so sorry you could not find your Lab in our database! What are his parents names? I don't recognize a kennel name in his registered name.

You are my heroes!!!!!!!!!!!

Barand · August 14, 2007

Prob because my two are UK registered.

Sign In

[SOLVED] Why doesn't this pedigree work?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information