sbroways sbroways - 2 months ago 5
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

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!

Answer

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