URGENT: A HUGE line of error!

[nirmah]

I checked the FAQ, but it doesn't seem to have the answer to this.

When I go to showthread.php file, I get this error.


Database error in vBulletin 3.0.7:

Invalid SQL:
SELECT
post.*, post.username AS postusername, post.ipaddress AS ip,
user.*, userfield.*, usertextfield.*,
userfield.field AS rpgname,
user.ma AS uma,user.hp AS uhp,user.pp AS upp,user.ap AS uap,
rpg_items_user.*,
rpg_battle_stats.*,
rpg_race.name AS rpgracename,
rpg_race.alignment AS rpgalignmentname,
rpg_class.name AS classname,
rpg_class.folder AS classfolder,
rpg_element.name AS elename,
rpg_clan.title AS clantitle,

icon.title as icontitle, icon.iconpath,
avatar.avatarpath, NOT ISNULL(customavatar.avatardata) AS hascustomavatar, customavatar.dateline AS avatardateline,
level,
NOT ISNULL(deletionlog.primaryid) AS isdeleted, deletionlog.userid AS del_userid, deletionlog.username AS del_username, deletionlog.reason AS del_reason,
editlog.userid AS edit_userid, editlog.username AS edit_username, editlog.dateline AS edit_dateline,
editlog.reason AS edit_reason,
post_parsed.pagetext_html, post_parsed.hasimages,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid

FROM post AS post
LEFT JOIN user AS user ON(user.userid = post.userid)
LEFT JOIN userfield AS userfield ON(userfield.userid = user.userid)
LEFT JOIN usertextfield AS usertextfield ON(usertextfield.userid = user.userid)
LEFT JOIN icon AS icon ON(icon.iconid = post.iconid)
LEFT JOIN avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN customavatar AS customavatar ON(customavatar.userid = user.userid) LEFT JOIN reputationlevel AS reputationlevel ON(user.reputationlevelid = reputationlevel.reputationlevelid)
LEFT JOIN deletionlog AS deletionlog ON(deletionlog.primaryid = post.postid AND deletionlog.type = 'post')
LEFT JOIN editlog AS editlog ON(editlog.postid = post.postid)
LEFT JOIN post_parsed AS post_parsed ON(post_parsed.postid = post.postid)
LEFT JOIN rpg_items_user AS rpg_items_user ON(rpg_items_user.userid=user.userid)
LEFT JOIN rpg_battle_stats AS rpg_battle_stats ON(rpg_battle_stats.userid=user.userid)
LEFT JOIN rpg_class AS rpg_class ON(rpg_class.id=user.rpgclass)
LEFT JOIN rpg_element AS rpg_element ON(rpg_element.elementid=user.element)
LEFT JOIN rpg_clan AS rpg_clan ON(rpg_clan.clanid=user.clan)
LEFT JOIN rpg_race AS rpg_race ON(rpg_race.raceid=user.rpgrace)
AND rpg_race.alignmentid=user.alignment

WHERE post.postid IN (0,1)
ORDER BY dateline

mysql error: Unknown column 'userfield.field' in 'field list'

mysql error number: 1054


I've been trying to figure out the problem myself, but just can't notice it. Do you?


Thank you,
- Static

[tamarian]

userfield.field AS rpgname,

Something went wrong installing an RPG hack? :)

I don't have any experience with RPG, but seems like you needed to define a custom user field for it, then specify which filed it was. Here it seems like the field has no number, while it should be something like field7 or field 20, depending on which field you made for it.

[nirmah]

Thanks for pointing it out tamarian! :)

I just noticed that the RPG Profile field was 5 and the RPG Name field had no number, which I think confused the system. Changed it to field5 and its working great now! :)

Thanks!


- Static

P.S. Before, I go, can I ask where I can find how to add another navigation bar below the current one so I can add the RPG links?

Thanks :)

Oops. I guess I shouted way too fast!

Alright, so now the showthread.php works, but even when I changed the field to field5, the showpost.php displays this error.

Invalid SQL:
SELECT
post.*, post.username AS postusername, post.ipaddress AS ip,
user.*, userfield.*, usertextfield.*,
userfield.field5 AS rpgname,
user.ma AS uma,user.hp AS uhp,user.pp AS upp,user.ap AS uap,
rpg_items_user.*,
rpg_battle_stats.*,
rpg_race.name AS rpgracename,
rpg_race.alignment AS rpgalignmentname,
rpg_class.name AS classname,
rpg_class.folder AS classfolder,
rpg_element.name AS elename,
rpg_clan.title AS clantitle,

icon.title as icontitle, icon.iconpath,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
,avatar.avatarpath, NOT ISNULL(customavatar.avatardata) AS hascustomavatar, customavatar.dateline AS avatardateline
,level,
NOT ISNULL(deletionlog.primaryid) AS isdeleted,
post_parsed.pagetext_html, post_parsed.hasimages

FROM post AS post
LEFT JOIN user AS user ON(user.userid = post.userid)
LEFT JOIN userfield AS userfield ON(userfield.userid = user.userid)
LEFT JOIN rpg_items_user AS rpg_items_user ON(rpg_items_user.userid=user.userid)
LEFT JOIN rpg_battle_stats AS rpg_battle_stats ON(rpg_battle_stats.userid=user.userid)
LEFT JOIN rpg_class AS rpg_class ON(rpg_class.id=user.rpgclass)
LEFT JOIN rpg_element AS rpg_element ON(rpg_element.elementid=user.element)
LEFT JOIN rpg_clan AS rpg_clan ON(rpg_clan.clanid=user.clan)
LEFT JOIN rpg_race AS rpg_race ON(rpg_race.raceid=user.rpgrace)
AND rpg_race.alignmentid=user.alignment

LEFT JOIN usertextfield AS usertextfield ON(usertextfield.userid = user.userid)
LEFT JOIN icon AS icon ON(icon.iconid = post.iconid)
LEFT JOIN avatar AS avatar ON(avatar.avatarid = user.avatarid) LEFT JOIN customavatar AS customavatar ON(customavatar.userid = user.userid) LEFT JOIN reputationlevel AS reputationlevel ON(user.reputationlevelid = reputationlevel.reputationlevelid)
LEFT JOIN deletionlog AS deletionlog ON(deletionlog.primaryid = post.postid AND type = 'post')
LEFT JOIN post_parsed AS post_parsed ON(post_parsed.postid = post.postid)
WHERE post.postid = 1

mysql error: Column 'type' in on clause is ambiguous

mysql error number: 1052

[EDIT] I saw the FAQ, but where do I delete the "AND type = 'post'" and also, I'm running the latest version right now, so shall I do the same fix of deleted the and type = post part? Also, I'm very noobish, so I'd have to ask, where do I go to delete this?

[Otikeu]

I have alot of mysql issues like this too, what could be causing this? I mean, in updating rpg status, showthread.php, etc.

[neolithic]

I have noticed that this error only occurs on my forum when someone who isnt' registered tries to browse the threads. I've changed the way the unregistered usergroup works so that they can't browse anything unless they log in, testing that to see if it will stop those errors from flooding through.

[nirmah]

Actually, that error comes up when I'm in my administrator ID. So I don't think your solution will be helpful here neolithic. Thanks anyways. :)


- Static

[tamarian]

Try changing this line in showpost:
LEFT JOIN deletionlog AS deletionlog ON(deletionlog.primaryid = post.postid AND type = 'post')
To this:
LEFT JOIN deletionlog AS deletionlog ON(deletionlog.primaryid = post.postid AND deletionlog.type = 'post')

[Revan]

Tamarians fix is correct.
And I figured it would be quite obvious that you edit the file corresponding to the file in which you are recieving the error...

[Hoffi]

Ah, I just got this Error. Thanks.

[Revan]

This should not happen in future versions, Im gonna make sure the field is set properly and working. Probably just a bug in the installer.

[Otikeu]

Try changing this line in showpost:
LEFT JOIN deletionlog AS deletionlog ON(deletionlog.primaryid = post.postid AND type = 'post')
To this:
LEFT JOIN deletionlog AS deletionlog ON(deletionlog.primaryid = post.postid AND deletionlog.type = 'post')

I can't find this line of code in showpost.php, is that the wrong file?

This is the only code the instructions told me to input into showpost.php


// rpg
$sqlplug1 = "userfield.$vboptions[rpg_namefield] AS rpgname,
user.ma AS uma,user.hp AS uhp,user.pp AS upp,user.ap AS uap,
rpg_items_user.*,
rpg_battle_stats.*,
rpg_race.name AS rpgracename,
rpg_race.alignment AS rpgalignmentname,
rpg_class.name AS classname,
rpg_class.folder AS classfolder,
rpg_element.name AS elename,
rpg_clan.title AS clantitle,
";
$sqlplug2 = "LEFT JOIN " . TABLE_PREFIX . "rpg_items_user AS rpg_items_user ON(rpg_items_user.userid=user.userid)
LEFT JOIN " . TABLE_PREFIX . "rpg_battle_stats AS rpg_battle_stats ON(rpg_battle_stats.userid=user.userid)
LEFT JOIN " . TABLE_PREFIX . "rpg_class AS rpg_class ON(rpg_class.id=user.rpgclass)
LEFT JOIN " . TABLE_PREFIX . "rpg_element AS rpg_element ON(rpg_element.elementid=user.element)
LEFT JOIN " . TABLE_PREFIX . "rpg_clan AS rpg_clan ON(rpg_clan.clanid=user.clan)
LEFT JOIN " . TABLE_PREFIX . "rpg_race AS rpg_race ON(rpg_race.raceid=user.rpgrace)
AND rpg_race.alignmentid=user.alignment
";
$totalcats = $DB_site->query_first("SELECT COUNT(*) AS countcats FROM " . TABLE_PREFIX . "rpg_items_cats");


of which I do not find

LEFT JOIN deletionlog AS deletionlog ON(deletionlog.primaryid = post.postid AND type = 'post')

inside of the code and outside. I am using the latest version since i just downloaded yesterday.

[Revan]

Yes... but you might want to try to search the actual query $posts ;)