dicksonvm Posted January 14, 2008 Report Share Posted January 14, 2008 Hi,Is it possible to generate a report of the not used placenames? While there is not a administrative tool to delete the no longer used placenames that have some coordinates data, I would like to have this report and delete them manually. Any ideas?Thank you!Dickson von Mühlen Quote Link to comment Share on other sites More sharing options...
Luke Posted January 15, 2008 Report Share Posted January 15, 2008 Hi,Is it possible to generate a report of the not used placenames? While there is not a administrative tool to delete the no longer used placenames that have some coordinates data, I would like to have this report and delete them manually. Any ideas?Thank you!Dickson von MühlenHi Dickson,This is fairly easy with SQL. Here is an example of the report:http://www.cousinfolk.net/showreport.php?reportID=70Here is the SQL statement. It requires at least mysql 4.1 because it uses subqueries. Here's how it works: A union of six queries finds all the fields where places are used. 'tng_places left join the used places' will give all places and if there isn't an equivalent used place that field will have a null. So all nulls will list unused places. Then list the fields where place is in the unused listHow does yours work? Is this what you want?LukeHere's the code:Note: 'yourtree' must be replaced with the name of you default tree in eight spots belowSELECT id, gedcom, place, longitude, latitude, notesFROM tng_placesWHERE gedcom = 'yourtree'AND placeIN (SELECT pl.placeFROM tng_places AS plLEFT JOIN (SELECT gedcom, birthplace AS placeFROM `tng_people`WHERE gedcom = 'yourtree'UNION SELECT gedcom, altbirthplaceFROM `tng_people`WHERE gedcom = 'yourtree'UNION SELECT gedcom, marrplaceFROM `tng_families`WHERE gedcom = 'yourtree'UNION SELECT gedcom, deathplaceFROM `tng_people`WHERE gedcom = 'yourtree'UNION SELECT gedcom, burialplaceFROM `tng_people`WHERE gedcom = 'yourtree'UNION SELECT gedcom, eventplaceFROM tng_eventsWHERE gedcom = 'yourtree') AS pUSING ( gedcom, place )WHERE pl.gedcom = 'yourtree'AND isnull( p.place )) Now that you see the places that are not used and are satisfied this list is correct and has what you want to delete, you may replace the first line (everything up to FROM) with the single word 'DELETE' and it will delete all the records in that the previous 'select' listed. For reference only,This is the equivalent DELETE: DO NOT COPY THIS, change your select that you know is working. One small typo in 'yourtree' could be disastrous AND delete far more than you intended. You have been warned. DELETEFROM tng_placesWHERE gedcom = 'yourtree'AND placeIN (SELECT pl.placeFROM tng_places AS plLEFT JOIN (SELECT gedcom, birthplace AS placeFROM `tng_people`WHERE gedcom = 'yourtree'UNION SELECT gedcom, altbirthplaceFROM `tng_people`WHERE gedcom = 'yourtree'UNION SELECT gedcom, marrplaceFROM `tng_families`WHERE gedcom = 'yourtree'UNION SELECT gedcom, deathplaceFROM `tng_people`WHERE gedcom = 'yourtree'UNION SELECT gedcom, burialplaceFROM `tng_people`WHERE gedcom = 'yourtree'UNION SELECT gedcom, eventplaceFROM tng_eventsWHERE gedcom = 'yourtree') AS pUSING ( gedcom, place )WHERE pl.gedcom = 'yourtree'AND isnull( p.place )) Quote Link to comment Share on other sites More sharing options...
dicksonvm Posted January 15, 2008 Author Report Share Posted January 15, 2008 Hi Luke,That was exactly what I needed and worked perfectly! I preferred to carefully delete manually each not used placename, but in only a few minutes I solved my "great" problem.Thank you!Dickskon von Mühlen Quote Link to comment Share on other sites More sharing options...
Luke Posted January 15, 2008 Report Share Posted January 15, 2008 Hi Luke,That was exactly what I needed and worked perfectly! I preferred to carefully delete manually each not used placename, but in only a few minutes I solved my "great" problem.Thank you!Dickskon von MühlenHi Dickson,You're welcome.Glad it worked for you. I understand completely wanting to carefully delete manually especially if its a small number. SQL deletes make me extremely nervous and SQL updates make me a little nervous. I always do an equivalent select before doing either but .... Really like your website. Wonder if my family and your family came across each other in Brasil. My grandfather, Edward Dicke, was a missionary to the German population in Ijuhy, Rio Grande do Sul, Brazil (anglicized spelling?) for 10 years in the 1920's. My mother was born there. Is that the same as Ijui on your website? See:http://www.cousinfolk.net/getperson.php?pe...amp;tree=weertsSincerely,Luke Quote Link to comment Share on other sites More sharing options...
dicksonvm Posted January 16, 2008 Author Report Share Posted January 16, 2008 Luke,The world is very small. Its probable that our ancestors really met in Brasil. I found out your grandfather was missionary in the same church we are related today (Lutheran Church - Missouri Synod). I found your grandfather's name in this document of our church: http://www.ielb.org.br/old/recursos/pdfs/p...falecidos_0.pdfIn the famly of my wife there are several pastors of this Church, including her great grandfather: http://genealogia.tati.dickson.nom.br/getp...personID=I18664. As he graduated as Pastor in 1918 and our Brazilian church was not so big that time, is really possible they met. And his father, Guilherme Augusto, lived near Ijuí in the 1920's. Ijuhy is the old spelling of Ijuí, that appears in my site. RS = Rio Grande do Sul (state).Very interesting!Best regards,Dickson Quote Link to comment Share on other sites More sharing options...
Tom Strong Posted July 26, 2009 Report Share Posted July 26, 2009 Luke, et alI have tried this report query in version 7.1 and get no results. I know I have unassociated/unlinked places so I thought I'd give this a try. When I run the report, all seems to operate correctly but I get NO RESULTS. Here is one example of an unlinked place that should be reported. http://www.strongfamilytree.org/placesearc...kogee,+OklahomaHas something changed in the database structure of version 7 that will allow this report to run but have no results? Or could it possibly be that these have been orphaned from TNG i.e. they have NO PLACE NUMBER and only exist in the SQL database and are manifested in TNG for any places query?Here is the report result of running the querySQL: SELECT id, gedcom, place, longitude, latitude, notes FROM tng_places WHERE gedcom = 'Strong Family Tree' AND place IN ( SELECT pl.place FROM tng_places AS pl LEFT JOIN ( SELECT gedcom, birthplace AS place FROM `tng_people` WHERE gedcom = 'Strong Family Tree' UNION SELECT gedcom, altbirthplace FROM `tng_people` WHERE gedcom = 'Strong Family Tree' UNION SELECT gedcom, marrplace FROM `tng_families` WHERE gedcom = 'Strong Family Tree' UNION SELECT gedcom, deathplace FROM `tng_people` WHERE gedcom = 'Strong Family Tree' UNION SELECT gedcom, burialplace FROM `tng_people` WHERE gedcom = 'Strong Family Tree' UNION SELECT gedcom, eventplace FROM tng_events WHERE gedcom = 'Strong Family Tree' ) AS p USING ( gedcom, place ) WHERE pl.gedcom = 'Strong Family Tree' AND isnull( p.place ) )Home Search Print Logout # ID Tree Place Longitude Latitude NotesThanks in advance,Tom Stronghttp://strongfamilytree.org Quote Link to comment Share on other sites More sharing options...
theKiwi Posted July 26, 2009 Report Share Posted July 26, 2009 ... WHERE gedcom = 'Strong Family Tree' Your gedcom is STR06 not "Strong Family Tree".Change that in all the places and it will work I'm sure.Roger Quote Link to comment Share on other sites More sharing options...
arnold Posted July 26, 2009 Report Share Posted July 26, 2009 This is fairly easy with SQL. Here is an example of the report:Not easy for me!!! What do I do to make the code below work? I know enough to get to phpMyAdmin.My thanks,Arnold Quote Link to comment Share on other sites More sharing options...
Tom Strong Posted July 26, 2009 Report Share Posted July 26, 2009 Thanks Roger,For the database name assumed that it was the "Tree Name" and not the "Tree ID". When I used the tree ID it worked perfectly.Arnold, when they say insert the code below, they are referring to the Admin Reports Create a New Report. On the TNG Wiki there is a graphic explanation of how this works. http://tng.lythgoes.net/wiki/index.php?tit..._with_no_EventsCheers,Tom StrongNot easy for me!!! What do I do to make the code below work? I know enough to get to phpMyAdmin.My thanks,Arnold Quote Link to comment Share on other sites More sharing options...
arnold Posted July 26, 2009 Report Share Posted July 26, 2009 Thanks, Tom.Worked like a charm.The TNG Wiki <http://tng.lythgoes.net/wiki/index.php?title=Main_Page> is fantastic.Arnold Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.