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

[DRE]

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]

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]

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]

SELECT * FROM xf_user_field_value WHERE field_id = <field ID>

 

[DRE]

SELECT * FROM xf_user_field_value WHERE field_id = <field ID>

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?

 

[Paul B]

https://www.google.co.uk/search?q=#...rver&aqs=chrome.0.57&sourceid=chrome&ie=UTF-8

 

[DRE]

https://www.google.co.uk/search?q=#1045 Cannot log in to the MySQL server&aq=f&oq=#1045 Cannot log in to the MySQL server&aqs=chrome.0.57&sourceid=chrome&ie=UTF-8

Thanks! Glad to know it wasn't on my end. Was confused for a moment.

 

[Chris D]

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

 

[DRE]

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

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

 

[Chris D]

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]

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.

Was using my ftp login. *face-palm*

 

[DRE]

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

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

 

[MattW]

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

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

SELECT * FROM xf_user_field_value WHERE field_id = 'xboxlive'

 

[DRE]

SELECT * FROM xf_user_field_value WHERE field_id = 'xboxlive'

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

 

[MattW]

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

SELECT *
FROM  `xf_user_field_value`
WHERE  `field_id` =  'xbox_gametag'
AND  `field_value` !=  ''

 

[Chris D]

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]

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

 

[Jake Bunce]

http://xenforo.com/community/threads/costum-fields.22017/#post-277061

 

[Ludachris]

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:

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?

 

[Ludachris]

@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?