SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 31 of 31
  1. #26
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,191
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    You have the answer ahundiak:
    PHP Code:
    return array_merge($results,$resultsx); 
    0 1 Willow as in tree
    1 2 Puppies father
    1 3 Puppies mother
    2 4 Grandparent 21
    2 5 Grandparent 22
    2 6 Grandparent 23
    2 7 Grandparent 24
    3 8 GT Grandparent 31
    3 9 GT Grandparent 32
    3 10 GT Grandparent 33
    3 11 GT Grandparent 34
    3 12 GT Grandparent 35
    3 13 GT Grandparent 36
    3 14 GT Grandparent 37
    3 15 GT Grandparent 38
    All I need to do now is format it

    Thanks again for your help.

  2. #27
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    663
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Yea.

    But while one query per generation is not bad how about we set it up so only one query is needed to get the entire tree? And at the same time how about we enable it to get all the descendants of a particular dog in one query?

    Really not that hard. Create a second table:
    ancestor
    ..puppy_id
    ..ancestor_id
    ..generation

    Each time a new puppy is added to pedigree, run the query_ancestor routine you just developed. For each ancestor, add a record to the ancestor table. That gives you a direct link between each puppy and each ancestor.

    Now when you need to find all the ancestors for a given dog just do one one query on ancestor for puppy_id and join with the pedigree table.

    And as a bonus you can query on ancestor_id and get a list of all descendants of a given dog.

    Little bit more work but a much cleaner system.

  3. #28
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,191
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    A quick question on the mods you are suggesting - if a puppy in the database is used for breading and the new puppies are added to the list. The original puppy will now be generation 1 not generation 0. Will this cause a problem with the extra table you are suggesting ?

    I have just reread your post again and it will be OK:
    Each time a new puppy is added to pedigree, run the query_ancestor routine you just developed.
    Last edited by Rubble; Oct 21, 2010 at 15:03. Reason: Added second paragraph

  4. #29
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,191
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    I have had a go at the first part of you improvement suggestion ahundiak and the code is below.

    1/ I had to pass the original puppies ID to the function - correct or not ?
    2/ I added a Mysql query to add the data to the database.
    3/ The database also includes a row for the puppy where it shouldn't ?

    Database contents:
    PHP Code:
    INSERT INTO `ancestor` (`ID_number`, `puppy_id`, `ancestor_id`, `generation`) VALUES
    (1110),
    (
    2121),
    (
    3131),
    (
    4142),
    (
    5152),
    (
    6162),
    (
    7172),
    (
    8183),
    (
    9193),
    (
    101103),
    (
    111113),
    (
    121123),
    (
    131133),
    (
    141143),
    (
    151153); 
    Code:
    PHP Code:
    /* =====================================
     * This is what the final interface should do
     */
    $generation 0
    $ids = array(1); // id of the the dog to start with
    $ids_start $ids[0]; // ********* ADDED LINE ********
    $results query_ancestors($ids,$generation,$ids_start);
    foreach(
    $results as $result)
    {
      echo 
    "{$result['generation']} {$result['ID_number']} {$result['KC_name']} <br>\n";
    }

    /* =======================================
     * 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); 


  5. #30
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    663
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Rubble View Post
    3/ The database also includes a row for the puppy where it shouldn't ?
    I would leave this in. Doesn't really hurt anything and might make some queries easier.

    Now you will need to write a routine to completely rebuild the ancestor table from scratch.
    1. empty ancestor table
    2. query pedigree for all ids
    3a. call query_ancestor for each id and
    3b. add the records to ancestor

    You will need something like this to keep the two tables in sync. You will find that errors will occur as you enhance your program and you will end up modifying pedigree without updating ancestor and things will go badly.

    You will also find that if a dog's pedigree is entered incorrectly and needs to be fixed later, then updating the ancestor table will be difficult especially if the dog has offspring before the error is discovered. Rebuilding will be the easiest way.

    So implement the sync routine up front and save some effort later.

  6. #31
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,191
    Mentioned
    71 Post(s)
    Tagged
    3 Thread(s)
    I am confused now - the result looks OK but I am not sure !

    My new code to generate the ancestor table:
    PHP Code:
    <?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:
    PHP Code:
    CREATE TABLE IF NOT EXISTS `ancestor` (
      `
    ID_numbersmallint(3NOT NULL AUTO_INCREMENT,
      `
    puppy_idsmallint(3NOT NULL,
      `
    ancestor_idsmallint(3NOT NULL,
      `
    generationsmallint(3NOT NULL,
      
    PRIMARY KEY (`ID_number`)
    ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=57 ;

    INSERT INTO `ancestor` (`ID_number`, `puppy_id`, `ancestor_id`, `generation`) VALUES
    (1110),
    (
    2121),
    (
    3131),
    (
    4142),
    (
    5152),
    (
    6162),
    (
    7172),
    (
    8183),
    (
    9193),
    (
    101103),
    (
    111113),
    (
    121123),
    (
    131133),
    (
    141143),
    (
    151153),
    (
    16220),
    (
    17241),
    (
    18251),
    (
    19282),
    (
    20292),
    (
    212102),
    (
    222112),
    (
    23330),
    (
    24361),
    (
    25371),
    (
    263122),
    (
    273132),
    (
    283142),
    (
    293152),
    (
    30440),
    (
    31481),
    (
    32491),
    (
    33550),
    (
    345101),
    (
    355111),
    (
    36660),
    (
    376121),
    (
    386131),
    (
    39770),
    (
    407141),
    (
    417151),
    (
    42880),
    (
    43990),
    (
    4410100),
    (
    4511110),
    (
    4612120),
    (
    4713130),
    (
    4814140),
    (
    4915150),
    (
    5016160),
    (
    511641),
    (
    521651),
    (
    531682),
    (
    541692),
    (
    5516102),
    (
    5616112); 
    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:
    PHP Code:
    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
    PHP Code:
    ID_number----puppy_id----ancestor_id------generation
    --36-------------6-------------6---------------
    --37-------------6-------------12--------------
    --38-------------6-------------13--------------
    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.
    PHP 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'23),
    (
    2'Puppies father'11'Level 1''2010-10-21''D'45),
    (
    3'Puppies mother'12'Level 1''2010-10-01''B'67),
    (
    4'Grandparent 21'21'Level 2''2010-10-29''D'89),
    (
    5'Grandparent 22'22'Level 2''2010-10-12''B'1011),
    (
    6'Grandparent 23'23'Level 2''2010-10-28''D'1213),
    (
    7'Grandparent 24'24'Level 2''2010-10-29''B'1415),
    (
    8'GT Grandparent 31'31'Level 3''2010-10-22''D'NULLNULL),
    (
    9'GT Grandparent 32'32'Level 3''2010-10-12''B'NULLNULL),
    (
    10'GT Grandparent 33'33'Level 3''2010-10-23''D'NULLNULL),
    (
    11'GT Grandparent 34'34'Level 3''2010-10-04''B'NULLNULL),
    (
    12'GT Grandparent 35'35'Level 3''2010-10-27''D'NULLNULL),
    (
    13'GT Grandparent 36'36'Level 3''2010-10-27''B'NULLNULL),
    (
    14'GT Grandparent 37'37'Level 3''2010-10-02''D'NULLNULL),
    (
    15'GT Grandparent 38'38'Level 3''2010-10-20''B'NULLNULL),
    (
    16'Another generation'1000'Willows uncle''2010-10-08''D'45); 
    As you can see the new puppy 16 is an uncle and shares some ancestors of the original puppy.
    Last edited by Rubble; Oct 26, 2010 at 14:04. Reason: Added P.S.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •