Export 2 tables into one in phpmyadmin

I need to export the wp_user table from the database using the phpmyadmin, but I need to add the first and last name info from the wp_usermeta table.

For those not familiar with the wordpress tables:
the wp_user table has a column of ID assigned per row with user information, but the wp_usermeta table has all individual rows for additional user information. Those rows have a column of user_id that correspond to ID in the first table, but there's many rows for each user's ID. So there's two separate rows for first and last names instead of all on one row.

I only need to get the first and last name from the usermeta table and add to the user table, but can someone help me with the correct code to use in the sql tab in the phpmyadmin to export that information correctly?

What happened was that one of the admins deleted a whole bunch of subscribers from the wordpress admin area, but then realized they need an export of them for the newsletter mailing list system. They don't want to restore the database back and have to do that again, so I imported just those two tables into a separate database from a recent backup.

I'm just not that knowledgeable in sql commands and rarely need to work with it, so don't want to spend hours searching and trying to figure out and learn how to do this one time only.

Thanks for any help and let me know if you need any additional information.
LVL 14
DzynitAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
Why don't you create a SQL View from the two wp_user table add the first and last name info from the wp_usermeta table then use that SQL View as data source for your PHP code.


Can you post here just the structure(definition) of the two tables?
0
DzynitAuthor Commented:
Where is the SQL View? I'm not using php code, I just wanted to be able to use the sql tab in the phpmyadmin using sql commands/query.

Let me know if this helps with the the structure of the tables:

user_meta table -

INSERT INTO `wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES
(5368, 506, 'userphoto_thumb_file', '506.thumbnail.jpg'),
(5359, 506, 'skype', ''),
(5360, 506, 'facebook', ''),
(5361, 506, 'myspace', ''),
(5362, 506, 'twitter', ''),
(5363, 506, 'linkedin', ''),
(5364, 506, 'photos', 'a:0:{}'),
(5365, 506, 'userphoto_image_file', '506.jpg'),
(5366, 506, 'userphoto_image_width', '99'),
(5367, 506, 'userphoto_image_height', '150'),
(5357, 506, 'msn', ''),
(5337, 4, 'nav_menu_recently_edited', '201'),
(5338, 4, 'managenav-menuscolumnshidden', 'a:4:{i:0;s:11:\"link-target\";i:1;s:11:\"css-classes\";i:2;s:3:\"xfn\";i:3;s:11:\"description\";}'),
(5339, 4, 'metaboxhidden_nav-menus', 'a:2:{i:0;s:8:\"add-post\";i:1;s:12:\"add-post_tag\";}'),
(5340, 506, 'first_name', 'Dan'),
(5341, 506, 'last_name', 'Harris'),
(5342, 506, 'nickname', 'dandharris'),
(5343, 506, 'description', 'Talent Acquisition at Yahoo!'),
(5344, 506, 'rich_editing', 'true'),
(5345, 506, 'comment_shortcuts', 'false'),
(5346, 506, 'admin_color', 'fresh'),
(5347, 506, 'use_ssl', '0'),
(5348, 506, 'show_admin_bar_front', 'true'),
(5350, 506, 'aim', ''),
(5351, 506, 'yim', ''),
(5352, 506, 'jabber', ''),
(5353, 506, 'wp_capabilities', 'a:1:{s:10:\"subscriber\";s:1:\"1\";}'),
(5354, 506, 'wp_user_level', '0'),
(5356, 506, 'location', 'Sunnyvale, CA'),

user table -
INSERT INTO `wp_users` (`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name`) VALUES
(504, 'tomhalejr', 'encryptedpassword-removedforthispost', 'tomhalejr', 'tomhalejr@gmail.com', 'http://www.thomascreekconcepts.com/', '2011-07-31 22:57:57', '', 0, 'tomhalejr'),


The tables are large and also contain private info, so I just grabbed a piece from the beginning of each, but hopefully that gives you the idea. In the user table example, I know the person is user id 504 and most showing there in user_meta example is someone with user id 506.
0
Dan CraciunIT ConsultantCommented:
You can get the names from usermeta tables like this:
SELECT  `user_id` , CONCAT_WS(  ' ',  `meta_value` )  AS name
    FROM  `wp_usermeta` 
    WHERE  `meta_key` =  'first_name'
    OR  `meta_key` =  'last_name'
    GROUP BY  `user_id` 

Open in new window

so you can join this select and your wp_users table:
SELECT `ID`, `user_login`, `user_pass`, `user_nicename`, name, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name` FROM `wp_users` JOIN 
    (SELECT  `user_id` , CONCAT_WS(  ' ',  `meta_value` )  AS name
    FROM  `wp_usermeta` 
    WHERE  `meta_key` =  'first_name'
    OR  `meta_key` =  'last_name'
    GROUP BY  `user_id`) z

Open in new window


If the select works, just insert it into a new table.

HTH,
Dan
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

DzynitAuthor Commented:
Dan,
That almost works. It's looping through the entire usermeta table per user row though. What I'd like is each user row to just add that person's first and last name from the usermeta table.
Here's a small chunk of the output when I tried your join query above:

504       tomhalejr       password-replaced-for-privacy       tomhalejr       Kent      tomhalejr@gmail.com       http://www.thomascreekconcepts.com/       2011-07-31 22:57:57             0       tomhalejr
504       tomhalejr       password-replaced-for-privacy       tomhalejr       Benjamin      tomhalejr@gmail.com       http://www.thomascreekconcepts.com/       2011-07-31 22:57:57             0       tomhalejr
504       tomhalejr       password-replaced-for-privacy       tomhalejr       Scott      tomhalejr@gmail.com       http://www.thomascreekconcepts.com/       2011-07-31 22:57:57             0       tomhalejr
504       tomhalejr       password-replaced-for-privacy       tomhalejr       Todd      tomhalejr@gmail.com       http://www.thomascreekconcepts.com/       2011-07-31 22:57:57             0       tomhalejr
504       tomhalejr       password-replaced-for-privacy       tomhalejr       Heather      tomhalejr@gmail.com       http://www.thomascreekconcepts.com/       2011-07-31 22:57:57             0       tomhalejr
504       tomhalejr       password-replaced-for-privacy       tomhalejr       David      tomhalejr@gmail.com       http://www.thomascreekconcepts.com/       2011-07-31 22:57:57             0       tomhalejr
504       tomhalejr       password-replaced-for-privacy       tomhalejr       Scott      tomhalejr@gmail.com       http://www.thomascreekconcepts.com/       2011-07-31 22:57:57             0       tomhalejr
504       tomhalejr       password-replaced-for-privacy       tomhalejr       Tracy      tomhalejr@gmail.com       http://www.thomascreekconcepts.com/       2011-07-31 22:57:57             0       tomhalejr
504       tomhalejr       password-replaced-for-privacy       tomhalejr       Stanford      tomhalejr@gmail.com       http://www.thomascreekconcepts.com/       2011-07-31 22:57:57             0       tomhalejr
504       tomhalejr       password-replaced-for-privacy       tomhalejr       Tom      tomhalejr@gmail.com       http://www.thomascreekconcepts.com/       2011-07-31 22:57:57             0       tomhalejr
504       tomhalejr       password-replaced-for-privacy       tomhalejr       Stephanie      tomhalejr@gmail.com       http://www.thomascreekconcepts.com/
0
Dan CraciunIT ConsultantCommented:
I forgot the ON :)
SELECT `ID`, `user_login`, `user_pass`, `user_nicename`, name, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name` 
    FROM `wp_users` JOIN 
    (SELECT  `user_id` , CONCAT_WS(  ' ',  `meta_value` )  AS name
    FROM  `wp_usermeta` 
    WHERE  `meta_key` =  'first_name'
    OR  `meta_key` =  'last_name'
    GROUP BY  `user_id`) z
    ON `wp_users`.`ID`=z.`user_id`

Open in new window

0
DzynitAuthor Commented:
Almost perfect! It's getting the first name, but not the last name. If we can get that too, we'll be all set.
0
Dan CraciunIT ConsultantCommented:
It should merge the first and last name, or at least that's what I told MySQL to do.
CONCAT_WS(' ', first_name, last_name) should give you the full name.
If you want them separate, I'll have to modify the query.
0
DzynitAuthor Commented:
If you don't mind a little more time on this, could you try them separate? It seems that it "should" be combining them into the name column, but it's only filling the first name and not the last.
0
Dan CraciunIT ConsultantCommented:
OK, try this:
SELECT `ID`, `user_login`, `user_pass`, `user_nicename`, first_name, last_name, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name` 
    FROM `wp_users` JOIN (
    SELECT  `wp_usermeta`.`user_id` ,  `wp_usermeta`.`meta_value` AS first_name , x.`meta_value` AS last_name
        FROM  `wp_usermeta` 
        JOIN (
        SELECT  `user_id` ,  `meta_value` 
        FROM  `wp_usermeta` 
        WHERE  `meta_key` =  'last_name'
        )x ON  `wp_usermeta`.`user_id` = x.`user_id` 
        WHERE  `meta_key` =  'first_name'
        GROUP BY `wp_usermeta`.`user_id` ) z
    ON `wp_users`.`ID`=z.`user_id`

Open in new window


Tried it on a Wordpress install with 5 users and worked as expected.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DzynitAuthor Commented:
Perfect!! Thank you so much for all the time you put into this - I really appreciate it :)
0
DzynitAuthor Commented:
Great work!!
0
Dan CraciunIT ConsultantCommented:
Glad I could help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.