[問題]Recent Topics 檢視近日發表的文章出現資料庫錯誤

[一平]

下載： http://www.phpbbhacks.com/viewhack.php?id=1369

Appserv2.45

這個 Recent Topics 第三版之前使用相安無事，最近把Appserv升級到2.45最新版本，內建mysql5，一切正常但是就只有Recent Topics出問題，訊息如下，不知道有人有跟我一樣的情形嗎，要怎麼解決才好？

could not obtain main information.

DEBUG MODE

SQL Error : 1054 Unknown column 'p2.post_username' in 'field list'

SELECT t.*, p.poster_id, p.post_username AS last_poster_name, p.post_id, p.post_time, f.forum_name, f.forum_id, u.username AS last_poster, u.user_id AS last_poster_id, u2.username AS first_poster, u2.user_id AS first_poster_id, p2.post_username AS first_poster_name FROM phpbb_topics t, phpbb_posts p LEFT OUTER JOIN phpbb_forums f ON p.forum_id = f.forum_id LEFT OUTER JOIN phpbb_users u ON p.poster_id = u.user_id LEFT OUTER JOIN phpbb_users u2 ON u2.user_id = t.topic_poster WHERE t.forum_id NOT IN ('start') AND p.post_id = t.topic_last_post_id AND FROM_UNIXTIME(p.post_time,'%Y%m%d') - FROM_UNIXTIME(unix_timestamp(NOW()),'%Y%m%d') = 0 ORDER BY t.topic_last_post_id DESC LIMIT 0, 10

Line : 127
File : recent.php

參考網址 http://npb.club.tw/recent.php

[~倉木麻衣~]

因為少了這一句

LEFT OUTER JOIN ". POSTS_TABLE ." p2 ON p2.post_id = t.topic_first_post_id

所以系統不知道p2是從那裡蹦出來的
建議你用原始的recent.php重新覆蓋過

[一平]

蓋過以後錯誤訊息變成如下：

could not obtain main information.

DEBUG MODE

SQL Error : 1054 Unknown column 't.topic_first_post_id' in 'on clause'

SELECT t.*, p.poster_id, p.post_username AS last_poster_name, p.post_id, p.post_time, f.forum_name, f.forum_id, u.username AS last_poster, u.user_id AS last_poster_id, u2.username AS first_poster, u2.user_id AS first_poster_id, p2.post_username AS first_poster_name FROM phpbb_topics t, phpbb_posts p LEFT OUTER JOIN phpbb_posts p2 ON p2.post_id = t.topic_first_post_id LEFT OUTER JOIN phpbb_forums f ON p.forum_id = f.forum_id LEFT OUTER JOIN phpbb_users u ON p.poster_id = u.user_id LEFT OUTER JOIN phpbb_users u2 ON u2.user_id = t.topic_poster WHERE t.forum_id NOT IN ('start') AND p.post_id = t.topic_last_post_id AND FROM_UNIXTIME(p.post_time,'%Y%m%d') - FROM_UNIXTIME(unix_timestamp(NOW()),'%Y%m%d') = 0 ORDER BY t.topic_last_post_id DESC LIMIT 0, 10

Line : 127
File : recent.php

似乎是換湯不換藥的問題，不知道要怎麼解決？或是可以推薦一個類似的外掛讓我安裝嗎？謝謝。

[~倉木麻衣~]

個人是覺得, 先到phpMyAdmin裡確認一下phpbb_topics裡有topic_first_post_id欄位
不過這個欄位是內建的, 不可能會沒有
所以我把你上頭的sql語法複製到我的phpMyAdmin裡去執行(MySQL 4.1.18), 結果是可以順利執行, 沒有任何錯誤
因此個人猜測, 這可能是MySQL版本的問題

[~倉木麻衣~]

剛在VMWare裡安裝Appser 2.4.5版來做測試底下的SQL

代碼: 選擇全部

SELECT t. * , p.poster_id, p.post_username AS last_poster_name, p.post_id, p.post_time, f.forum_name, f.forum_id, u.username AS last_poster, u.user_id AS last_poster_id, u2.username AS first_poster, u2.user_id AS first_poster_id, p2.post_username AS first_poster_name
FROM phpbb_topics t, phpbb_posts p
LEFT OUTER JOIN phpbb_posts p2 ON p2.post_id = t.topic_first_post_id
LEFT OUTER JOIN phpbb_forums f ON p.forum_id = f.forum_id
LEFT OUTER JOIN phpbb_users u ON p.poster_id = u.user_id
LEFT OUTER JOIN phpbb_users u2 ON u2.user_id = t.topic_poster
WHERE t.forum_id NOT 
IN ( 65, 63, 85, 86 ) AND p.post_id = t.topic_last_post_id AND FROM_UNIXTIME( p.post_time, '%Y%m%d' ) - FROM_UNIXTIME( unix_timestamp( NOW( ) ) , '%Y%m%d' ) =0
ORDER BY t.topic_last_post_id DESC 
LIMIT 0 , 10

結果就如一平所講的, 會出現這個錯誤訊息
SQL Error : 1054 Unknown column 't.topic_first_post_id' in 'on clause'

後來去MySQL官方查了一下5.0版的join語法, 發現在FROM後若有接二個以上的資料表, 大部份都有用()括號括起來
於是我就將上述的SQL改成

SELECT t. * , p.poster_id, p.post_username AS last_poster_name, p.post_id, p.post_time, f.forum_name, f.forum_id, u.username AS last_poster, u.user_id AS last_poster_id, u2.username AS first_poster, u2.user_id AS first_poster_id, p2.post_username AS first_poster_name
FROM (phpbb_topics t, phpbb_posts p)
LEFT OUTER JOIN phpbb_posts p2 ON p2.post_id = t.topic_first_post_id
LEFT OUTER JOIN phpbb_forums f ON p.forum_id = f.forum_id
LEFT OUTER JOIN phpbb_users u ON p.poster_id = u.user_id
LEFT OUTER JOIN phpbb_users u2 ON u2.user_id = t.topic_poster
WHERE t.forum_id NOT
IN ( 65, 63, 85, 86 ) AND p.post_id = t.topic_last_post_id AND FROM_UNIXTIME( p.post_time, '%Y%m%d' ) - FROM_UNIXTIME( unix_timestamp( NOW( ) ) , '%Y%m%d' ) =0
ORDER BY t.topic_last_post_id DESC
LIMIT 0 , 10

結果就正常了

建議您將recent.php裡的這句

代碼: 選擇全部

FROM ". TOPICS_TABLE ." t, ". POSTS_TABLE ." p

改成這樣再試看看

代碼: 選擇全部

FROM (". TOPICS_TABLE ." t, ". POSTS_TABLE ." p)

[一平]

真強者！恩同再造！問題已經解決，也許倉木兄可以通知該外掛原作者有這個問題，讓全世界的愛用者都能及時修改QQ