mysqlsqlforumphpbb3bbpress

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 post_usernames from phpbb_posts 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: 

SELECT ID FROM wp_users 
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 user_login has to be attached to the ID from the first line. Hopefully this makes sense. Thanks in advance for any help!

Updated queries: 

SELECT ID FROM wp_users

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!

Solution

  • 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