MySQL query help moving data between tables

Question

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!

OMG Yes. Thank you for your help. Here are screen shots of the 3 table structures. [link](http://sebastianbroways.com/wp-content/uploads/screenshots/wp_posts.jpg) [link](http://sebastianbroways.com/wp-content/uploads/screenshots/wp_users.jpg) [link](http://sebastianbroways.com/wp-content/uploads/screenshots/phpbb_posts.jpg) — sbroways, Dec 05 '12 at 04:05
I am assuming that `wp_users.ID` is the Foreign key to `wp_posts.ID` and `phpbb_post.post_id` ? — Omar, Dec 05 '12 at 04:36
If I understand Foreign key correctly... `wp_users.ID` is the Foreign key for `wp_posts.ID` but not linked up to `phpbb_posts.post_id` . The `phpbb_posts.post_id` did not come into `wp_posts` with the importer so I don't think it's synced up to anything. Thanks! — sbroways, Dec 05 '12 at 04:57
No, it has it's own IDs. They begin at 29. The IDs in wp_posts begin at 1, 2, 3 and then jumps to 14k... — sbroways, Dec 05 '12 at 05:07
The last post id in phpbb_posts is 96589 and in wp_posts it's 150767. Maybe I should run a query that syncs the IDs first? It could go according to post date and then just override the id's in one table or the other? — sbroways, Dec 05 '12 at 05:10

score 1 · Accepted Answer · edited May 23 '17 at 10:34

1

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

edited May 23 '17 at 10:34

Community

1
1

answered Dec 05 '12 at 04:51

Omar

11,783
21
84
114

Trying this now. There are 75k posts and 13k users, so it's prob going to take a minute. Thanks for your help! – sbroways Dec 05 '12 at 05:16
@sbroways I'll have to edit my answer since the joins are of different data-types. But before that, Let's say `wp_users.ID` 'sbroways' has a post on `phpbb_posts` and `wp_posts`...For what I read, `phpbb_posts` will have the correct id (In this example, 'sbroways') located on `php_post.post_username`? And in order to locate the same post in `wp_posts` the match will be `phpbb_posts.post_time = wp_posts.post_date`? – Omar Dec 05 '12 at 05:16
@sbroways I also noticed (On your screen shot of your table structures) that `wp_users.ID` and `wp_posts.post_author` share the same data-type values. Which in turn leads me to believe that `wp_posts.post_author` is the actual FK of `wp_users`'s PK (`wp_users.ID`). Am I correct? Ej.: `SELECT wp_users.ID FROM wp_users, wp_posts WHERE wp_users.ID = wp_posts.post_author` – Omar Dec 05 '12 at 05:25
1

Thank you for all your help! I've been working on this for DAYS! The problem is that `wp_users.ID` is an integer. And `phpbb_posts` only has a string `post_username`. I've imported all of the `phpbb_posts.post_usernames` into `wp_users` so they now all have integer IDs: `wp_users.ID` and they all have the string as `wp_users.user_login`. `wp_posts` uses the ID from `wp_users` to attach the user name to each post. So I need to get all of those user IDs attached to the right post inside `wp_posts`. – sbroways Dec 05 '12 at 05:32
1

In order to do that, I think, I need to use the time stamp that match in `wp_posts` and `phpbb_posts` because the post IDs are all screwed up and don't match each other. So once I've matched the time stamp from `wp_posts.post_date` to the time stamp of `phpbb_posts.post_time` i then need to look in that record in `phpbb_posts` and find the `post_username` - take that username and match it up to the `wp_users.user_login` - grab the id from that record in `wp_users` and carry it all the way back to `wp_posts.post_author`. OMG I hope this makes sense. – sbroways Dec 05 '12 at 05:33
Yes, you are correct about the keys. `Post_author` is the ID from each `wp_user` record. – sbroways Dec 05 '12 at 05:34
@sbroways I think I am beginning to understand your problem. There are some things I need to clarify first. You said that you took the data from `phpbb_posts.post_username` to create `wp_users.user_login` right? -Ej: `SELECT post_username FROM phpbb_posts GROUP BY post_username` . – Omar Dec 05 '12 at 06:03
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/20603/discussion-between-omar-and-sbroways) – Omar Dec 05 '12 at 06:03
THANK YOU!! OMAR is awesome and spent hours helping with this problem. Life saver – sbroways Dec 05 '12 at 08:43

score 0 · Answer 2 · answered Dec 05 '12 at 03:48

0

you're right. your syntax is confusing and not correct. trying to understand what you are trying to accomplish. in second query, why are you selecting and inserting at the same time? perhaps i am missing something, but can you state what you are trying to pull out from which tables and how you would like to see the results in plain English?

Also you can think in terms of sub-queries (SELECT * FROM b WHERE id IS IN (SELECT Id from a). You can cascade this a few times and perhaps get to your answer.

answered Dec 05 '12 at 03:48

baskint

692
5
12

Yes, sorry to be confusing. In plain english: I'm trying to attach the user ids in wp_users to the appropriate posts in wp_posts and in order to do that I need to compare the time stamp on each post in wp_posts to the time stamps on each post in phpbb_posts because phpbb_posts have the correct user name attached to each posts. I then need to take that string from phpbb_posts and compare it to the user_login string from wp_users to make sure they match before I grab the ID and assign it to each post in wp_posts. I know it's confusing! I don't think it's as bad as it sounds, but hard to explai – sbroways Dec 05 '12 at 04:07

MySQL query help moving data between tables

2 Answers2