0

I have a request to migrate wordpress website to a custom CMS.

The problem is that this wordpress website has over 30 000 registered users, and user_meta table is very inconsistent because during the last 5 years they used different plugins for user management, so some users have 10 records in user_meta table, some have 50 records, and some have as much as 150 records (they stored different information about users during these 5 years).

I have to save all relevant data (for example: usernames, first_name, last_name, date_of_birth,...) about users in non key-attribute-value database table. So, I'm going to have regular tables for users (with specific columns like username, first_name, last_name,...) and i have to migrate specific records of data from user_meta to one or more related tables for new CMS. The user_meta table currently has couple of millions of records (relevant and non relevant).

How can I extract all required data for each user (for example: username, first_name, last_name, date_of_birth) from user_meta table, but i need them not in key-value format, but in regular format which i can import to column-based table (so i can easily import them)?

Thanks

I will add more details to this question in comments if needed

vuk_niko
  • 11
  • 6
  • Would exporting the database and then importing not work? – Isaac Aug 18 '18 at 09:14
  • The thing is user_meta has meta_key and meta_value columns, and i can export that, but in the new database for that custom CMS i don't have meta_key and meta_value, but keys from wordpress' table should act as column names in the new database table. So if meta_key is first_name, than that is a column of a new database table. And the point is i need to get rid of all unnecessary data (anmari bellow gave an example). Thanks for the response – vuk_niko Aug 18 '18 at 12:04

1 Answers1

0

Work which meta keys you need. Most of them you don't need since they are to do with wordpress settings like 'admin colour' etc.

Then you could do either something like this https://wordpress.stackexchange.com/questions/231003/how-to-get-list-of-all-users-and-their-metadata

OR a very large SQL query (maybe batched with limit & offset)

SELECT T.ID, M1.meta_value AS first_name, M2.meta_value AS last_name, M4.meta_value AS event 
FROM wp_users AS T 
LEFT OUTER JOIN  wp_usermeta AS M1 ON (T.ID=M1.user_id AND M1.meta_key = 'first_name') 
LEFT OUTER JOIN  wp_usermeta AS M2 ON (T.ID=M2.user_id AND M2.meta_key = 'last_name') 
LEFT OUTER JOIN  wp_usermeta AS M4 ON (T.ID=M4.user_id AND M4.meta_key = 'event')   
ORDER BY T.ID ASC

If there are any non-unique meta values that you need, it'll start getting trickier.

anmari
  • 3,830
  • 1
  • 15
  • 15
  • ok, this approach with large sql query might work very well because i can let it work for hours, time and disk space / memory are not a problem. I will try something based on your query on a local machine. If there aren't any better answers, and that query works as expected, I will mark your answer as a correct one. Thank you very much. Note: i tried to upvote your answer but i don't have good enough reputation :) Thank you very much for you answer – vuk_niko Aug 18 '18 at 12:10