[RESOLVED] Unknown column in 'where clause'

[Rayman]

Hi everyone,

I am getting this error (quoted below) when executing the query in PHP - however when I copy/paste the query into phpmyadmin, it executes and finds the data accordingly.

I have tried to also use provider_key in a for-loop ($row['provider_key']) and it would return an error (undefined index 'provider_key') as well. But if I use {$row.provider_key} in a template, it returns the data fine.

I have spent around 4 hours now fighting with this, it's really strange and perhaps its a limitation with xenforo - I would really appreciate some help here

Server Error in Xenforo

Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: Unknown column 'provider_key' in 'where clause' - library/Zend/Db/Statement/Mysqli.php:77

Stack Trace (I have replaced certain words with hidden for privacy)

#0 /home/hidden/public_html/library/Zend/Db/Statement.php(115): Zend_Db_Statement_Mysqli->_prepare('?????SELECT????...')
#1 /home/hidden/public_html/library/Zend/Db/Adapter/Mysqli.php(381): Zend_Db_Statement->__construct(Object(Zend_Db_Adapter_Mysqli), '?????SELECT????...')
#2 /home/hidden/public_html/library/Zend/Db/Adapter/Abstract.php(478): Zend_Db_Adapter_Mysqli->prepare('?????SELECT????...')
#3 /home/hidden/public_html/library/Zend/Db/Adapter/Abstract.php(734): Zend_Db_Adapter_Abstract->query('?????SELECT????...', Array)
#4 /home/hidden/public_html/library/hidden/Roster/ControllerPublic/Roster.php(145): Zend_Db_Adapter_Abstract->fetchAll('?????SELECT????...')
#5 /home/hidden/public_html/library/XenForo/FrontController.php(347): hidden_Roster_ControllerPublic_Roster->actionSearch()
#6 /home/hidden/public_html/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#7 /home/hidden/public_html/index.php(13): XenForo_FrontController->run()
#8 {main}

Query Run via my Roster addon

$row = $db->fetchAll('SELECT
user.user_id,user.username,user.user_group_id,user.secondary_group_ids,user.register_date,user.last_activity,user.gender,user.message_count,user.like_count,user.trophy_points,division.field_value,steam.provider,steam.provider_key
FROM `xf_user` AS user
INNER JOIN `xf_user_external_auth` AS `steam` ON (steam.user_id = user.user_id)
INNER JOIN `xf_user_field_value` AS `division` ON (division.user_id = user.user_id)
WHERE
`provider`="steam"
AND `field_id`="division"
AND `provider_key`="76561198047582366"
AND (`user_group_id`="10" OR `user_group_id`="63" OR `user_group_id`="11")
GROUP BY user.user_id');

Result in phpmyadmin (copy/paste the query above into phpymadmin)

To avoid any confusion, the fields 'provider' and 'provider_key' are shown in HEX in phpmyadmin, hence why it won't match visually, but the data does match.
​

File Heatlth Check

"All 1,349 checked files are present and correct."
​

 

[Rayman]

Well that was stupid. I had two of the same query running, but the other one didn't inner join with the table. I hadn't realized

Woops.

~ Resolved.