Solved

Export 2 tables into one in phpmyadmin

Posted on 2014-03-06
12
300 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

WordPress is constantly evolving, and with each evolution appears to get better and better.  One of the big drawbacks prior to version 3 was that there was no way to be able to set up a custom menu from the backend. The Old Way Adding menus is…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
This video teaches users how to migrate an existing Wordpress website to a new domain.

816 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

9 Experts available now in Live!

Get 1:1 Help Now