Page 1 of 1

GROUPING RESULTS

Posted: Fri Sep 05, 2003 9:37 am
by kendall
Hello,

I have 2 tables individuals and families...
im trying to create a query that lists all the families and extract the names of the members of the families associated with the family

thus i have created the following query
SELECT family.FAMID, family.Married, individual.FName, individual.MName, individual.LName FROM quesnel_marriages AS family, quesnel_individuals AS individual WHERE individual.INDID = family.Husband OR individual.INDID = family.Wife ORDER BY family.Married, individual.Born ASC
The results of this query come out like this....
1 209 1960-06-24 Vincent Edmund Quesnel
2 209 1960-06-24 Ulusia Joan Henderson De Lyon
Is there any way to group the results into a class field
1 209 1960-06-24 Vincent Edmund Quesnel Ulusia Joan Henderson De Lyon
IDeas are greatly appreciated

Kendall
[/quote]

Posted: Fri Sep 05, 2003 5:33 pm
by JAM
try adding, after WHERE, before ORDER

Code: Select all

GROUP BY family.FAMID, family.Married, individual.FName, individual.MName, individual.LName
(change the order of your preference)

http://www.mysql.com/doc/en/GROUP-BY-Functions.html

GROUPING RESULTS

Posted: Sat Sep 06, 2003 1:39 pm
by kendall
JAM,

I have tried your suggestion and i got the same results as before...However look through mysql documentation i found a CONCAT_WS() function so im trying the following query

Code: Select all

SELECT family.FAMID, family.Married, CONCAT_WS(" ",individual.INDID, individual.FName, individual.MName, individual.LName) AS Spouse1, CONCAT_WS(" ",individual.INDID, individual.FName, individual.MName, individual.LName) AS Spouse2 FROM quesnel_marriages AS family, quesnel_individuals AS individual GROUP BY family.FAMID
the result i get is
241 1992-03-16 9 Vincent Edmund Quesnel 9 Vincent Edmund Quesnel
which is more leaning to the result 'format' that i want but its duplicateing the entry.....hmmmmm

what i need to do is..
group by family ID
class the husband and wife

any ideas....im thinking....im thinking....

Kendall

Posted: Sat Sep 06, 2003 2:13 pm
by JAM
Hard when you cant test... =/
SELECT DISTINCT ... ?
Tho, if you have two Edmund (thats really are two Edmund's) You might have a problem...

GROUPING RESULTS CONCAT_WS()

Posted: Mon Sep 08, 2003 8:48 am
by kendall
JAM,

No there isnt 2 edmunds....at least not yet!! I don't see it being a problem as im identifying them by FAMID groupings....

Now note in my squery string i have CONCAT_WS() AS Spouse1 twice...I was using this to try to group the results by FAMID Spouse1 (husband) Spouse2 (wife) MArried date....Can I have expression in the CONCAT_WS? Cause i just realise i have to detect the males from females....

GROUPING RESULTS CONCAT_WS()

Posted: Mon Sep 08, 2003 10:14 am
by kendall
JAM,

ok i got it here is the following query i used

Code: Select all

SELECT DISTINCT family.FAMID, family.Married, CONCAT_WS(" ",husband.INDID, husband.FName, husband.MName, husband.LName) AS Spouse1, CONCAT_WS(" ",wife.INDID, wife.FName, wife.MName, wife.LName) AS Spouse2 FROM quesnel_marriages AS family, quesnel_individuals AS husband, quesnel_individuals AS wife WHERE husband.INDID = family.Husband AND wife.INDID = family.Wife GROUP BY family.FAMID
which give me the desired results
235 1990-04-23 2248 Valentine Marc Arneaud 2249 Sandra Mary Alexander
which is the family id, the year they were married, the husbands id and name, the wifes id and name

Was there an easier way to do this....just asking

Kendall

Re: GROUPING RESULTS CONCAT_WS()

Posted: Mon Sep 08, 2003 1:15 pm
by JAM
kendall wrote:Was there an easier way to do this....just asking
I'm sitting here with a rather stupid "uhmmmm..." look on my face. Im wondering if this cant be done by rearranging the join's abit.

But as you stated, the desired result is achieved, so...

If you really want to continue, by all means dump the table structures and a couple of lines of data for me. If you bother that is...
If not, glad you sorted it out ;)

GROUPING RESULTS CONCAT_WS()

Posted: Mon Sep 08, 2003 4:49 pm
by kendall
JAM,

sure man...just wanna get query optimised....

heres the structure

---------quesnel_individuals---------
INDID
FName
MName
LName
SEX
BORN
DIED
-------------------------------------------
-------------quesnel_marriages--------
FAMID
Husband
Wife
Married
Divorced
-------------------------------------------

not much to the code but

Code: Select all

if($index == 'family'){
	 $query = 'SELECT DISTINCT family.FAMID, family.Married, CONCAT_WS(" ",husband.FName, husband.MName, husband.LName) AS Spouse1, CONCAT_WS(" ",wife.FName, wife.MName, wife.LName) AS Spouse2 FROM quesnel_marriages AS family, quesnel_individuals AS husband, quesnel_individuals AS wife WHERE husband.INDID = family.Husband AND wife.INDID = family.Wife GROUP BY family.FAMID '; 
	$FAMILIES = QueryDatabase(&$database,$query,$Connection);
	$families = mysql_num_rows($FAMILIES);
	$Family = mysql_fetch_assoc($FAMILIES);
	// get individuals of the family
	echo 'total of - '.$families.'<br>';
	do&#123;
		// print family
		echo '<a href="display_family_tree.php?ID='.$Family&#1111;'FAMID'].'" target="main">';
		echo $Family&#1111;'Spouse1'].' & '.$Family&#1111;'Spouse2'].' on '.$Family&#1111;'Married'];
		echo '</a><br>';
	&#125;while($Family = mysql_fetch_assoc($FAMILIES));
the URL is http://www.quesnels.com/draft/

is just a mock up really im just trying to develop the components i may need before i really put in into workings.....this is hard...and im an ametuer

rite now im working on getting it to display a graphical family tree using DIVS...going good so far but not keen


Kendall