Changing the tree id in TNG

Posts: 101
Joined: Sun Oct 01, 2017 3:08 pm

Changing the tree id in TNG

Post by Steve »

I DO NOT recommend changing the tree id unless you made a mistake like I did. It is best to be familiar with phpMyAdmin but, if you're not, the instructions below are for modifying a copy of the database so the original is left unchanged. That said, when I first installed TNG, years ago, I used tree-1 for the tree ID because I didn't know any better. It worked although hyphens are not recommended and can cause errors. I wanted to install a fresh v12.1 but TNG no longer allows hyphens in the tree ID. This became a big problem because I could not enter tree-1 from the TNG readme.html page. So I decided to bite the bullet and fix the problem.

Unfortunately when you change the tree id, all search engine links like Google, Bing, MSN, Apple, Yahoo are broken. Those searching the internet and finding your site will receive Error 404 not found instead of a TNG page. Since the tree id occurs at or near the end of the URL there is no easy way to correct it using .htaccess. If you have a new site it is better to be patient and let the search engines correct the links. If you don't have a new site or want to capture the 404 errors, I've include a MOD at the bottom of this page that displays a friendly message with a link to search for people, families or sources depending on the error. Rush Krieger wrote the original Person Not Found MOD. I modified it work with families and sources too, so the name was changed to Link Not Found.

As mentioned earlier, it is best to work with a copy so to begin, create a new user and database. If you are not familiar with this procedure, here is a link to Instructions for creating the user and database. Use phpMyAdmin to copy the structure and data into the new empty database. The new database will still have the old tree id but these will be changed using SQL queries.

After you create the new empty database, select the existing TNG database from the left database column. Note the database you are copying from is listed at the top of the window.
Select the Operations tab from the top of the Window.
In the Copy Database to section, enter the new database name, select the Structure and Data radio button, uncheck CREATE DATABASE before copying. Leave Add AUTO INCREMENT, Add Constraints and Adjust Priviledges checked. Then select the Go button In the Copy Database to section.

I'm running a Synology NAS that that still uses php5.6 for phpMyAdmin which gave an error after the copy was complete, although I did not lose any data. If the error message is “column count value doesn't match the value count in row 1”, click to dismiss the error. If a different error occurs, check the code number to determine the cause with either phpMyAdmin, MySQL or MariaDB.
In any case, if you receive an error, select the new database to make sure the tables were copied. Then compare the number of rows in each table in the new database with the number of rows in each table in the original database. The row counts should match.

To change the tree id use phpMyAdmin to run queries on each of the tables below in the new database.
Note: if a table is empty you do not need to run the query on it.
  1. tng_addresses
  2. tng_associations
  3. tng_branches
  4. tng_branchlinks
  5. tng_children
  6. tng_citations
  7. tng_dna_groups
  8. tng_dna_links
  9. tng_dna_tests
  10. tng_events
  11. tng_families
  12. tng_media
  13. tng_medialinks
  14. tng_mostwanted
  15. tng_notelinks
  16. tng_people
  17. tng_places
  18. tng_repositories
  19. tng_saveimport
  20. tng_sources
  21. tng_temp_events
  22. tng_trees
  23. tng_users* (Requires two queries)
  24. tng_xnotes
To run a query in phpMyAdmin, open the database and select the table to modify. Then select SQL from the menu tabs at the top. Notice the top of the window displays the database and table that is open and the Run SQL query box shows the database and table info as well. Always verify you are accessing the correct database and table before making changes. Remember there is NO UNDO COMMAND if you run an incorrect query or run the query on the wrong database.
The SQL query in this example is a conditional query which means only trees named tree-1 will be changed. So if your database has more than one tree, any tree ids not matching tree-1 will not be changed. To run the query replace the default SELECT FROM line in the SQL window with that shown in the code below.

Code: Select all

UPDATE tng_addresses SET gedcom = 'tree1' WHERE gedcom = 'tree-1';
UPDATE is the query command that changes a table. In this example the table is tng_addresses.
SET gedcom = 'tree1' is the query command that specifies the column to change and the value to place in that column. In this case the value tree1 will be placed in the gedcom column.
WHERE gedcom = 'tree-1' is the conditional command. Only rows containing tree-1 will be changed.
Select Go and run the query which will change the value for the 'gedcom' column in this table. Repeat this procedure for each table listed above that is not empty which will be most of them. Make sure you enter the correct table name for each query that is run.

* Now run a second query on the tng_users table for people assigned to a specific tree. The query will update the mygedcom column in the users table.

Code: Select all

UPDATE tng_users SET mygedcom = 'tree1' WHERE mygedcom = 'tree-1';
Once you've finished, you will need to connect TNG to the new database. To connect, edit the TNG config.php file and change $database_name, $database_username and $database_password to match those of the new database just created. If you use a default tree change $defaulttree = to the new tree ID you entered in the SQL query.

At this point, when TNG is restarted, it should connect to the new database with the new tree name. To verify the new tree name is in use, search for an individual and go to their page. In the browser URL window you should see &tree= and the new tree name. Then go to their family group sheet and verify the tree id for the family as well. Media links to anyone in this tree will be retained as well. If you have any problems you can always reconnect TNG to the old database.
Created 9/2/19
You do not have the required permissions to view the files attached to this post.
Last edited by Steve on Thu Apr 23, 2020 8:57 am, edited 1 time in total.
Reason: Added mygedcom query for users