SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 31
  1. #1
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,435
    Mentioned
    82 Post(s)
    Tagged
    3 Thread(s)

    Better way to write this multiple mysql query

    I am looking to create a pedigree chart for dogs; I could hard code it but the b____ could have say 7 puppies which means I need to create 7 pages.
    If I put the data into a database and create the chart dynamicaly its a lot neater and easier to update.
    But I can not think of a good way to do the queries; below is what I have so far and on 2 generations its 5 queries but going up one more generation it becomes 13 queries and so on. I could continue this way and it is not to much work but is there a better way ?
    Also using a function reduces the amount of code but as well as echoing the result I need to return the Sire & Dam values for the next queries.
    PHP Code:
    <?php

    // Display function NEED TO RETURN THE $row[Sire] AND $row[Dam] FOR THE NEXT LOOP
    function display$ref$div )
    {
    $result MYSQL_QUERY("SELECT * FROM pedigree WHERE ID_number = '$ref' ") or die ( mysql_error() );
    $row mysql_fetch_array$result );

    echo 
    "<div class=\"$div\">Name: "$row[Name] ."<br/>DOB: "$row[DOB] ."<br/>Sex: "$row[Sex] ."<br/>KC name: "$row[KC_name] ."<br/>KC number: "$row[KC_number]."<br/>Sire: "$row[Sire]."<br/>Dam: "$row[Dam]."</div>";
    }

    // Input the dogs ID from the URL - HARD CODED FOR NOW
    // $ref = $_POST['ID'];
    $ref '1';

    // Select details of dog from database
    $result MYSQL_QUERY("SELECT * FROM pedigree WHERE ID_number = '$ref' ") or die ( mysql_error() );
    $row mysql_fetch_array$result );

    echo 
    "<div class=\"first\">Name: "$row[Name] ."<br/>DOB: "$row[DOB] ."<br/>Sex: "$row[Sex] ."<br/>KC name: "$row[KC_name] ."<br/>KC number: "$row[KC_number]."<br/>Sire: "$row[Sire]."<br/>Dam: "$row[Dam]."</div>";

    // Setup the variables for this dogs parents
    // Need this or else the details are taken from whichever record is used next!
    $sire_1 $row[Sire];
    $dam_1 $row[Dam];

    // Select all dogs relatives from the database

    // Select details of the dog from database
    if( !empty($sire_1) ){
    $ref $sire_1;
    display$ref'V'); 
    }

    // Select details of the ***** from database
    if( !empty($dam_1) ){
    $ref $dam_1;
    display$ref'V'); 
    }

    ?>
    Last edited by Rubble; Oct 17, 2010 at 08:55. Reason: Would not let me say the female dogs name?

  2. #2
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    698
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    It's not really clear to me exactly what you are trying to accomplish. I would suggest posting your database schema along with a clear outline of what you need to query over on the mysql forum. There is a database expert over there who really enjoys this sort of stuff.

    Once you have the queries then the php portion will be easy.

  3. #3
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Essentially you will need to store a multi-level hierarchy in a database.

    My recommendation is to use The Nested Set Model to do that.

  4. #4
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,435
    Mentioned
    82 Post(s)
    Tagged
    3 Thread(s)
    The last question I posted on the Mysql forum didn't get one answer and I think the one before that was not well answered so I didn't bother.

    Thanks for the link Kalon that is similar to what I am trying to do but I am not sure that method will work. I will have a better read through later.
    Looking at the first paragraph the way to go may be XML rather than a database?

    I will have the dog of interest at the bottom on the first row, on the next row up I will have the dogs parents, the next row will have the parents of those dogs.

    S_D___S D___S__D__ S__D
    _S_____D_____ S______D
    ____S____________D
    __________Dog

    S = father D = mother

    The user will pick a dog and go to the page to view the pedigree; when that page loads the ancestor details will be loaded if known and it will be arranged as in the diagram above as far back as the data is available.
    Last edited by Rubble; Oct 17, 2010 at 12:59. Reason: Chart collapsed

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Rubble View Post
    The last question I posted on the Mysql forum didn't get one answer and I think the one before that was not well answered so I didn't bother.
    Sorry about that. The MySQL forum, like the PHP one, is a very lively forum, and usually most (if not all) questions are answered very quickly. But like in all SP forums, the people that answer the questions do so because they like to help others, and sometimes they have other things to do.
    I checked your posts, and found a non answered question in the MySQL forum on August, 29th. I went back as far as February, but I didn't see another one. I hope you resolved the problem in this August post? Or do you still need help with it?

  6. #6
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,435
    Mentioned
    82 Post(s)
    Tagged
    3 Thread(s)
    Thanks for the message guido2004 and looking through my post you must have seen I try to help others.
    Anyway concerning the August post I did what I didn't want to do in the end and had one big table. Its not to bad as the rows will only have 10 columns containing a small integer and I am only creating the row if it has any data. The maximium amount of rows will only be 88.

    I am not sure if XML is the way to go after all as it gets complicted if I need to add a new generation.
    The same goes for using levels in the database - add a new generation and I need to increment some levels and not others messy.

    If I keep the information about the dogs in one table and relationships in another it looks better.

  7. #7
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,435
    Mentioned
    82 Post(s)
    Tagged
    3 Thread(s)
    Forgot to post the current database table:
    PHP Code:
    CREATE TABLE IF NOT EXISTS `pedigree` (
      `
    ID_numbersmallint(3NOT NULL AUTO_INCREMENT COMMENT 'Dog referance number',
      `
    KC_namevarchar(100NOT NULL DEFAULT 'Unknown',
      `
    KC_numbermediumint(5NOT NULL DEFAULT '1000',
      `
    Namevarchar(20) DEFAULT NULL,
      `
    DOBdate DEFAULT NULL,
      `
    Sexenum('D','B'NOT NULL DEFAULT 'D',
      `
    Siresmallint(3) DEFAULT NULL,
      `
    Damsmallint(3) DEFAULT NULL,
      
    PRIMARY KEY (`ID_number`),
      
    UNIQUE KEY `KC_name` (`KC_name`,`KC_number`)
    ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=

  8. #8
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,435
    Mentioned
    82 Post(s)
    Tagged
    3 Thread(s)
    The current output:

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Kalon View Post
    Essentially you will need to store a multi-level hierarchy in a database.

    My recommendation is to use The Nested Set Model to do that.
    Things are a bit more complicated here though:
    1) First of all, we're talking inverted trees here (not starting from a single root with 0-n children, but starting from a single puppie with 2 parents, ecc.). Only if the OP would like to show all the descentants from a dog, the nested set model as described in that article would work.
    2) Second, there are n of these inverted trees (we're not talking 1 puppy here, but an indefinit number)
    3) The inverted trees are not separate, but can have relationships between them, as the dogs can have multiple puppies, and also most probably won't have a monogamous relationship. So the 'left' and 'right' boundaries of the nested set model could be different for the same dog in relation to different puppies.

    I've tried googling for 'family tree database model' and 'pedigree database model', but sofar haven't found anything useful.
    Of course, maybe it's easy adapting the nested set model to this situation, but right now I don't have time to dive into it.

  10. #10
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Rubble, you may find this SitePoint Article (http://articles.sitepoint.com/articl...-data-database) a good read. I personally prefer using
    Modified Preorder Tree Traversal for scalability and less queries needed. For either you might want to consider the use of transactions to maintain the integrity of the data.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    Rubble, you may find this SitePoint Article (http://articles.sitepoint.com/articl...-data-database) a good read. I personally prefer using
    Modified Preorder Tree Traversal for scalability and less queries needed. For either you might want to consider the use of transactions to maintain the integrity of the data.
    Like with the nested set model, I don't see how you can take care of point number 3?
    3) The inverted trees are not separate, but can have relationships between them, as the dogs can have multiple puppies, and also most probably won't have a monogamous relationship. So the 'left' and 'right' boundaries of the nested set model could be different for the same dog in relation to different puppies.
    Without having to duplicate each ancestor for each single puppie that is?

    And the other 2 points are valid here as well. Aren't they?

  12. #12
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Rubble, do you have any sample data?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  13. #13
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,435
    Mentioned
    82 Post(s)
    Tagged
    3 Thread(s)
    Here is some data and I have modified it so the output should give you an indication if the results are in the correct place.

    24 will not have any parents.

    31 32 33 34 35 36 37 38
    21 22 23 24
    11 12
    1

    PHP Code:
    CREATE TABLE IF NOT EXISTS `pedigree` (
      `
    ID_numbersmallint(3NOT NULL AUTO_INCREMENT COMMENT 'Dog referance number',
      `
    KC_namevarchar(100NOT NULL DEFAULT 'Unknown',
      `
    KC_numbermediumint(5NOT NULL DEFAULT '1000',
      `
    Namevarchar(20) DEFAULT NULL,
      `
    DOBdate DEFAULT NULL,
      `
    Sexenum('D','B'NOT NULL DEFAULT 'D',
      `
    Siresmallint(3) DEFAULT NULL,
      `
    Damsmallint(3) DEFAULT NULL,
      
    PRIMARY KEY (`ID_number`),
      
    UNIQUE KEY `KC_name` (`KC_name`,`KC_number`)
    ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;


    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); 
    The user will input the puppies name and is interested in the ancestors; puppies from the same litter will have the same ancestors.

    If there are ancestor details display them and if not stop that part of the tree.

    I want to do it this way so that is a puppie is used for breading later it can be added to the tree and all the levels will move up one. This will be automatic without having to change all the level numbers as this will effect other puppies trees.
    Last edited by Rubble; Oct 18, 2010 at 10:21. Reason: Added last text

  14. #14
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,435
    Mentioned
    82 Post(s)
    Tagged
    3 Thread(s)
    Bit of a bodge but I have the output I wanted; to go back one more generation could get really complicated !


  15. #15
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    698
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Are you doing one query per generation?

    It really should not be all that difficult to write a little recursive function which would query all the way up the chain as it were. At which point the number of generations would become irrelevant.

    Basically, write a function which:
    1. Querys the immediate ancestors of the current records and stores the results.
    2. Call itself with the immediate ancestors.
    3. Stop when no more ancestors are found.

  16. #16
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,435
    Mentioned
    82 Post(s)
    Tagged
    3 Thread(s)
    Basicly I am running 15 different mysql queries and if I went another generation I would need another 16

    The way I wanted to go is how you suggest ahundiak but it is beyond me and that is why I posted originaly.

  17. #17
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    698
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    This is completely untested but should help get you started in the wonderful world of recursion. Might want to test by running from a standalone script so you don't need to worry about any browser stuff.
    PHP Code:
    /* =====================================
     * This is what the final interface should do
     */
    $generation 0
    $ids = array(1); // id of the the dog to start with
    $results query_ancestors($ids,$generation);
    foreach(
    $results as $result)
    {
      echo 
    "{$result['generation']} {$result['ID_number']} {$result['KC_name']} \n";
    }

    /* =======================================
     * Recursive routine called once for each generation
     */
    function query_ancestors($ids,$generation)
    {
      
    // 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;

        
    // 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);
      return 
    $results $resultsx;


  18. #18
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,435
    Mentioned
    82 Post(s)
    Tagged
    3 Thread(s)
    Thank you very much for the code ahundiak; it ran first time and I will study it later - feeling a bit under the weather at the moment.

    One small problem is that I only get 8 results and not 15. It follows the puppies mothers side but not the puppies fathers side.

    0 1 Willow as in tree
    1 3 Puppies mother
    2 6 Grandparent 23
    2 7 Grandparent 24
    3 12 GT Grandparent 35
    3 13 GT Grandparent 36
    3 14 GT Grandparent 37
    3 15 GT Grandparent 38

  19. #19
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    698
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Add:
    error_reporting(E_ALL);
    To the top of the script.

    Make sure the spelling of 'Sire' matches your tale column name.

    And maybe add print_r($ids) to the top of the function.

  20. #20
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,435
    Mentioned
    82 Post(s)
    Tagged
    3 Thread(s)
    No errors displayed when error reporting turned on.

    The spelling of Sire is correct to the table.
    PHP Code:
    echo "<pre>";print_r($ids); echo "</pre>"
    Array
    (
    [0] => 1
    )

    Array
    (
    [0] => 2
    [1] => 3
    )

    Array
    (
    [0] => 4
    [1] => 5
    [2] => 6
    [3] => 7
    )

    Array
    (
    [0] => 8
    [1] => 9
    [2] => 10
    [3] => 11
    [4] => 12
    [5] => 13
    [6] => 14
    [7] => 15
    )

    Array
    (
    )
    PHP Code:
    echo "$idsx"
    1 - 15
    PHP Code:
    echo "<pre>";print_r($results); echo "</pre>"
    Prints similar to this for all 15 results
    [7] => Array
    (
    [ID_number] => 15
    [KC_name] => GT Grandparent 38
    [KC_number] => 38
    [Name] => Level 3
    [DOB] => 2010-10-20
    [Sex] => B
    [Sire] =>
    [Dam] =>
    [generation] => 3
    )
    The problem must be here somewhere ?

    PHP Code:
        if ($row['Sire']) $parentIds[] = $row['Sire'];
        echo 
    "<pre>";print_r($parentIds); echo "</pre><hr>";
        if (
    $row['Dam'])  $parentIds[] = $row['Dam'];
        echo 
    "<pre>";print_r($parentIds); echo "</pre>"
    Array
    (
    [0] => 2
    )


    --------------------------------------------------------------------------------

    Array
    (
    [0] => 2
    [1] => 3
    )

    Array
    (
    [0] => 4
    )


    --------------------------------------------------------------------------------

    Array
    (
    [0] => 4
    [1] => 5
    )

    Array
    (
    [0] => 4
    [1] => 5
    [2] => 6
    )


    --------------------------------------------------------------------------------

    Array
    (
    [0] => 4
    [1] => 5
    [2] => 6
    [3] => 7
    )

    Array
    (
    [0] => 8
    )


    --------------------------------------------------------------------------------

    Array
    (
    [0] => 8
    [1] => 9
    )

    Array
    (
    [0] => 8
    [1] => 9
    [2] => 10
    )


    --------------------------------------------------------------------------------

    Array
    (
    [0] => 8
    [1] => 9
    [2] => 10
    [3] => 11
    )

    Array
    (
    [0] => 8
    [1] => 9
    [2] => 10
    [3] => 11
    [4] => 12
    )


    --------------------------------------------------------------------------------

    Array
    (
    [0] => 8
    [1] => 9
    [2] => 10
    [3] => 11
    [4] => 12
    [5] => 13
    )

    Array
    (
    [0] => 8
    [1] => 9
    [2] => 10
    [3] => 11
    [4] => 12
    [5] => 13
    [6] => 14
    )


    --------------------------------------------------------------------------------

    Array
    (
    [0] => 8
    [1] => 9
    [2] => 10
    [3] => 11
    [4] => 12
    [5] => 13
    [6] => 14
    [7] => 15
    )

    Array
    (
    )


    --------------------------------------------------------------------------------

    Array
    (
    )

    Array
    (
    )


    --------------------------------------------------------------------------------

    Array
    (
    )

    Array
    (
    )


    --------------------------------------------------------------------------------

    Array
    (
    )

    Array
    (
    )


    --------------------------------------------------------------------------------

    Array
    (
    )

    Array
    (
    )


    --------------------------------------------------------------------------------

    Array
    (
    )

    Array
    (
    )


    --------------------------------------------------------------------------------

    Array
    (
    )

    Array
    (
    )


    --------------------------------------------------------------------------------

    Array
    (
    )

    Array
    (
    )


    --------------------------------------------------------------------------------

    Array
    (
    )

  21. #21
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,435
    Mentioned
    82 Post(s)
    Tagged
    3 Thread(s)
    I moved the position of the horizontal rule and get this:

    PHP Code:
        // Find the parents
        
    if ($row['Sire']) $parentIds[] = $row['Sire'];
        echo 
    "<pre>";print_r($parentIds); echo "</pre>";
        if (
    $row['Dam'])  $parentIds[] = $row['Dam'];
        echo 
    "<pre>";print_r($parentIds); echo "</pre><hr>"
    Array
    (
    [0] => 2
    )

    Array
    (
    [0] => 2
    [1] => 3
    )


    --------------------------------------------------------------------------------

    Array
    (
    [0] => 4
    )

    Array
    (
    [0] => 4
    [1] => 5
    )


    --------------------------------------------------------------------------------

    Array
    (
    [0] => 4
    [1] => 5
    [2] => 6
    )

    Array
    (
    [0] => 4
    [1] => 5
    [2] => 6
    [3] => 7
    )


    --------------------------------------------------------------------------------

    Array
    (
    [0] => 8
    )

    Array
    (
    [0] => 8
    [1] => 9
    )


    --------------------------------------------------------------------------------

    Array
    (
    [0] => 8
    [1] => 9
    [2] => 10
    )

    Array
    (
    [0] => 8
    [1] => 9
    [2] => 10
    [3] => 11
    )


    --------------------------------------------------------------------------------

    Array
    (
    [0] => 8
    [1] => 9
    [2] => 10
    [3] => 11
    [4] => 12
    )

    Array
    (
    [0] => 8
    [1] => 9
    [2] => 10
    [3] => 11
    [4] => 12
    [5] => 13
    )


    --------------------------------------------------------------------------------

    Array
    (
    [0] => 8
    [1] => 9
    [2] => 10
    [3] => 11
    [4] => 12
    [5] => 13
    [6] => 14
    )

    Array
    (
    [0] => 8
    [1] => 9
    [2] => 10
    [3] => 11
    [4] => 12
    [5] => 13
    [6] => 14
    [7] => 15
    )


    --------------------------------------------------------------------------------

    Array
    (
    )

    Array
    (
    )


    --------------------------------------------------------------------------------

    Array
    (
    )

    Array
    (
    )


    --------------------------------------------------------------------------------

    Array
    (
    )

    Array
    (
    )


    --------------------------------------------------------------------------------

    Array
    (
    )

    Array
    (
    )


    --------------------------------------------------------------------------------

    Array
    (
    )

    Array
    (
    )


    --------------------------------------------------------------------------------

    Array
    (
    )

    Array
    (
    )


    --------------------------------------------------------------------------------

    Array
    (
    )

    Array
    (
    )


    --------------------------------------------------------------------------------

    Array
    (
    )

    Array
    (
    )


  22. #22
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    698
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Strange. It's clearly finding all the ids.
    echo $sql . "\n";
    Right after the $sql = statement.
    Verify all the ids are shoing up in the IN() clause
    Try running the query directly from mysql and make sure the records are getting picked up.

    And maybe post your code. An extra mysql_fetch_row somewhere in the loop could be causing every other record to be skipped?

  23. #23
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,289
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by ahundiak View Post
    Strange. It's clearly finding all the ids.
    echo $sql . "\n";
    Right after the $sql = statement.
    Verify all the ids are shoing up in the IN() clause
    Try running the query directly from mysql and make sure the records are getting picked up.

    And maybe post your code. An extra mysql_fetch_row somewhere in the loop could be causing every other record to be skipped?
    It's not the sql biting him, it's the results returned.

    Shouldn't this line:
    $resultsx = query_ancestors($parentIds,$generation);
    be
    $resultsx[] = query_ancestors($parentIds,$generation);

    And shouldn't there be a $resultx = array(); somewhere? It doesn't look like there's anything which delineates resultx as an array...

    (excuse me if I'm way off base - I've been doing C# lately, so my php is probably rusty again...)
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  24. #24
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,435
    Mentioned
    82 Post(s)
    Tagged
    3 Thread(s)
    As you say all the records are found but not displayed.

    PHP Code:
    $sql  "SELECT * FROM pedigree WHERE ID_number IN($idsx)";
    echo 
    $sql "<br>\n"
    SELECT * FROM pedigree WHERE ID_number IN(1)
    SELECT * FROM pedigree WHERE ID_number IN(2,3)
    SELECT * FROM pedigree WHERE ID_number IN(4,5,6,7)
    SELECT * FROM pedigree WHERE ID_number IN(8,9,10,11,12,13,14,15)
    Here is the code I am using:
    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" );

    /* =====================================
     * This is what the final interface should do
     */
    $generation 0
    $ids = array(1); // id of the the dog to start with
    $results query_ancestors($ids,$generation);
    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)
    {
      
    // 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;

        
    // 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);
      return 
    $results $resultsx;

    ?>
    The database table:
    PHP Code:
    CREATE TABLE IF NOT EXISTS `pedigree` (
      `
    ID_numbersmallint(3NOT NULL AUTO_INCREMENT COMMENT 'Dog referance number',
      `
    KC_namevarchar(30NOT NULL DEFAULT 'Unknown',
      `
    KC_numbermediumint(5NOT NULL DEFAULT '1000',
      `
    Namevarchar(20) DEFAULT NULL,
      `
    DOBdate DEFAULT NULL,
      `
    Sexenum('D','B'NOT NULL DEFAULT 'D',
      `
    Siresmallint(3) DEFAULT NULL,
      `
    Damsmallint(3) DEFAULT NULL,
      
    PRIMARY KEY (`ID_number`),
      
    UNIQUE KEY `KC_name` (`KC_name`,`KC_number`)
    ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

    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); 

  25. #25
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    698
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    It's not the sql biting him, it's the results returned.

    Shouldn't this line:
    $resultsx = query_ancestors($parentIds,$generation);
    be
    $resultsx[] = query_ancestors($parentIds,$generation);

    And shouldn't there be a $resultx = array(); somewhere? It doesn't look like there's anything which delineates resultx as an array...

    (excuse me if I'm way off base - I've been doing C# lately, so my php is probably rusty again...)
    I don't think so. query_ancestors returns an array. And then return $results + $resultsx just cats them together.
    suppose he could try:
    PHP Code:
    return array_merge($results,$resultsx); 
    I thought + would do the same thing but maybe not.


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
  •