Better way to write this multiple mysql query

PHP
21

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.

22

Rubble, you may find this SitePoint Article (http://articles.sitepoint.com/article/hierarchical-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.

23

The current output:

24

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.

25

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.

26

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.

27

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.

28

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.

29

Forgot to post the current database table:


CREATE TABLE IF NOT EXISTS `pedigree` (
  `ID_number` smallint(3) NOT NULL AUTO_INCREMENT COMMENT 'Dog referance number',
  `KC_name` varchar(100) NOT NULL DEFAULT 'Unknown',
  `KC_number` mediumint(5) NOT NULL DEFAULT '1000',
  `Name` varchar(20) DEFAULT NULL,
  `DOB` date DEFAULT NULL,
  `Sex` enum('D','B') NOT NULL DEFAULT 'D',
  `Sire` smallint(3) DEFAULT NULL,
  `Dam` smallint(3) DEFAULT NULL,
  PRIMARY KEY (`ID_number`),
  UNIQUE KEY `KC_name` (`KC_name`,`KC_number`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
30

Basicly I am running 15 different mysql queries and if I went another generation I would need another 16 :nono:

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

31

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.