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.

 

[MattW]

Should those be AND or OR queries for the tags you want to check? So you want to get the results only if all 3 tag fields are empty?

 

[Ludachris]

Want to make sure none of those 3 fields are empty.

 

[MattW]

not tested

SELECT *
FROM `xf_user_field_value`
WHERE field_value` != ''
AND `field_id` IN (xbox_gametag, xbox_gametag2, xbox_gametag3)

 

[LaxmiSathy]

Hello,

Am looking for a sql query as below:
-Table : xf_user - register_date is say after a date so I write the query as:

SELECT  username, email, FROM_UNIXTIME(register_date)
FROM `xf_user`
WHERE FROM_UNIXTIME( register_date ) > '2016-03-20'
order by register_date asc

For all the resulting rows of users I would also want to get their custom profile field values from the table 'xf_user_field_value'
such that field_id IN ( 'city' , 'country' )

So the resulting output reads as :
username, email, city, country

Any help regarding the query?

 

[Jake Bunce]

Hello,

Am looking for a sql query as below:
-Table : xf_user - register_date is say after a date so I write the query as:

SELECT  username, email, FROM_UNIXTIME(register_date)
FROM `xf_user`
WHERE FROM_UNIXTIME( register_date ) > '2016-03-20'
order by register_date asc

For all the resulting rows of users I would also want to get their custom profile field values from the table 'xf_user_field_value'
such that field_id IN ( 'city' , 'country' )

So the resulting output reads as :
username, email, city, country

Any help regarding the query?

Example based on yours:

SELECT  username, email, FROM_UNIXTIME(register_date), f1.field_value, f2.field_value
FROM `xf_user` AS u
LEFT JOIN xf_user_field_value AS f1 ON (f1.user_id = u.user_id AND f1.field_id = 'city')
LEFT JOIN xf_user_field_value AS f2 ON (f2.user_id = u.user_id AND f2.field_id = 'country')
WHERE FROM_UNIXTIME( register_date ) > '2005-03-20'
order by register_date asc

 

[LaxmiSathy]

yes Jake that worked perfect.