• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

SQL Query For Finding Custom User Fields That Have Been Filled By User

DRE

Well-known member
#1
Hey what's the SQL command for finding out all the results of a custom field that's been filled out and not both the filled out ones and empty ones?

I'm trying to find out who all have filled out their X-Box Live ID custom field because some buttmunch linked to their complete profile instead of putting their name which ended up breaking a gamertag mod I had previously installed a month ago.
 

Chris D

XenForo developer
Staff member
#2
If you run SELECT * FROM xf_user_field_value WHERE user_id = 123 AND field_id = <field ID>

That will show you the value entered in the field by user with ID 123
 

DRE

Well-known member
#3
If you run SELECT * FROM xf_user_field_value WHERE user_id = 123 AND field_id = <field ID>

That will show you the value entered in the field by user with ID 123
Is that for finding one user? I need to find all users who filled out that user field.
 

Chris D

XenForo developer
Staff member
#8
Usually incorrect password.

Make sure you're using our PHP My Admin link (the ValveTime.net one) and the username and password is whatever the database username and password is in your config.php file.

Thanks! Glad to know it wasn't on my end. Was confused for a moment.
It is on your end... it's usually an incorrect password. Type the correct username and password and it will let you in :D
 

DRE

Well-known member
#9
Usually incorrect password.

Make sure you're using our PHP My Admin link (the ValveTime.net one) and the username and password is whatever the database username and password is in your config.php file.


It is on your end... it's usually an incorrect password. Type the correct username and password and it will let you in :D
I did type the password out. No copying and pasting. I am using the user info login you gave me.
 

Chris D

XenForo developer
Staff member
#10
I didn't give you any user info login information for the database.

It's the database username and password from your config.php file that you need to enter.
 

DRE

Well-known member
#12
I tried this: SELECT * FROM xf_user_field_value WHERE field_id = xboxlive

got the error: #1054 - Unknown column 'xboxlive' in 'where clause'
 

Chris D

XenForo developer
Staff member
#16
In php my admin you can click the column headers to sort. I would guess if you sorted them in reverse order the blank ones should go to tbs bottom.

Edit: and that ninja query works!
 

DRE

Well-known member
#17
Okay I finally found the offending X-Box Live ID that broke my leaderboard.

It was Robbos. https://live.xbox.com/en-US/Profile?Gamertag=Robbo 89

As you can see, Robbo 89 is not a valid X-Box Live ID.

This was the error I had gotten that was preventing my Leaderboards from updating.

Server Error

Mysqli prepare error: Unknown column 'l.field_id' in 'where clause'
  1. Zend_Db_Statement_Mysqli->_prepare() in Zend/Db/Statement.php at line 115
  2. Zend_Db_Statement->__construct() in Zend/Db/Adapter/Mysqli.php at line 381
  3. Zend_Db_Adapter_Mysqli->prepare() in Zend/Db/Adapter/Abstract.php at line 478
  4. Zend_Db_Adapter_Abstract->query() in HDJuegos/XbxLiveLeaderBoards/CronEntry/GamerTag.php at line 185
  5. HDJuegos_XbxLiveLeaderBoards_CronEntry_GamerTag::runGamerTagUpdate()
  6. call_user_func() in XenForo/Model/Cron.php at line 356
  7. XenForo_Model_Cron->runEntry() in WhoHasReadAThread/Model/Cron.php at line 30
  8. WhoHasReadAThread_Model_Cron->runEntry() in XenForo/ControllerAdmin/Cron.php at line 204
  9. XenForo_ControllerAdmin_Cron->actionRun() in XenForo/FrontController.php at line 310
  10. XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 132
  11. XenForo_FrontController->run() in /home/user/public_html/admin.php at line 13
Reported the error here: http://xenforo.com/community/threads/hdjuegos-net-xbox-live-leaderboards.27733/page-4
 

Ludachris

Well-known member
#19
Code:
SELECT *
FROM  `xf_user_field_value`
WHERE  `field_id` =  'xbox_gametag'
AND  `field_value` !=  ''
Sorry to bring this one back but I wanted to ask another question about the query above - what if you wanted to check to see if multiple fields were not empty? Could you simply add:
Code:
SELECT *
FROM `xf_user_field_value`
WHERE `field_id` = 'xbox_gametag'
AND `field_value` != ''
WHERE `field_id` = 'xbox_gametag2'
AND `field_value` != ''
WHERE `field_id` = 'xbox_gametag3'
AND `field_value` != ''
That doesn't look right. But how would you effectively achieve what's being attempted with that code correctly?