Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    4

    Unanswered: mysql commands help

    hello
    i have a forum and i mess it up with the aql commands so i will try and explain a little more
    i have a php website and 72 sql tables
    the 2 tables i want to compine are called phpbb_topics and phpbb_posts
    the phpbb_topics have the following fields
    topic_id
    forum_id
    icon_id
    topic_title
    topic_poster

    and the phpbb_post the following
    post_id
    topic_id
    forum_id
    icon_id
    poster_id
    post_subject
    post_title

    topic_id,forum_id,icon_id have the same values row,type etc
    the topic_poster and poster_id had the same as the other table but when i did something wrong the poster_id had nothing us a value
    but because the topic_poster keep having the records from the poster id i want to copy those value and put them in the poster_id

    so i am looking of an sql command to take the value the topic poster value and copy them in the place of the poster_id
    i can try and make it one by one but will take ages as there are more than 10000 pages with 30 rows each
    basically i am looking something like this
    take from phpbb_topic where topic_id and forum_id=phpbb_posts topic_id and forum_id take the topic_poster from phpbb_topics and put it in the phpbb_post in where it is the poster_id

    i hope this will make more sence

  2. #2
    Join Date
    Oct 2011
    Posts
    4
    anyone pleaseeeeeee

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I have a question, what is the type of topic_poster and poster_id? The fields appear to have a consistent naming convention and I think topic_poster sounds like it is a VARCHAR and poster_id is an INT. If that is the case you will not be able to perform the UPDATE. If they are the same type then you need to issue the following command:

    UPDATE phpbb_post, phpbb_topics
    SET phpbb_post.poster_id = phpbb_topics.topic_poster
    WHERE phpbb_post.topic_id = phpbb_topics.topic_id
    AND phpbb_post.forum_id = phpbb_topics.forum_id;

    Be sure to take a backup of your tables before manipulating your data or you may end up having an even bigger mess. With the backup you can at least revert back to a consistent state.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Oct 2011
    Posts
    4
    hello
    i check the files
    topic poster is mediumint type and have Length/Values 8
    poster id is mediumint type and have Length/Values 8
    so i dont see any difference between the 2 of them
    i will try and run the command and let you know about the results
    thanks

  5. #5
    Join Date
    Oct 2011
    Posts
    4
    Quote Originally Posted by it-iss.com View Post
    I have a question, what is the type of topic_poster and poster_id? The fields appear to have a consistent naming convention and I think topic_poster sounds like it is a VARCHAR and poster_id is an INT. If that is the case you will not be able to perform the UPDATE. If they are the same type then you need to issue the following command:

    UPDATE phpbb_post, phpbb_topics
    SET phpbb_post.poster_id = phpbb_topics.topic_poster
    WHERE phpbb_post.topic_id = phpbb_topics.topic_id
    AND phpbb_post.forum_id = phpbb_topics.forum_id;

    Be sure to take a backup of your tables before manipulating your data or you may end up having an even bigger mess. With the backup you can at least revert back to a consistent state.
    you are a life saver it-iss
    although the most reply come as a posts of the the first poster its better than nothing
    thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •