I'm encountering a MySQL error indicating a missing column. However in phpMyAdmin the mentioned column is visibly present in the database

Question

I've successfully installed an older forum script, VBulletin 2, on my hosting environment with PHP4. While I acknowledge the potential security concerns associated with using PHP4, my intention is to revive one of my initial forums that I created.

Nevertheless, the forum functions smoothly, allowing me to perform all the necessary operations. However, I encounter errors when attempting to access the User Panel.

Database error in vBulletin 2.3.11:
Invalid SQL: SELECT privatemessage.*,touser.username AS tousername,fromuser.username AS fromusername,icon.title AS icontitle,icon.iconpath
FROM privatemessage,user AS touser,user AS fromuser
LEFT JOIN icon ON icon.iconid=privatemessage.iconid
WHERE privatemessage.userid='1'
AND folderid=0
AND touser.userid=privatemessage.touserid
AND fromuser.userid=privatemessage.fromuserid
AND messageread=0
ORDER BY dateline DESC

mysql error: Unknown column 'privatemessage.iconid' in 'on clause' mysql error number: 1054

I can post a new thread but when I answer to it I get this error:

Database error in vBulletin 2.3.11:
Invalid SQL: SELECT user.*, style.templatesetid
            FROM subscribethread,user,usergroup
                LEFT JOIN style ON (IF(user.styleid=0, 1, user.styleid)=style.styleid)
            WHERE subscribethread.threadid='1'
            AND subscribethread.userid=user.userid
            AND usergroup.usergroupid=user.usergroupid
            AND user.userid<>'1'
            AND user.usergroupid<>'3'
            AND usergroup.canview = 1
            AND user.lastactivity>'1703928997'

mysql error: Unknown column 'user.styleid' in 'on clause'
mysql error number: 1054

Upon inspecting through PhpMyAdmin, I verified that the columns do indeed exist. Despite this confirmation, I'm puzzled about the root cause of the issue. Can anyone provide assistance or insights?

I tried to recreate the column and also changing the order but nothing happened.

Answer 1

You combine comma-style JOIN and explicit JOIN. Comma-style JOIN have less priority then explicit JOIN. So your

FROM subscribethread,user,usergroup
LEFT JOIN style ON (IF(user.styleid=0, 1, user.styleid)=style.styleid)

is by fact

FROM subscribethread,user,
(
                          usergroup
LEFT JOIN style ON (IF(user.styleid=0, 1, user.styleid)=style.styleid)
)

and it is obvious that in the added parenthesis the reference to user table (which is outer for added parenthesis) is not legal.

The most simple fix - replace a comma with CROSS JOIN:

FROM subscribethread
CROSS JOIN user
CROSS JOIN usergroup
LEFT JOIN style ON (IF(user.styleid=0, 1, user.styleid)=style.styleid)