1. 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

Discussion in 'XenForo Questions and Support' started by DRE, Apr 5, 2013.

  1. DRE

    DRE Well-Known Member

    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.
     
  2. Chris D

    Chris D XenForo Developer Staff Member

    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
     
  3. DRE

    DRE Well-Known Member

    Is that for finding one user? I need to find all users who filled out that user field.
     
  4. Chris D

    Chris D XenForo Developer Staff Member

    SELECT * FROM xf_user_field_value WHERE field_id = <field ID> :)
     
  5. DRE

    DRE Well-Known Member

    Thank. Okay I tried to login to phpMyAdmin to find that dude's entry but it keeps saying #1045 Cannot log in to the MySQL server

    Is #1045 some error code?
     
  6. Brogan

    Brogan XenForo Moderator Staff Member

  7. DRE

    DRE Well-Known Member

  8. Chris D

    Chris D XenForo Developer Staff Member

    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
     
  9. DRE

    DRE Well-Known Member

    I did type the password out. No copying and pasting. I am using the user info login you gave me.
     
  10. Chris D

    Chris D XenForo Developer Staff Member

    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 likes this.
  11. DRE

    DRE Well-Known Member

    Was using my ftp login. *face-palm*
     
  12. DRE

    DRE Well-Known Member

    I tried this: SELECT * FROM xf_user_field_value WHERE field_id = xboxlive

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

    MattW Well-Known Member

    Code:
    SELECT * FROM xf_user_field_value WHERE field_id = 'xboxlive'
     
    DRE and Chris D like this.
  14. DRE

    DRE Well-Known Member

    Isn't there a way to organize it in such a way that you only show those who have actually filled out that field?
     
  15. MattW

    MattW Well-Known Member

    Code:
    SELECT *
    FROM  `xf_user_field_value`
    WHERE  `field_id` =  'xbox_gametag'
    AND  `field_value` !=  ''
     
    DRE and Chris D like this.
  16. Chris D

    Chris D XenForo Developer Staff Member

    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 and MattW like this.
  17. DRE

    DRE Well-Known Member

    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
     
  18. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

  19. Ludachris

    Ludachris Well-Known Member

    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?
     
  20. Ludachris

    Ludachris Well-Known Member

    @MattW or @Chris D - either of you know if the query I posted above is the correct way to achieve what I'm after? Is there a better, or a correct way to structure that query?
     

Share This Page