Is Multiselect from Mysql possible

[segerdalen]

In my database there are 16 columns but these 5 are now of interest
K! K2 K3 K4 K5
Familyno Familjyrank Name Grandfamilyno Grandfamilyrank
1883 0 Adam 46 12
1883 1 Eva 1447 11
1883 10 Lukas 0 0
1883 11 Johannes 0 0

46 0 Bertil 32 12
46 1 Brita 682 11
46 10 Axel 0 0
46 12 Adam 0 0

32 0 Olof 21 13
32 1 Margareta 2335 12
32 10 Johan 0 0
32 15 Magda 0 0

My father has rank 0 and my mother rank 1.
I am Johannes (rank 11=second born child) and 1883 is my whole Families number. My granfathers familyno is 46 and my grandmothers 1447.

I want let the user first search for a name. The answere is probably several with the same namn. But one is the rigtht.
Let´s say he click on the familynr 1883 and gets my whole family. Now I want to see the whole family 46 and if the father there rank 0 has a grandfamily I want to se that family (here shown as familyno 32). As long as the father has a grandfamily I want to see them until ther is no more. And if the user clicks on rank 1=the mother we follow all mothers and grandmothers with family back. Is it possibly to do that in one select? And how is this problem solved?
It would be pretty nice to let the user see all there ancestors in just one click.
Hope you understand my text even if I am a retired man living in Sweden. Otherwise let me know.

[kgun]

Do you know what a relational database is?

Do you know what inner and outer joins are?

[segerdalen]

You see that in this case all people are in the same table coming from excelsheet so joins are not a solution as I can see.

[kgun]

Select the unique columns you need.

SQL statement

select from

If you have overlapping information, that is an indication that your table should be broken into lesser tables with an unique ID column.

KW search:

sql tutorial (you got a lot of Google suggest terms if you start like that).

[jammybiskit]

Is it possibly to do that in one select? And how is this problem solved?
It would be pretty nice to let the user see all there ancestors in just one click.

A solution may be possible in just one SELECT but it won't be easy. You would have to use a succession of self-joins which can quickly become unmanageable. SQL (all implementations) is really bad at this sort of thing.

MySQL on a normal PC is fast enough for you to consider applying a simple SELECT recursively. Embed the SELECT in a procedural language loop (I use Perl myself) and feed the Grandfamilyno back to the next iteration as the Familyno with an exit from the loop when Grandfamilyno is 0.

It's not as elegant as it might be but it would be more maintainable and you could still have your data in a single table as you do at the moment.

[segerdalen]

Thanks jammy.... but it looks being beyound my knowledge. But if you can give me an exampel I might test and increase my skill

[jammybiskit]

Hi, sorry for taking so long to respond again.

I've written a short Perl program to do this in the simple-minded way I suggested before. This creates a table in an existing database, populates it with your example data and then navigates the tree printing out some data each time it gets a match. The results for this example are

Name: Adam
Name: Bertil
Name: Olof

The interesting part occurs after the Inserts.

Familyno is set to 1883 (this would be entered by the user but is just set in the program here for simplicity.
HTML contains the results.

The loop fetches the row for rank 0 (the father) and then uses the Grandfamilyno to point to the next row to be fetched.
I presume that eventually the Grandfamilyno will be 0 (or we will run out of data) and this is what causes the exit from the loop.

...

  # Connect to database
  my $dbh = DBI->connect("DBI:mysql:$DBName:$DBHost", $UserName, $PWD) or die "can't connect to $DBName";

  
  # Create table and load data
  my $CreateScript = qq~
CREATE TABLE family_tree (
Familyno    integer not null, 
Familyrank    integer not null,
Name         varchar(100) not null,
Grandfamilyno    integer not null,
Grandfamilyrank    integer not null,

primary key (Familyno, Familyrank)
);
~;

  $dbh->do($CreateScript) or die "failed to create table: " . $dbh->errstr;

  $dbh->do("INSERT INTO family_tree VALUES (1883, 0, 'Adam', 46, 12)") or die $dbh->errstr;
  $dbh->do("INSERT INTO family_tree VALUES (1883, 1, 'Eva', 1447, 11)");
  $dbh->do("INSERT INTO family_tree VALUES (1883, 10, 'Lukas', 0, 0)");
  $dbh->do("INSERT INTO family_tree VALUES (1883, 11, 'Johannes', 0, 0)");
  $dbh->do("INSERT INTO family_tree VALUES (46, 0, 'Bertil', 32, 12)");
  $dbh->do("INSERT INTO family_tree VALUES (46, 1, 'Brita', 682, 11)");
  $dbh->do("INSERT INTO family_tree VALUES (46, 10, 'Axel', 0, 0)");
  $dbh->do("INSERT INTO family_tree VALUES (46, 12, 'Adam', 0, 0)");
  $dbh->do("INSERT INTO family_tree VALUES (32, 0, 'Olof', 21, 13)");
  $dbh->do("INSERT INTO family_tree VALUES (32, 1, 'Margareta', 2335, 12)");
  $dbh->do("INSERT INTO family_tree VALUES (32, 10, 'Johan', 0, 0)");
  $dbh->do("INSERT INTO family_tree VALUES (32, 15, 'Magda', 0, 0)");

  # Now do the find
  my $Familyno = 1883;
  my $HTML;

  while (1)
  {
    my $sth = $dbh->prepare("SELECT Familyno, Familyrank, Name, Grandfamilyno, Grandfamilyrank FROM family_tree WHERE Familyno = $Familyno AND Familyrank = 0");
    $sth->execute or die $dbh->errstr;
    my @row = $sth->fetchrow_array; # row is an array with the fetched values: row[0] is Familyno etc.
    if (!(@row and $row[3])) { last; } # break out of loop here if we have run out of data or if we are at the base of this branch
    $HTML .= "<br>Name: $row[2]"; # append data to output
    $Familyno = $row[3]; # Set up for next iteration
  }

  # tidy up
  $dbh->disconnect;

It's pretty crude but hopefully will give you a pointer as to how a full solution would work.

Of course it doesn't have to be written in Perl - any other language that has procedures and an interface to SQL will do: C, PHP, Python, Java ...

If you want to pursue the single SQL statement approach, Google 'SQL parts explosion' or 'SQL bill of materials'. This problem is sometimes set as an exercise to computer science students.

[sushil]

I think you will get help form the following link.
PHP and mySQL - comparing data from two tables - SitePoint Forums

[segerdalen]

I already have a db with all this information. Is it your meaning that I should make a new one for this purpose??
Why can't I do the select direct to my db as is?

[jammybiskit]

I already have a db with all this information. Is it your meaning that I should make a new one for this purpose??
Why can't I do the select direct to my db as is?

No, I didn't mean that you needed to make a new database. In order to demonstrate that the proposed solution really works I had to create a table myself. As I don't know the exact structure of your database - only what you gave in your original example - I had to guess at the tables and columns you have used.

If my database design does not match yours, you will have to modify the SELECT statement to match your own columns. Otherwise, the bit after the 'now do the find' comment should work.

Obviously, if you want to use a different language from Perl then you will have to change that code too.