Need help fixing broken relational links in MySQL database after an upgrade

I have recently upgraded a forum database but the posts are not linking to the users. Looking at the tables, I can see the following (example with all tables shortened for brevity):

jos_posts:

 Poster = Guest  (which is incorrect)
 Poster_ID = 0   (which is incorrect)
 Topic_ID = 10

jos_Topics:

 id = 10
 Poster = Ian

jos_users:

 Poster_ID = 2153
 Username = Ian

So although the poster_ID is incorrectly showing 0 in jos_posts, the relational link to the Topic_ID means that the real Poster and, subsequently the Poster_ID from the jos_users table, can be found and, hopefully, the jos_posts table updated.

It seems to me that I would need to carry out the following steps:

1. look at each post in jos_posts, save the Topic_ID to a variable,
2. Go to the jos_topics table and search for the id, and save the Poster to a variable,
3. Go to jos_users, and serach for the Poster and save the Poster_ID to a variable,
4. Go back to the jos_posts and update the Poster and Poster_ID with the variables collected.
5. Finally skip down a row, and loop the above until there are no more records in the jos_posts table.

Now to my question: My php/SQL coding is very poor, so can anyone confirm my logic above is correct and, if possible, point me in the right direction to开发者_开发百科 write a short program that can correct the jos_posts table?

---

No need for looping, it's just two simple SQL statements:

update jos_posts set poster_id = (
  select u.poster_id from jos_topics t, jos_users u 
  where t.id = topic_id and t.poster = u.username);

With a subsequent update of the poster column, everything should be fine:

update jos_posts set poster = (
 select u.username from jos_users u where u.poster_id = poster_id);

---

It would probably do you better to just grab all the records in jos_posts and iterate through them, performing the other operations you listed.

$posts = mysql_query("select * from jos_posts");
if($posts){
  while($post = mysql_fetch_array($posts)){
     $topic = $post['Topic_ID'];
     /* Look up the topic and get poster */
     /* Look up the user and get the id */
     $sql = "UPDATE jos_posts SET Poster = '".$poster
           ."', Poster_ID = '."$posterid"
           .' WHERE Topic_ID = '".$topic."'";
     mysql_query($sql);
  }
}

Something like that might work out...adding logic as necessary.

---

Use this query to select all users and their ids.

SELECT jos_Topics.Topic_ID, jos_Topics.Poster, jos_users.Poster_ID FROM jos_Topics INNER JOIN jos_users ON jos_Topics.Poster=jos_users.Username

Update jos_posts Poster_ID using the previous query.

However, I'm pretty sure your problem is that you have the data in two places to begin with. The whole point of relational databases is to eliminate redundancies.

---

Not completely off topic, I hope:

Ian surely did a good job to describe his problem. However, with a little bit of further work, the description could be augmented with a script that makes the problem testable:

use test;

DROP TABLE IF EXISTS jos_posts;

CREATE TABLE jos_posts (
    Id INTEGER PRIMARY KEY
  , Poster VARCHAR( 20 )
  , Poster_ID INTEGER
  , Topic_ID INTEGER
);

INSERT INTO jos_posts VALUES (1, 'GUEST', 0, 10);
INSERT INTO jos_posts VALUES (2, 'GUEST', 0, 20);

DROP TABLE IF EXISTS jos_topics;

CREATE TABLE jos_topics (
    Id INTEGER PRIMARY KEY
  , Poster VARCHAR( 20 )
);

INSERT INTO jos_topics VALUES (10, 'JAN');
INSERT INTO jos_topics VALUES (20, 'JON');

DROP TABLE IF EXISTS jos_users;

CREATE TABLE jos_users (
    Poster_ID INTEGER PRIMARY KEY
  , Username VARCHAR( 20 )
);

INSERT INTO jos_users VALUES (2153, 'JAN');
INSERT INTO jos_users VALUES (2154, 'JON');

SELECT * FROM jos_posts;

-- please insert solution here    

SELECT * FROM jos_posts;

Then a guru like Frank Schmitt could post his solution immediately and a nitpicker like me wouldn't need hours to get evidence for the objection that

update jos_posts set poster = (
  select u.username from jos_users u where u.poster_id = poster_id
);

should be:

update jos_posts p set poster = (
  select u.username from jos_users u where u.poster_id = p.poster_id
);