Report to display all places in TNG, but in reverse order?

[JMM]

• TNG 15.0.2

Hello, folks,

Would it be possible to somehow create a TNG report that lists all places in my TNG database, but have it displayed in reverse order?  Rather than try to explain it, here is an example (generated by Legacy) of what I'm talking about:

And although not a necessity, it would be nice if each of the above lines included a hyperlink to each.

Would that be doable?  Or would anyone already have such a report?

Thanks in advance.

Regards,
John

[theKiwi]

Try this

SELECT place
FROM tng_places
ORDER BY CASE WHEN LOCATE( ',', place ) =0 THEN place ELSE TRIM( SUBSTRING_INDEX( place, ',', -1 ) ) END , CASE WHEN LOCATE( ',', place ) =0 THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -2 ) , ',', 1 ) ) END ,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -2 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) ) THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) ) END ,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) ) THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) ) END ,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) ) THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) ) End, 
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -6 ) , ',', 1 ) ) THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -6 ) , ',', 1 ) ) End

Roger

[JMM]

Thanks, Roger.

It is indeed displaying all places in my database, thank you, but they are displaying normally (i.e. city, county, state, country), instead of reversed (i.e. country, state, county, city).

And they are not sorting at all either, but then, I forgot to mention that initially .

Thank you for taking the time to put together the above code, it is sincerely greatly appreciated.

Regards,
John

[JMM]

16 minutes ago, JMM said:

And they are not sorting at all either, but then, I forgot to mention that initially .

Actually, I just noticed: it looks like it is indeed sorting all the places.  Thank you, Sir.

I hadn't noticed, because they were all listed normally, instead of reversed, so it wasn't obvious that they were indeed sorted.

[Rob Severijns]

For those looking for reports.

Have a look at this wiki page: Category:Henny Savenije - Reports - TNG_Wiki

The page contains more than a hundred reports.

Always good to check this page before asking for reports for it might just have what you're looking for.

BTW John. The one you requested isn't in the list.

[JMM]

Just now, Rob Severijns said:

BTW John. The one you requested isn't in the list.

Yes, I already looked.  I already have a dozen or so of his reports that I found on that Wiki page a couple of years ago, but as you say, what I'm looking for isn't one of them.

Roger has already started to help me with this report that I'm looking for.  His code displays what I am looking for, and sorted.  But the only problem as of this time is that the report is displaying the places normally (example: city, county, state, country), instead of reversed the way I would like it (example: country, state, county, city).

We're almost there .

Thanks, Rob.

[JMM]

On 6/11/2025 at 4:03 AM, JMM said:

Would that be doable?

I was hoping that it might be doable, but it seems it is not.  It was worth a try.

[Rob Roy]

I was hoping it was doable as well

 

Sailor Rob

[Warren S GILBERT]

@JMM, @Rob Roy,

Did you ever get a solution to this?

I can see that the topic is old, but I don't come here often, and I was browsing the other day, and this interested me.

I have a solution, but don't want to clutter the Forum with duplicates.

Warren

[JMM]

21 minutes ago, Warren S GILBERT said:

@JMM, @Rob Roy,

Did you ever get a solution to this?

Nope.  Someone offered some help initially, but their solution did not work at all.

21 minutes ago, Warren S GILBERT said:

I have a solution, but don't want to clutter the Forum with duplicates.

I would love to learn what your solution is, Warren.  Thanks in advance.

 

[Warren S GILBERT]

Hi JMM,

It's based on what theKiwi posted, above, but with an extension.

My places are all 4 elements or less. There is a limitation to to this report in that it will only work with place names that are 6 elements or less, but could easily be extended.

SELECT
CASE WHEN s.p2 = '' THEN s.p1
WHEN s.p3 = '' THEN CONCAT_WS(', ', s.p1, s.p2)
WHEN s.p4 = '' THEN CONCAT_WS(', ', s.p1, s.p2, s.p3)
WHEN s.p5 = '' THEN CONCAT_WS(', ', s.p1, s.p2, s.p3, s.p4)
WHEN s.p6 = '' THEN CONCAT_WS(', ', s.p1, s.p2, s.p3, s.p4, s.p5)
ELSE CONCAT_WS(', ', s.p1, s.p2, s.p3, s.p4, s.p5, s.p6) END AS reversedplace
FROM
(SELECT
CASE WHEN LOCATE( ',', place ) =0 THEN place ELSE TRIM( SUBSTRING_INDEX( place, ',', -1 ) ) END AS p1, 
CASE WHEN LOCATE( ',', place ) =0 THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -2 ) , ',', 1 ) ) END AS p2,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -2 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) ) THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) ) END AS p3,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) ) THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) ) END AS p4,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) ) THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) ) END AS p5, 
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -6 ) , ',', 1 ) ) THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -6 ) , ',', 1 ) ) END AS p6
FROM tng_places) AS s
ORDER BY reversedplace

Regards,

Warren
 

[JMM]

Awesome.  It works perfectly.  Thank you, sir.

I do have 1 minor question: in your query, you show the placenames as "reversedplace" (without the quotes).  I was able to modify mine slightly, to be (for example) Reversed_Place, but I was unable to make it into more than 1 word.  Ideally, I would like mine to say: Places (Reversed).  Would that somehow be possible?

Thank you in advance, and thank you again for getting this query to work.

Regards,
John

[Warren S GILBERT]

Hi JMM,

I always try to avoid using embedded spaces and non-standard characters in table names, column names, aliases etc., but it can be done if you enclose the name in back-ticks (`).

e.g.

AS `Place (Reversed)`

and

ORDER BY `Place (Reversed)`

I changed the report and it ran OK in phpMyAdmin.

I then tried it as a TNG SQL report, and it failed! The correct number of rows, but no content in the `Place (Reversed)` column.

It does work (as you said) if I change the alias to Place_Reversed.

Unfortuntely, it would seem that back-ticks are not allowed in TNG reports.

Regards,

Warren

[JMM]

12 minutes ago, Warren S GILBERT said:

Unfortuntely, it would seem that back-ticks are not allowed in TNG reports.

No problem, Sir.  I'm happy just the way it is.

The only other thing that I can think of, would be to have a column that displays the number of associations for each place displayed.  But as I'm quite possibly the only person that would ever view that listing, it is definitely not worth the effort.

Thanks again for your time & expertise, Warren.

Regards,
John

[JMM]

4 hours ago, Warren S GILBERT said:

There is a limitation to to this report in that it will only work with place names that are 6 elements or less, but could easily be extended.

I did just that... as it is guaranteed that if/when in the future I end up adding a place with more than 6 levels (elements), I will forget to increase it in this report to compensate, or forget entirely how to do it .  So I've increased it to a nice round-figure of 10 .

Thanks again, Warren.

John

[Warren S GILBERT]

Hi John,

Quote

The only other thing that I can think of, would be to have a column that displays the number of associations for each place displayed.  But as I'm quite possibly the only person that would ever view that listing, it is definitely not worth the effort.

Well, that's as maybe, but I took that as a challenge - SQL below.

It accumulates references to the following places:

people:- birthplace, altbirthplace, deathplace, burialplace
families:-marrplace, divplace
events:- eventplace
cemeteries:- place
media:- placetaken
medialinks:- personID (linktype = 'L', i.e. media linked to a place).

I have omitted all LDS places (because I don't use them), but they are easy enough to add.

I couldn't find any places other than those.

Because the SQL makes multiple passes of some tables, it runs for longer than the original version, but unless you have a huge database, it should run OK.

I hope you find it useful (or at least interesting)!

Warren

SELECT
CASE WHEN x.p2 = '' THEN x.p1
WHEN x.p3 = '' THEN CONCAT_WS(', ', x.p1, x.p2)
WHEN x.p4 = '' THEN CONCAT_WS(', ', x.p1, x.p2, x.p3)
WHEN x.p5 = '' THEN CONCAT_WS(', ', x.p1, x.p2, x.p3, x.p4)
WHEN x.p6 = '' THEN CONCAT_WS(', ', x.p1, x.p2, x.p3, x.p4, x.p5)
WHEN x.p7 = '' THEN CONCAT_WS(', ', x.p1, x.p2, x.p3, x.p4, x.p5, x.p6)
WHEN x.p8 = '' THEN CONCAT_WS(', ', x.p1, x.p2, x.p3, x.p4, x.p5, x.p6, x.p7)
WHEN x.p9 = '' THEN CONCAT_WS(', ', x.p1, x.p2, x.p3, x.p4, x.p5, x.p6, x.p7, x.p8)
WHEN x.p10 = '' THEN CONCAT_WS(', ', x.p1, x.p2, x.p3, x.p4, x.p5, x.p6, x.p7, x.p8, x.p9)
ELSE CONCAT_WS(', ', x.p1, x.p2, x.p3, x.p4, x.p5, x.p6, x.p7, x.p8, x.p9, x.p10) END AS Place_Reversed,
x.Used
FROM
(SELECT
place,
y.Used,
CASE WHEN LOCATE( ',', place ) = 0 THEN place ELSE TRIM( SUBSTRING_INDEX( place, ',', -1 ) ) END AS p1,
CASE WHEN LOCATE( ',', place ) = 0 THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -2 ) , ',', 1 ) ) END AS p2,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -2 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) ) THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) ) END AS p3,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -3 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) ) THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) ) END AS p4,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -4 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) ) THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) ) END AS p5,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -5 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -6 ) , ',', 1 ) ) THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -6 ) , ',', 1 ) ) END AS p6,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -6 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -7 ) , ',', 1 ) ) THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -7 ) , ',', 1 ) ) END AS p7,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -7 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -8 ) , ',', 1 ) ) THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -8 ) , ',', 1 ) ) END AS p8,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -8 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -9 ) , ',', 1 ) ) THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -9 ) , ',', 1 ) ) END AS p9,
CASE WHEN TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -9 ) , ',', 1 ) ) = TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -10 ) , ',', 1 ) ) THEN ' ' ELSE TRIM( SUBSTRING_INDEX( SUBSTRING_INDEX( place, ',', -10 ) , ',', 1 ) ) END AS p10
FROM tng_places
INNER JOIN
(SELECT
z.ThisPlace,
SUM(Total) AS Used
FROM
(SELECT birthplace AS ThisPlace, COUNT(*) AS Total FROM tng_people WHERE birthplace != '' GROUP BY ThisPlace
UNION ALL
SELECT deathplace AS ThisPlace, COUNT(*) AS Total FROM tng_people WHERE deathplace != '' GROUP BY ThisPlace
UNION ALL
SELECT altbirthplace AS ThisPlace, COUNT(*) AS Total FROM tng_people WHERE altbirthplace != '' GROUP BY ThisPlace
UNION ALL
SELECT burialplace AS ThisPlace, COUNT(*) AS Total FROM tng_people WHERE burialplace != '' GROUP BY ThisPlace
UNION ALL
SELECT marrplace AS ThisPlace, COUNT(*) AS Total FROM tng_families WHERE marrplace != '' GROUP BY ThisPlace
UNION ALL
SELECT divplace AS ThisPlace, COUNT(*) AS Total FROM tng_families WHERE divplace != '' GROUP BY ThisPlace
UNION ALL
SELECT eventplace AS ThisPlace, COUNT(*) AS Total FROM tng_events WHERE eventplace != '' GROUP BY ThisPlace
UNION ALL
SELECT place AS ThisPlace, COUNT(*) AS Total FROM tng_cemeteries WHERE place != '' GROUP BY ThisPlace
UNION ALL
SELECT placetaken AS ThisPlace, COUNT(*) AS Total FROM tng_media WHERE placetaken != '' GROUP BY ThisPlace
UNION ALL
SELECT personID AS ThisPlace, COUNT(*) AS Total FROM tng_medialinks WHERE linktype = 'L' AND personID != '' GROUP BY ThisPlace
) z
GROUP BY ThisPlace
) y
ON place = y.ThisPlace
GROUP BY place
) x
ORDER BY Place_Reversed

 

[JMM]

1 hour ago, Warren S GILBERT said:

Because the SQL makes multiple passes of some tables, it runs for longer than the original version, but unless you have a huge database, it should run OK.

I hope you find it useful (or at least interesting)!

Awesome.  It works perfectly, as is.  And I don't use LDS either.

I'm using this report of yours as a main-menu item on my TNG Home Page, along with the other Places menu items.

And I have only about 1,500 places, so the report runs almost instantaneously.

Thanks again for your help, Warren, it is greatly appreciated for taking the time.

Regards,
John

[Rob Roy]

Works for me as well.

Sailor Rob