I am confused now - the result looks OK but I am not sure !
My new code to generate the ancestor table:
<?php
error_reporting(E_ALL);
// Connect to database
$host= 'localhost';
$username = 'root';
$password = '';
$database = 'dogs';
$conn = @mysql_connect( "localhost", "$username", "$password" );
if (!$conn) die ("Could not connect MySQL");
@mysql_select_db( $database,$conn ) or die ( "Could not open database" );
// Empty Ancestor table
mysql_query('TRUNCATE TABLE ancestor');
$sql = "SELECT ID_number FROM pedigree";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)){
$generation = 0;
$ids = array($row['ID_number']); // id of the the dog to start with
$ids_start = $ids[0];
$results = query_ancestors($ids,$generation,$ids_start);
}
/* =======================================
* Recursive routine called once for each generation
*/
function query_ancestors($ids,$generation,$ids_start)// ********* ADDED $ids_start ********
{
// Make sure we have some doggies to look up
$results = array();
if (count($ids) < 1) return $results; // No more ancestors
// For storing parent ids
$parentIds = array();
// Query all of the current generation
$idsx = implode(',',$ids); // Makes comma delimited string
$sql = "SELECT * FROM pedigree WHERE ID_number IN($idsx)";
$rows = mysql_query($sql);
while ($row = mysql_fetch_assoc($rows))
{
// Add to results
$row['generation'] = $generation;
$results[] = $row;
$number = $row['ID_number']; // ********* ADDED LINE ********
$generation_number = $row['generation']; // ********* ADDED LINE ********
$sql = "INSERT INTO ancestor ( puppy_id, ancestor_id, generation ) VALUES ( $ids_start, $number, $generation_number )" ; // ********* ADDED LINE ********
mysql_query($sql); // ********* ADDED LINE ********
// Find the parents
if ($row['Sire']) $parentIds[] = $row['Sire'];
if ($row['Dam']) $parentIds[] = $row['Dam'];
}
// And here is the trick, repeat for all the parent dogs
$generation++;
$resultsx = query_ancestors($parentIds,$generation,$ids_start); // ********* ADDED $ids_start ********
return array_merge($results,$resultsx);
}
?>
The contents of the ancestor table:
CREATE TABLE IF NOT EXISTS `ancestor` (
`ID_number` smallint(3) NOT NULL AUTO_INCREMENT,
`puppy_id` smallint(3) NOT NULL,
`ancestor_id` smallint(3) NOT NULL,
`generation` smallint(3) NOT NULL,
PRIMARY KEY (`ID_number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=57 ;
INSERT INTO `ancestor` (`ID_number`, `puppy_id`, `ancestor_id`, `generation`) VALUES
(1, 1, 1, 0),
(2, 1, 2, 1),
(3, 1, 3, 1),
(4, 1, 4, 2),
(5, 1, 5, 2),
(6, 1, 6, 2),
(7, 1, 7, 2),
(8, 1, 8, 3),
(9, 1, 9, 3),
(10, 1, 10, 3),
(11, 1, 11, 3),
(12, 1, 12, 3),
(13, 1, 13, 3),
(14, 1, 14, 3),
(15, 1, 15, 3),
(16, 2, 2, 0),
(17, 2, 4, 1),
(18, 2, 5, 1),
(19, 2, 8, 2),
(20, 2, 9, 2),
(21, 2, 10, 2),
(22, 2, 11, 2),
(23, 3, 3, 0),
(24, 3, 6, 1),
(25, 3, 7, 1),
(26, 3, 12, 2),
(27, 3, 13, 2),
(28, 3, 14, 2),
(29, 3, 15, 2),
(30, 4, 4, 0),
(31, 4, 8, 1),
(32, 4, 9, 1),
(33, 5, 5, 0),
(34, 5, 10, 1),
(35, 5, 11, 1),
(36, 6, 6, 0),
(37, 6, 12, 1),
(38, 6, 13, 1),
(39, 7, 7, 0),
(40, 7, 14, 1),
(41, 7, 15, 1),
(42, 8, 8, 0),
(43, 9, 9, 0),
(44, 10, 10, 0),
(45, 11, 11, 0),
(46, 12, 12, 0),
(47, 13, 13, 0),
(48, 14, 14, 0),
(49, 15, 15, 0),
(50, 16, 16, 0),
(51, 16, 4, 1),
(52, 16, 5, 1),
(53, 16, 8, 2),
(54, 16, 9, 2),
(55, 16, 10, 2),
(56, 16, 11, 2);
If I pick a dog at random from the original pedigree table ( not neceserly a puppy ) and this will teach me to start with more realistic data so as not to get confused:
ID----KC_name-----KC_number----Name------DOB------Sex--Sire--Dam
6---Grandparent 23------23------Level 2--2010-10-28---D----12----13
Contents of the ancestor table for ancestor
ID_number----puppy_id----ancestor_id------generation
--36-------------6-------------6---------------0
--37-------------6-------------12--------------1
--38-------------6-------------13--------------1
Which does tie up - in both tables the parents for the dog are 12 and 13 and they are generation 1. The generation 0 shows up for the dog that is being researched ? For a dog with no parents there is only one row of genertion = 0
All I need to do now is tie it all together !
P.S. I have added an extra puppy ID number 16 when testing the prevoiuse code.
INSERT INTO `pedigree` (`ID_number`, `KC_name`, `KC_number`, `Name`, `DOB`, `Sex`, `Sire`, `Dam`) VALUES
(1, 'Willow as in tree', 1, 'Puppie', '2005-10-17', 'B', 2, 3),
(2, 'Puppies father', 11, 'Level 1', '2010-10-21', 'D', 4, 5),
(3, 'Puppies mother', 12, 'Level 1', '2010-10-01', 'B', 6, 7),
(4, 'Grandparent 21', 21, 'Level 2', '2010-10-29', 'D', 8, 9),
(5, 'Grandparent 22', 22, 'Level 2', '2010-10-12', 'B', 10, 11),
(6, 'Grandparent 23', 23, 'Level 2', '2010-10-28', 'D', 12, 13),
(7, 'Grandparent 24', 24, 'Level 2', '2010-10-29', 'B', 14, 15),
(8, 'GT Grandparent 31', 31, 'Level 3', '2010-10-22', 'D', NULL, NULL),
(9, 'GT Grandparent 32', 32, 'Level 3', '2010-10-12', 'B', NULL, NULL),
(10, 'GT Grandparent 33', 33, 'Level 3', '2010-10-23', 'D', NULL, NULL),
(11, 'GT Grandparent 34', 34, 'Level 3', '2010-10-04', 'B', NULL, NULL),
(12, 'GT Grandparent 35', 35, 'Level 3', '2010-10-27', 'D', NULL, NULL),
(13, 'GT Grandparent 36', 36, 'Level 3', '2010-10-27', 'B', NULL, NULL),
(14, 'GT Grandparent 37', 37, 'Level 3', '2010-10-02', 'D', NULL, NULL),
(15, 'GT Grandparent 38', 38, 'Level 3', '2010-10-20', 'B', NULL, NULL),
(16, 'Another generation', 1000, 'Willows uncle', '2010-10-08', 'D', 4, 5);
As you can see the new puppy 16 is an uncle and shares some ancestors of the original puppy.