Solved

Export 2 tables into one in phpmyadmin

Posted on 2014-03-06
12
297 Views
Last Modified: 2014-03-09
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.
0
Comment
Question by:Dzynit
  • 6
  • 5
12 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
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
 
LVL 14

Author Comment

by:Dzynit
Comment Utility
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
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
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
 
LVL 14

Author Comment

by:Dzynit
Comment Utility
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
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
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
 
LVL 14

Author Comment

by:Dzynit
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
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
 
LVL 14

Author Comment

by:Dzynit
Comment Utility
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
 
LVL 34

Accepted Solution

by:
Dan Craciun earned 500 total points
Comment Utility
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
 
LVL 14

Author Comment

by:Dzynit
Comment Utility
Perfect!! Thank you so much for all the time you put into this - I really appreciate it :)
0
 
LVL 14

Author Closing Comment

by:Dzynit
Comment Utility
Great work!!
0
 
LVL 34

Expert Comment

by:Dan Craciun
Comment Utility
Glad I could help!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The purpose of this video is to demonstrate how to make a WordPress Site faster and smaller in size by cleaning up the database. This will be demonstrated using a Windows 8 PC. Plugin WP Optimize will be used. Go to your WordPress login page. T…
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now