sbroways sbroways - 1 year ago 83
MySQL Question

MySQL query help moving data between tables

I've imported by phpbb3 forum in bbpress using the built-in importer. All of the anonymous users from bbpress who didn't have accounts, but were allowed to post are disconnected from there posts and everything is showing up as anonymous in bbpress. I grabbed all the

and created users with this query:

INSERT INTO wp_users (user_login)
SELECT DISTINCT post_username
FROM phpbb_posts

Now I'm trying to do a query between the 3 different tables. Something along these lines:

INSERT INTO wp_posts(post_author)
WHERE wp_posts(post_date) = phpbb_posts(post_time)
AND phpbb_posts(post_username) = wp_users(user_login)

Obviously this isn't right... probably syntax errors, but I also need to add some way of telling MySQL that the
has to be attached to the
from the first line. Hopefully this makes sense. Thanks in advance for any help!

Updated queries:


SELECT post_time FROM phpbb_posts = post_date
SELECT post_username FROM phpbb_posts = user_login

hopefully this syntax makes more sense. These did work and they select the right information. The problem is I don't know how to write the WHERE statement properly and like you said baskint, I think I need to make the last statement a sub-query somehow. Thanks again!

Answer Source

I am still not sure what are the PK's (Primary Key) and FK's (Foreign Key) relationships of each table. However, assuming that wp_users is the primary table and phpbb_posts.post_username is the FK of wp_users.user_login...:

SELECT `wp_users`.`ID` 
FROM `wp_users` INNER JOIN
(SELECT `phpbb_posts`.`post_username` FROM `phpbb_posts`, `wp_posts` WHERE `phpbb_posts`.`post_time` = `wp_posts`.`post_date` ) AS `posts`
ON `wp_users`.`user_login` = `posts`.`post_username`;

EDIT (Dec-05-2012): After chatting and going through specific, @sbroways had to change data-types on some fields and a few other modifications. In turn, the final query turned out to be:

SELECT wp_users.*, ws_posts.*
FROM wp_users INNER JOIN ws_posts
ON wp_users.user_login = ws_posts.user_login
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download