hopefully an easy question

[khtims75]

So I need some help grouping I think. I have a list of master_id’s in a table with a list of family_id’s for a family address book. each entry gets one master_id, then as many family_id’s as I need to assign them so far up to 3 but most have 2. So my table looks something like:

master_id|family_id
1|1
2|1
2|2
3|1
3|3
4|1
4|2
5|1
5|2
6|1
6|4

What I have set up is to have a family selected so it will return only where family_id = $_POST[fam_id]
I need it to now consider whatever the 2nd family_id for that master_id is and group or order by that number. Does that make sense? How can I do this?

This is my select statement so far:

$sel_id= “(select master_id from master_family where family_id = ‘$_POST[fam_id]’ order by master_id)”;

Thanks for any help I can get on this!

[Redscouse]

if i understand correct, your trying to select all members of the same family??

here’s a query that will do that,
$_POST[‘id’] will determine which family to choose:
[php]<?php
$query = “SELECT * FROM master_family WHERE master_id = $_POST[‘id’]”;
$result = mysql_query($query);

echo $_POST[‘id’]; // show the master id (IE: which family)
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo $row[‘family_id’]; // show each family member id
}
?>[/php]
Hope this helps,

[khtims75]

Thanks for the reply. That’s close. What I need is to have it select by family but sort by the 2nd family in the database based on their master_id. So one person would be assigned more than one family. I have everyone in the family listed under my grandmother. then each aunt and uncle has their own families and when I select everyone by choosing my grandmother’s name I need everyone grouped by my aunt and uncle’s children so they are not spread all over the place like it is now. So each person has a master_id and family_id. everyone has a family_id of 1 (my grandmother) and then their own family_id 2-30 (aunts & Uncles). when I select my grandmother I am only selecting all the 1’s so I cannot sort by 2-30. How can I get all of this to work? Thanks so much for helping.

[Redscouse]

add this to the select call:

ORDER BY master_id ASC, family_id ASC , own_id ASC
you can change the order listed using (ASC | DESC)

this will list the families by master id first, more than one member has the same id, then they will be listed by family id (the bit i’ve bolded is not needed but will also list by their own id if used (make sure to change this to proper column name)

I hope that gets you where your aiming,

[khtims75]

this might work if i change my query up a bit. thanks. right now my query only pulls one family_id at a time so sorting by it would do no good. I think i’m just going to state that if family_id =1 to pull a different query. THanks anyway.

[khtims75]

so i added a query for if family_id=1 and i still have an issue even with the order by mentioned above. the issue i’m seeing is because everyone’s first family_id is 1 so it sorts by that. I need it to sort by the 2nd family id which would be row 2 of their master_id. this is pretty frustrating. i wish I had gone to school for this now so I could have all these answers. Thanks for helping! This is what I have now for my query I need help with:

$sel_id= “SELECT * FROM master_family ORDER BY master_id asc, family_id asc”;

[Redscouse]

[tt]-----------------------------------------------------------
| master_id | family_id | own_id | first_name | last_name |

| 1 | 1 | 1 | john | doe | // founding family member (husband)
| 1 | 1 | 2 | jane | doe | // founding family member (wife)

| 1 | 2 | 3 | bob | doe | // john and jane doe’s son
| 1 | 2 | 4 | lisa | smith | // john and jane doe’s daughter

| 1 | 3 | 5 | billy | doe | // bob’s son

| 1 | 4 | 6 | betty | smith | // lisa’s daughter

[/tt]
as you can see from the table, they are all members of the same family (master_id)
and there are 3 individual families (john & jane, bob, lisa) (bob & billy) (lisa & betty)

query: SELECT * FROM family_tree ORDER BY master_id ASC, family_id ASC, own_id ASC

should produce something like: (depending on how you layout your html/php)

[tt]john doe
jane doe
bob doe
billy doe
lisa doe
betty doe
[/tt]
i would advise using seperate tables for the names to make it easier for you.

(please note, this is untested but should work without to much alteration.)