SQL query failing after changing hosts

Discussion in 'OT Technology' started by Dustro, Jan 20, 2006.

  1. Dustro

    Dustro New Member

    Joined:
    Nov 1, 2003
    Messages:
    2,123
    Likes Received:
    0
    Location:
    NC
    Im helping a friend move a phpbb site from his old host to dreamhost and ran into a problem when executing this query
    Code:
    SELECT u.username, u.user_id, u.user_posts, u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim, u.user_yim, u.user_regdate, u.user_msnm, u.user_viewemail, u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_avatar, u.user_avatar_type, u.user_allowavatar, u.user_allowsmile, p.*,  pt.post_text, pt.post_subject, pt.bbcode_uid, g.year, makes.make, models.model, g.id as garage_id
        FROM " . POSTS_TABLE . " p, " . USERS_TABLE . " u, " . POSTS_TEXT_TABLE . " pt
        LEFT JOIN " . GARAGE_TABLE ." AS g on g.member_id = p.poster_id and g.main_vehicle = 1
                    LEFT JOIN " . GARAGE_MAKES_TABLE . " AS makes ON g.make_id = makes.id
                    LEFT JOIN " . GARAGE_MODELS_TABLE . " AS models ON g.model_id = models.id
        WHERE p.topic_id = $topic_id
            $limit_posts_time
            AND pt.post_id = p.post_id
            AND u.user_id = p.poster_id
        ORDER BY p.post_time $post_time_order
        LIMIT $start, ".$board_config['posts_per_page'];
    I get the following error
    Code:
         [B]MySQL said: [/B][URL="http://dev.mysql.com/doc/mysql/en/Error-returns.html"][IMG]http://www.ks-minis.dreamhosters.com/dh_phpmyadmin/ksphpbb.ks-minis.dreamhosters.com/themes/original/img/b_help.png[/IMG][/URL] 
      #1054 - Unknown column 'p.poster_id' in 'on clause' 
    Now the query works fine on the old server but fails on this one, I cannot find where the issue would be for this and after trying my best im looking for some help :wtc:
     
    Last edited: Jan 20, 2006
  2. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    Are both versions of phpbb exactly the same?
     
  3. Dustro

    Dustro New Member

    Joined:
    Nov 1, 2003
    Messages:
    2,123
    Likes Received:
    0
    Location:
    NC
    yes everything is copied over, is is failing in the phpmyadmin query window too though so im sure the phpbb ver shouldnt matter.

    if i take out
    it works but the mod does not display

    the only thing that is different is the version of mySQL is newer on the new server (5.0.10 - 4.1.2)

    oops i had pasted the code after phpbb had processed it, I updated the post with the raw php query, it still worked only on the old server either way in the query window.
     
  4. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    I thought the table may have changed. I have no idea.
     
  5. Sabrewulf

    Sabrewulf New Member

    Joined:
    Jun 10, 2004
    Messages:
    1,463
    Likes Received:
    0
    Location:
    Hammond, LA
    Juedging by the error, the Database structure isnt the same. Did you happen to backup your old db structure and import it into your new website? B/c it says it cant find the p.poster_id column in the on clause table. Might wanna check the db structure and also chcek and make sure your code hasnt changed and it may be looking to a different location. :dunno:
     
  6. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    Yeah DESCRIBE them tables.
     
  7. CyberBullets

    CyberBullets I reach to the sky, and call out your name. If I c

    Joined:
    Nov 13, 2001
    Messages:
    11,865
    Likes Received:
    0
    Location:
    BC, Canada/Stockholm, Sweden
    did you use mysqldump to transfer the data?
     
  8. Dustro

    Dustro New Member

    Joined:
    Nov 1, 2003
    Messages:
    2,123
    Likes Received:
    0
    Location:
    NC
    well after racking my small brain I checked with the developer of the module that is having the issue and is an incompatibility with mySQL 5 that he is working on. Not worth my time to try to correct it too.

    the dump was done from phpmyadmin, i guess i could have done it from the command line but the db was full and complete.

    thanks for the suggestions
     

Share This Page