Better way to write this multiple mysql query

Rubble · October 17, 2010, 2:53pm

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

// 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'); 
}

?>

ahundiak · October 24, 2010, 4:06pm

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.

empty ancestor table
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.

Rubble · October 21, 2010, 6:36pm

You have the answer ahundiak:


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.

Rubble · October 21, 2010, 6:30pm

As you say all the records are found but not displayed.


$sql  = "SELECT * FROM pedigree WHERE ID_number IN($idsx)";
echo $sql . "<br>\
";

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
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>\
";
}

/* =======================================
 * 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:


CREATE TABLE IF NOT EXISTS `pedigree` (
  `ID_number` smallint(3) NOT NULL AUTO_INCREMENT COMMENT 'Dog referance number',
  `KC_name` varchar(30) 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=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', 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);

ahundiak · October 21, 2010, 6:05pm

Strange. It’s clearly finding all the ids.
echo $sql . "
";
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?

ahundiak · October 21, 2010, 6:33pm

I don’t think so. query_ancestors returns an array. And then return $results + $resultsx just cats them together.
suppose he could try:


return array_merge($results,$resultsx);

I thought + would do the same thing but maybe not.

Rubble · October 21, 2010, 5:37pm

No errors displayed when error reporting turned on.

The spelling of Sire is correct to the table.

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

echo "$idsx";

1 - 15

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 ?

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

ahundiak · October 21, 2010, 2:14pm

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.


/* =====================================
 * 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']} \
";
}

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

ahundiak · October 21, 2010, 7:14pm

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.

Rubble · October 18, 2010, 4:17pm

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


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=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', 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);

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.

DarthGuido · October 17, 2010, 7:17pm

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?

Rubble · October 21, 2010, 9:02pm

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.

Rubble · October 20, 2010, 5:38pm

Bit of a bodge but I have the output I wanted; to go back one more generation could get really complicated !

Rubble · October 23, 2010, 7:58pm

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:


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

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']} &lt;br&gt;\
";
}

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

ahundiak · October 20, 2010, 9:33pm

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:

Querys the immediate ancestors of the current records and stores the results.
Call itself with the immediate ancestors.
Stop when no more ancestors are found.

Rubble · October 21, 2010, 3:48pm

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

DaveMaxwell · October 21, 2010, 6:25pm

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

Rubble · October 21, 2010, 5:50pm

I moved the position of the horizontal rule and get this:


    // 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
(
)

SpacePhoenix · October 18, 2010, 6:43am

Rubble, do you have any sample data?

DarthGuido · October 18, 2010, 6:37am

Like with the nested set model, I don’t see how you can take care of point 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.

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?