Sorting data from a large database - need help!

[black-ops]

Ok so the database is incredibly large over 3000 entries, sadly due to the project I am doing I can't explain a great deal of it as it's far to complicated to explain in such a small post. Only issue I have come across so far is actually a rather simple one I once resolved before but cannot seem to do it with this project. There are various relationships assigned within the database (not a relational db) but hierarchal.

For instance a single row would look like this:

GGGmother->GGmother->Gmother->Mother->Child

That is fine I can sort that through an array, however in the database data is branched off depending upon the selection the user makes. Thus if they select GGmother there are more options under GGmother. Like so:

GGGmother->GGmother->Gmother->Mother->Child
GGGmother->GGmother->GmotherA->Mother->Child
GGGmother->GGmother->GmotherB->Mother->Child
GGGmother->GGmother->GmotherC->Mother->Child

This then breaks up into even more diversity thus:

GGGmother->GGmother->Gmother->Mother->Child
GGGmother->GGmother->GmotherA->Mother->Child
GGGmother->GGmother->GmotherA->MotherA->Child
GGGmother->GGmother->GmotherA->MotherB->Child

My question, is there any class out there already or any means by logic I can say, if the previous selection is equal to the current row then negate the previous parent.

Thus I will just get the child appearing of each selection.

Like this:

GGGmother->GGmotherA
                ->GGmotherB
                ->GGmotherC

GGGmother->GGmotherA->GmotherA
                                  ->GmotherB
                                  ->GmotherC

 

[Pho]

3000 rows is a tiny amount of data for a database.

From looking at your data it looks like storing it the way you do will just cause you more trouble than needed, you would be better using some sort of hierarchy within the database rather than storing it all as a string in a single column, i.e.:

tblPerson
ID, Name
1, PersonA
2, PersonB
3, PersonC

tblRelatonships
Person, Person_Relative, Relationship
1, 2, "Mother"
2, 3, "Grandmother"

(you could infer that person 3 is the grandmother of person 1 through this too)

What database server are you using?

 

[Dj_Jestar]

3000 rows is a tiny amount of data for a database.

From looking at your data it looks like storing it the way you do will just cause you more trouble than needed, you would be better using some sort of hierarchy within the database rather than storing it all as a string in a single column, i.e.:

tblPerson
ID, Name
1, PersonA
2, PersonB
3, PersonC

tblRelatonships
Person, Person_Relative, Relationship
1, 2, "Mother"
2, 3, "Grandmother"

(you could infer that person 3 is the grandmother of person 1 through this too)

What database server are you using?

In his post he has stated he is using a Hierarchical model, not a relational one, so that won't be much help.

OP: Something on here help? http://en.wikipedia.org/wiki/Hierarchical_query

 

[black-ops]

In his post he has stated he is using a Hierarchical model, not a relational one, so that won't be much help.

OP: Something on here help? http://en.wikipedia.org/wiki/Hierarchical_query

bingo, that sorted it within seconds. Thank you, I honestly had no clue these queries actually existed hah, thank you for the help

 

[Pho]

In his post he has stated he is using a Hierarchical model, not a relational one, so that won't be much help.

OP: Something on here help? http://en.wikipedia.org/wiki/Hierarchical_query

Serves me right for not reading it properly .

 

[sparkey196]

Ok so the database is incredibly large over 3000 entries

Aww how cute.

 

[Haircut]

Aww how cute.

I thought this was going to be a thread about how best to sort hundreds of millions of records when I saw the title.

To the OP, you're going to have a shock once you start using proper production databases if you think 3000 records is large!

 

[sparkey196]

Your application will be much more scalable and the data more manageable if you just store the relationships between people as Pho suggested. See PHP/MySQL code example below.

The only problem with this would be if for example your cousin became your step dad then they would get returned twice... in which case the duplicate would probably be the least of your concerns.

<?php
// table name = "family";
// id field = "id";
// my current id = 1
// parent id field = "parentId"
// levels down to search = 3
$allMyChildren = recursiveDescent("family", "id", 1, "parentId", 3);

echo "<pre>\n";
print_r($allMyChildren);
echo "</pre>";


function recursiveDescent($table, $idField, $myId, $parentField, $levels)
{
  $levels--;
  $sql = "SELECT * FROM $table WHERE $parentField = '$myId'";
  $result = mysql_query($sql) or die(mysql_error());
  $offspring = array();
  while ($row = mysql_fetch_assoc($result))
  {
    array_push($offspring, $row);
    if ($levels > 0)
    {
      $chillins = recursiveDescent($table, $idField, $row[$idField], $parentField, $levels);
      if (count($chillins) > 0)
      {
        // Use this line to get a 1-D array - everybody is at the same level
        array_splice($offspring, count($offspring), 0, $chillins);
        // Use this line instead to get an array of arrays - children are top level arrays, grandchildren are arrays grouped in an array, great-grandchildren are arrays grouped in arrays grouped in arrays, etc.
        //array_push($offspring, $chillins);
      }
    }
  }
  return $offspring;
}
?>

 

[Dj_Jestar]

He is using a Hierarchical data model, not a relational model.. how many times does it need spelling out?