Generating HTML links on your reports (Family Size example)

[Luke]

Hello all,

I've seen several questions about creating links on each record of a report to be able to click to the full individual or family screen. This is accomplished by encapsulating a particular data string with the appropriate <a href=> html codes.

This works in TNG Report because the output of the SQL is expected to be HTML. This will not look good with other SQL processors which print out text rather than HTML. Therefore, the following SQL is TNG Report specific.

The key to doing this is the"concat" statement which strings together constant HTML statements with the fields from the database:

concat('<a href="getperson.php?personID=',f.husband,'&tree=', f.gedcom,'">',h.firstname,' ',h.lastname,'</a>') as Husband,  

In the above example we are looking at the "family" table joined with the "people" table. First an HTML link is created with the "husband" and "gedcom"(tree) fields. Then the "firstname" and "lastname" fields are strung together as the link title. Finally the end link code is appended and the entire thing is called "Husband" Following is an SQL statement with an example of both family and individual HTML links This query finds the size of all families, placing the largest first.

SELECT 
concat('<a href="familygroup.php?familyID=',f.familyid, '&tree=', f.gedcom,'">', f.familyid,'</a>') as familyid, 

concat('<a href="familygroup.php?familyID=',f.familyid, '&tree=', f.gedcom,'">', if(isnull(h.lastname), ' ',h.lastname),'/ ',if(isnull(w.lastname), ' ',w.lastname),'</a>') as FamilyName,
count(c.personid) as Size, 

group_concat(ch.firstname order by ordernum separator ', ') as Children,    

concat('<a href="getperson.php?personID=',f.husband,'&tree=', f.gedcom,'">',h.firstname,' ',h.lastname,'</a>') as Husband,  

concat('<a href="getperson.php?personID=',f.wife,'&tree=', f.gedcom,'">',w.firstname,' ',w.lastname,'</a>') as Wife, 

f.marrdate, f.marrplace, f.branch as Branch  FROM tng_families as f 
left join tng_people as h on f.gedcom = h.gedcom and f.husband = h.personid 
left join tng_people as w on f.gedcom=w.gedcom and f.wife = w.personid 
left join tng_children as c on f.gedcom=c.gedcom and f.familyid = c.familyid 
left join tng_people as ch on f.gedcom=ch.gedcom and c.personid = ch.personid 
WHERE f.gedcom = 'weerts'  
group by c.gedcom,c.familyid 
order by Size desc, h.lastname,w.lastname,h.firstname, w.firstname

Any questions? Just ask.

Luke

[aewold]

This SQL report (from a modified Luke report) shows the number of children fathered by the men in the database.

SELECT f.gedcom, count(c.personid) as Children,
concat('<a href="descendtext.php?personID=',h.personid,'&tree=TREENAME&display=block&generations=2">',concat(h.firstname,' ',h.lastname),'</a>') as Husband
FROM tng_families as f
left join tng_people as h on f.gedcom=h.gedcom and f.husband = h.personid
left join tng_people as w on f.gedcom=w.gedcom and f.wife = w.personid
join tng_children as c on f.gedcom=c.gedcom and f.familyid = c.familyid
WHERE f.gedcom = "TREENAME" 
AND h.firstname NOT LIKE '(null%'
group by c.gedcom,h.personid
order by Children desc 

Quite handy for those of us with polygamists in our lines.

Change the two TREENAME's to your tree names, and you can remove the "AND h.firstname NOT LIKE '(null%' " section (I use "(null child)" for generation place holders from obituaries.)

The Husband's Name link will bring up a 2 generation text Descendancy report for the man.

Andy

[Brewmaster]

This SQL report (from a modified Luke report) shows the number of children fathered by the men in the database.

SELECT f.gedcom, count(c.personid) as Children,
concat('<a href="descendtext.php?personID=',h.personid,'&tree=TREENAME&display=block&generations=2">',concat(h.firstname,' ',h.lastname),'</a>') as Husband
FROM tng_families as f
left join tng_people as h on f.gedcom=h.gedcom and f.husband = h.personid
left join tng_people as w on f.gedcom=w.gedcom and f.wife = w.personid
join tng_children as c on f.gedcom=c.gedcom and f.familyid = c.familyid
WHERE f.gedcom = "TREENAME" 
AND h.firstname NOT LIKE '(null%'
group by c.gedcom,h.personid
order by Children desc 

Quite handy for those of us with polygamists in our lines.

Change the two TREENAME's to your tree names, and you can remove the "AND h.firstname NOT LIKE '(null%' " section (I use "(null child)" for generation place holders from obituaries.)

The Husband's Name link will bring up a 2 generation text Descendancy report for the man.

Andy

Any idea what went wrong? This is the response to running that script in reports...

SELECT f.gedcom, count(c.personid) as Children, concat('',concat(h.firstname,' ',h.lastname),'') as Husband FROM tng_families as f left join tng_people as h on f.gedcom=h.gedcom and f.husband = h.personid left join tng_people as w on f.gedcom=w.gedcom and f.wife = w.personid join tng_children as c on f.gedcom=c.gedcom and f.familyid = c.familyid WHERE f.gedcom = "difava-brandt" AND h.firstname NOT LIKE '(null%' group by c.gedcom,h.personid order by Children desc

('',concat(h.firstname,' ',h.lastname), is high;ighted and underlined and claims to be the error...

Now I'm lost!!!!

Brewmaster

[aewold]

Brewmaster,

Just change the

concat('',concat(h.firstname,' ',h.lastname),'') as Husband

to the following

concat(concat(h.firstname,' ',h.lastname)) as Husband

(but you will not have the link to the person)

Andy

[Torben]

Hello all,

I've seen several questions about creating links on each record of a report to be able to click to the full individual or family screen. This is accomplished by encapsulating a particular data string with the appropriate <a href=> html codes.

This works in TNG Report because the output of the SQL is expected to be HTML. This will not look good with other SQL processors which print out text rather than HTML. Therefore, the following SQL is TNG Report specific.

The key to doing this is the"concat" statement which strings together constant HTML statements with the fields from the database:

concat('<a href="getperson.php?personID=',f.husband,'&tree=', f.gedcom,'">',h.firstname,' ',h.lastname,'</a>') as Husband,  

In the above example we are looking at the "family" table joined with the "people" table. First an HTML link is created with the "husband" and "gedcom"(tree) fields. Then the "firstname" and "lastname" fields are strung together as the link title. Finally the end link code is appended and the entire thing is called "Husband" Following is an SQL statement with an example of both family and individual HTML links This query finds the size of all families, placing the largest first.

SELECT 
concat('<a href="familygroup.php?familyID=',f.familyid, '&tree=', f.gedcom,'">', f.familyid,'</a>') as familyid, 

concat('<a href="familygroup.php?familyID=',f.familyid, '&tree=', f.gedcom,'">', if(isnull(h.lastname), ' ',h.lastname),'/ ',if(isnull(w.lastname), ' ',w.lastname),'</a>') as FamilyName,
count(c.personid) as Size, 

group_concat(ch.firstname order by ordernum separator ', ') as Children,    

concat('<a href="getperson.php?personID=',f.husband,'&tree=', f.gedcom,'">',h.firstname,' ',h.lastname,'</a>') as Husband,  

concat('<a href="getperson.php?personID=',f.wife,'&tree=', f.gedcom,'">',w.firstname,' ',w.lastname,'</a>') as Wife, 

f.marrdate, f.marrplace, f.branch as Branch  FROM tng_families as f 
left join tng_people as h on f.gedcom = h.gedcom and f.husband = h.personid 
left join tng_people as w on f.gedcom=w.gedcom and f.wife = w.personid 
left join tng_children as c on f.gedcom=c.gedcom and f.familyid = c.familyid 
left join tng_people as ch on f.gedcom=ch.gedcom and c.personid = ch.personid 
WHERE f.gedcom = 'weerts'  
group by c.gedcom,c.familyid 
order by Size desc, h.lastname,w.lastname,h.firstname, w.firstname

Any questions? Just ask.

Luke

Hi Luke

Is it possible to change this report,

so it only shows living people, when I'm or one of my users are loged in

Torben

[genhcn]

Bonjour!

Trouver les familles les plus nombreuses

Exactement ce que je cherchais

Possible d'avoir le code prêt à l'emploi?

Je ne suis pas très forte en programmation et j'aimerais bien avoir cet élément sur ma page d'accueil.

http://www.genhcn.net/genealogie/

Not speak english... sorry for my english.

Hi!

Find the largest families

Exactly what I want

Possible to have the code ready?

I'm not very good at programming. I would like to have this item on my homepage

Thank!

Nat

[genhcn]

Quelqu'un pour m'aider à construire cette page?

Comment se servir de ce code où le placer?

J'aurais besoin d'un peu d'indice....

someone to help me build this page?

How to use this code? where?

Thank's

Nat

[Ken Roy]

Quelqu'un pour m'aider à construire cette page?

Comment se servir de ce code où le placer?

J'aurais besoin d'un peu d'indice....

someone to help me build this page?

How to use this code? where?

Thank's

Nat

Nat,

To create this report, go to TNG Admin >> Reports

1 - select Add New

2 - Enter a Title for the report, like Largest Family Report

3 - Enter a Description for the report, like The Largest Family Report was provided by Luke Weerts on the <a href="http://www.tngforum.us/index.php?s=&showtopic=2907&view=findpost&p=13260" target="_blank">TNG Forum</a>

4 - Copy the SQL statement within the Code section in the TNG Forum Post above http://www.tngforum.us/index.php?s=&sh...ost&p=13260

5 - Paste it in the TNG input field just above the Save button

6 - Change every '&tree=' to '&tree=your-treeID' where treeID is the ID you assigned to your tree record, mine is T0001

7 - update the WHERE f.gedcom = 'weerts' to replace weerts with your treeID

8 - save the report

9 - use the test icon to test the report. It produced a listing of Matches 1 to 50 of 5250

Note that at this point, the report is not Active and therefore not available in the public side of the site. Whether you make it active will be up to you. Personally, I would not since I have over 8000 marriages in my database, so it will use quite a bit of resources.

Sorry, I did not get a change to translate it to French, even though I read, write, and speak French.

Ken

[genhcn]

My english is bad and Google traduction is very, very bad!

MERCI MERCI MERCI

Thank you!!!!

I'm understand now!

TNG is realy fantastic script!

I wish I could put this result public

J'aimerais pouvoir mettre ce résultat public!

Je travaille à la généalogie d'une région et je suis certaine que ça plairait beaucoup aux gens de voir les familles nombreuses.

I work in the genealogy of a region and I am sure it would please many people to see Top-10 or Top-20 large families.

[Ken Roy]

My english is bad and Google traduction is very, very bad!

MERCI MERCI MERCI

Thank you!!!!

I'm understand now!

TNG is realy fantastic script!

I wish I could put this result public

J'aimerais pouvoir mettre ce résultat public!

Je travaille à la généalogie d'une région et je suis certaine que ça plairait beaucoup aux gens de voir les familles nombreuses.

I work in the genealogy of a region and I am sure it would please many people to see Top-10 or Top-20 large families.

Note that all you would need to do is set the report Active to Yes, when you add a new report it defaults to No.

Vous avez simplement a selectionner Oui pour marquer le rapport Actif

[genhcn]

Je vois...

Sauf que

On voit aussi le code!

De plus, je dois limiter la demande au 10 ou 15 familles les + nombreuses, sinon, c'est beaucoup, j'ai 14,766 familles dans ma base.

[Ken Roy]

Je vois...

Sauf que

On voit aussi le code!

De plus, je dois limiter la demande au 10 ou 15 familles les + nombreuses, sinon, c'est beaucoup, j'ai 14,766 familles dans ma base.

le code SQL est seulement montrer quand l'administrateur test le rapport. Yous devrez demander sur la liste User2 comment limiter le rapport aux 15 familles principalles

The SQL code is only shown when the Admin is testing the report. You might ask on the User2 list or in the forum how to limit the output to the top 15 families

[genhcn]

Merci de votre aide et votre patience

Thank you for your help and your patience.

Demain, je regarde tout ça et j'essayerai

Tomorrow, I look at it and I'll try

Ce script m'impressionne vraiment beaucoup par sa simplicité car je ne connais pas beaucoup de chose en php/mysql et je réussis à personnaliser beaucoup de chose.

Encore merci

Nat

[billard]

:!:

Bonjour,

j'ai créé des rapports multiples. I created multiple reports.

Certains montrent des liens actifs pour chaque individu (Some show active links for each individual): balance- libra

d'autres ne donnent que des liens inactifs: erreur 404 "Not found" (others give only inactive links: 404 "Not found"):Guerre 1914-1918

comment obtenir des liens fiables? how to get reliable links?

Il est certain que pour les francophones les explications sont difficilement compréhensibles.... Y-a-t-il une communauté francophone??? (It is certain that for Francophones explanations are difficult to understand .... When a French-speaking community??)

Gracias

Serge My Webpage

[billard]

:oops:

De plus je n'ai pas compris comment limiter le nombre de famille dans le rapport (Also I did not understand how to limit the number of families in the report): Les Familles les Plus Nombreuses

Il est certain que pour les francophones les explications sont difficilement compréhensibles.... Y-a-t-il une communauté francophone??? (It is certain that for Francophones explanations are difficult to understand .... When a French-speaking community??)

Gracias

Serge My Webpage