Changing an array of IDs in the usermeta table to fit different format

Question

A site I'm developing used WP Post Favorites to store post IDs in the usermeta table. I'm now trying to port this data over to a new site (the users and usermeta tables have been migrated over), but using a different plugin (Favorites). The new site has the same content but the post IDs have changed for various reasons that we can't control.

What I'm attempting to do is write a script that:

1. Replaces the old post IDs with the new post IDs from the new site.in the 'wpfp_favorites' usermeta key for each user. I have the corresponding old and new post IDs in a csv file.
2. Change the usermeta data from the format of the old plugin ('wpfp_favorites' meta key: a:3:{i:0;s:4:"4615";i:1;s:4:"2747";i:2;s:4:"9635";} to the format of the new plugin ('simplefavorites' meta key: a:1:{i:0;a:3:{s:7:"site_id";i:1;s:5:"posts";a:1:{i:0;i:9075;}s:6:"groups";a:1:{i:0;a:4{s:8:"group_id";i:1;s:7:"site_id";i:1;s:10:"group_name";s:12:"Default List";s:5:"posts";a:1:{i:0;i:9075;}}}}}

Notes:

• All of the post IDs are 4 digits long so the serialization shouldn't be too big a deal
• The way I see it we would probably need to run a one-time SQL query, or maybe a WP action that only runs once for each user on their next login
• Technically replacing all post IDs on the new site to match the old site would work, but this seems like it would be more complicated given the postmeta table, etc
• Would need to account for users who don't have favorites, etc
• This is a large website with over 100k users

Any guidance on building this type of query?

Answer 1

To do this reliably, you're going to have to fetch each user and pull in the meta then write it out again 1 by 1.

Use a custom WP CLI command to do this. If you don't have SSH access you can pull down a copy of the site and run it on your machine locally then push the changes back up