Solved

Export 2 tables into one in phpmyadmin

Posted on 2014-03-06
12
305 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
ID: 39910559
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
ID: 39912781
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
ID: 39914903
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 14

Author Comment

by:Dzynit
ID: 39915982
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
ID: 39916048
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
ID: 39916069
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
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39916079
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
ID: 39916100
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
ID: 39916103
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
ID: 39916240
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
ID: 39916241
Great work!!
0
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39916264
Glad I could help!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

828 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