Can I use phpMyAdmin to export/import all content of specific TNG tables?

[JMM]

• TNG 15.0.2

Hello, folks, let me clarify...

On my previous TNG site (14.0.6), I have many pictures that have many hundreds of people linked to those pictures, spread across 8-10 categories.  I do have a copy (backup) of my previous TNG 14.0.6 site, which I ran a 14.0.6-to-15.0.2 upgrade on, so I do have a database from a previous site will all my categories & photos in it, and which the database is compatible withTNG 15.0.2.

I installed my TNG 15.0.2 site from scratch, and have been slowly setting up my media on the new site from scratch.  It's a slow process, and I've managed to create a few categories manually so far.

For you MySQL gurus, is there a way that I can somehow, from that previoussite's tng_media table, export or extract all media items with a specific mediatypeID to a file, and then import all those items into a category on my live TNG 15.0.2 site?

As an example, I have a media category on my previous site with a mediatypeID called maps.  I wish to import everything in that category into a new (empty) category on my current site with a mediaID that I will call photosmaps when I create it.

What I wish to accomplish is to have all the maps from my previous site with all their associated (Linked to) people, imported into my new site, with all the maps & their associated (Linked to) people.

And if the above is indeed possible, is it better for me to already have the new category reated on my current site to import the maps (photos) into?  Or is it better to not have the maps category created yet, and have the import create it during the import process?

Apologies for not explaining that very well, but I hope it makes a little sense.

Thank you in advance & have a great day.

Regards,
John

[Philip Roy]

Gosh I have to admit that you’ve taken some very complex approaches to establishing a new site in some of your posts.

The short answer is yes, if (as it sounds) both the old site and new site are running 15.0.2. I use phpMyAdmin for all my database activities, including backing up my site. If you google “how to export a table using phpMyAdmin” there are a wealth of links to help. The first one seemed great…

https://help.dreamhost.com/hc/en-us/articles/214395738-phpMyAdmin-How-to-backup-or-export-a-database-or-table

WARNING! Backup both entire database before trying this.

I do think you are possibly making it further complicated by wanting to change maps to photomaps as part of the process. This means changing all instances of maps to photomaps in the exported sql file before importing it into the database at the new site, but what immediately worries me is if that reference of maps for all those items are also referenced in other tables. It may mean you’re not exporting all the information you think you need…database tables often contain references to other tables. This could get messy!

 

[Philip Roy]

PS…as I said, I don’t and have never used it, but why not use the inbuilt table backup functionality in TNG? Backup the table and import them in the new site?

AGAIN…back everything up first.

 

[Ken Roy]

5 hours ago, JMM said:

I installed my TNG 15.0.2 site from scratch, and have been slowly setting up my media on the new site from scratch.  It's a slow process, and I've managed to create a few categories manually so far.

If you had a previous site at 14.0.6 you should have use the upgrade zip file to upgrade your site and avoid all these problems

[JMM]

3 hours ago, Ken Roy said:

If you had a previous site at 14.0.6 you should have use the upgrade zip file to upgrade your site and avoid all these problems

I know how to upgrade TNG.  I've been upgrading TNG for nearly 20 years.  Most times they were by using the upgrade method; other times they were clean installs for whatever reason (corruption, bloat, etc.).  This time, to 15.0.2, I decided to do a clean install (for whatever reason... I can't remember what that reason was).

[Philip Roy]

I think Ken and I have interpreted this response differently...

Quote

I do have a copy (backup) of my previous TNG 14.0.6 site, which I ran a 14.0.6-to-15.0.2 upgrade on, so I do have a database from a previous site will all my categories & photos in it, and which the database is compatible withTNG 15.0.2.

So you have a 15.0.2 version of your old site's database is how I read that? If so, my response from previous stands...you can export a table from the OLD database and IMPORT to the new, with issues over you wanting to rename the media types in the process...or use the back up feature in TNG between OLD and NEW sites.

Or am I confused?

[JMM]

Phillip, thanks for your suggestions...

5 hours ago, Philip Roy said:

Gosh I have to admit that you’ve taken some very complex approaches to establishing a new site in some of your posts.

What can I say?  I'm a retired old geezer, living alone, with nothing to do all day & all night except to spend 99% of my awake time fiddling with TNG .

5 hours ago, Philip Roy said:

I do think you are possibly making it further complicated by wanting to change maps to photomaps as part of the process.

Minor detail.  I will leave mediatypeID for both of them with the same name (maps), for simplicity.

5 hours ago, Philip Roy said:

... but what immediately worries me is if that reference of maps for all those items are also referenced in other tables. It may mean you’re not exporting all the information you think you need…database tables often contain references to other tables. This could get messy!

Noted.  I'll forget about using the export/import method, and just manually continue to add the media to my 15.0.2 site.  Thanks for the advise.

5 hours ago, Philip Roy said:

I don’t and have never used it, but why not use the inbuilt table backup functionality in TNG? Backup the table and import them in the new site?

AGAIN…back everything up first.

The thing is, I already have 199 media items already added to my 15.0.2 site, with 363 more to go, including the few media items with many hundreds of people linked to those media items .  If I were to backup those media things (media items, media links & media types) from my previous site & then restore those 3 into my new 15.0.2 site, I would be overwriting the 199 media items that I've already added to my 15.0.2 site.  So as mentioned, I will forget about using the export/import method, and just manually continue to add the media to my 15.0.2 site.  Thanks for the backup/restore advise, though.

Thanks for taking the time to reply, Phillip, and have yourself a great day.

Regards,
John

[JMM]

15 minutes ago, Philip Roy said:

So you have a 15.0.2 version of your old site's database is how I read that? If so, my response from previous stands...you can export a table from the OLD database and IMPORT to the new, with issues over you wanting to rename the media types in the process...or use the back up feature in TNG between OLD and NEW sites.

That is correct, and actually, on my first ttempt with 15.0.2, that is exactly what I did... export such tables as cemeteries, reports, and the 3 media ones from my previous site's 15.0.2 version of its database, and then imported those tables into 15.0.2.  The cemeteries & reports export/import was successful.  Well, the export/import of the 3 media ones were successful too, except I found that none of the media items were properly linked to anyone... or something like that, so I abandoned that export/import method for the media items, and reinstalled 15.0.2 again from scratch , exporting/importing this time only the cemeteries & reports tables this time.

22 minutes ago, Philip Roy said:

Or am I confused?

Nah... everything to do with & about me is confusing !!!  It's not you .

[Philip Roy]

That made me laugh.

You can import the contents of the table from the OLD site's mediatype into the table of the NEW site's mediatype AND retain all current entries. Just google how to import a table in phpMyAdmin without losing existing content. I've never done it myself. but if you take a backup first, you can give it a try.

I have a local copy of my site on my Mac and use MAMP (WAMP for PC users) to test out these things first.

[JMM]

6 minutes ago, Philip Roy said:

You can import the contents of the table from the OLD site's mediatype into the table of the NEW site's mediatype AND retain all current entries. Just google how to import a table in phpMyAdmin without losing existing content. I've never done it myself. but if you take a backup first, you can give it a try.

That's interesting... let me investigate that further].  Thanks for the info.

[JMM]

40 minutes ago, Philip Roy said:

You can import the contents of the table from the OLD site's mediatype into the table of the NEW site's mediatype AND retain all current entries. Just google how to import a table in phpMyAdmin without losing existing content.

Well, I Google'd it, but didn't find what I was looking for... everything that Google reported was about how to import tables using phpMyAdmin, but nothing about AND retain all current entries, or similar.  Then again, since the days of Google back in the 90s, when Google actually reported back to us what we were searching for, most of what Google reports back to us now in the past couple of decades is NOT what we asked Google to find for us.  Sad.  Pathetic.

[JMM]

BTW, this is what I did:

From my previous site's database, from the tng_media table, I selected ONLY the media items that I wanted to export, which are those items with a mediatypeID of maps, and saved the file as tng_media_14-0-6_maps02.sql (disregard the 14-0-6 in the filename... that database's structure was actually upgraded for 15.0.2).

I then tried to import that exported file into my new site's database, but couldn't find where or how to actually append the content only.  The process failed, telling me that table_media already exists in the database that I am trying to import into.

[Philip Roy]

I may have put you wrong on what I thought could be done.

What you are suggesting is NOT easy. But, the issue you are facing is that the exported sql file includes a command to create the table. I have highlighted it here and you need to delete it from the SQL file so that when you import to the new database, it doesn't attempt to create the table again.

You can try again after that but there is potential for this to wreck your database....so please make a backup.

[Philip Roy]

Oh, sorry....I was looking at the media types table. You were looking at media. The principle is the same though....BUT...

I think you are going to run into issues as every entry gets given a unique key in the database and there is possibly going to be a conflict between those unique keys when you do the import.

[JMM]

Noted.  Too risky for trying to save myself a few days of manually adding items.

It was a thought, that I didn't know if it was possible or not.  Now I know.

Thanks, and have a great day, Phillip.

Regards,
John