Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Export 2 tables into one in phpmyadmin

Posted on 2014-03-06
12
Medium Priority
?
318 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 40

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 35

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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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 35

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 35

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 35

Accepted Solution

by:
Dan Craciun earned 2000 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 35

Expert Comment

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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
The purpose of this video is to demonstrate how to update a WordPress Site’s version. WordPress releases new versions of its software frequently and it is important to update frequently in order to keep your site secure, and to get new WordPress…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…
Suggested Courses

609 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