find user without sig (sql query+)

[Sculli]

SQL query god(s), once again I call upon thee!

One of my board rules is that every user must ad their game character to their signature. This is very important as the persons in-game reputations carries over to the board, and their board rep into the game.

Anyway, some users don't read the rules, some don't care, etc etc.

Ultimately what I would like is some sort of automated way to send all those who do not have anything at all in their sig a PM that says they do need to add their character name to their sig.

In the short term a query that would return the user names of those who do not have a sig of any kind would help, I would then manually PM those users.

Thanks in advance for your time and consideration.

[Marco van Herwaarden]

[sql]SELECT user.username
FROM usertextfield AS usertextfield
LEFT JOIN user AS user ON (user.userid = usertextfield.userid)
WHERE usertextfield.signature = ""[/sql]

[Sculli]

Macro to the rescue once again!
Thanks, really appreciate it.

Edit:
Oops, not quite working, returned a total of 18 users, and on the board I see some without a sig that are not in the listed 18. Also, 18 out of 3,200+ doesn't seem quite right.

Makes me wonder whether I would get different results if I were to run this from mysql> rather than through the board, will test that out tonight.

[Reeve of shinra]

Have you considered having the users add thier game name to a custom profile field and having the profile field called in the sig template?

$post[fieldx] where x is the custom field #?

[Marco van Herwaarden]

Originally Posted by Sculli

Edit:
Oops, not quite working, returned a total of 18 users, and on the board I see some without a sig that are not in the listed 18. Also, 18 out of 3,200+ doesn't seem quite right.

Makes me wonder whether I would get different results if I were to run this from mysql> rather than through the board, will test that out tonight.

Try:
[sql]SELECT user.username
FROM user AS user
LEFT JOIN usertextfield AS usertextfield ON (user.userid = usertextfield.userid)
WHERE usertextfield.signature = ""
OR usertextfield.signature = NULL
[/sql]

It should make no difference if you run from ACP or mysql directly.

[Sculli]

@Reeve of shinra
Well, either which way, the problem is that some users simply don't do it, no matter where I would give them the option to do so. So I am trying to teack the little buggers down and yell at them!

@MarcoH64
No luck, same 18 entries returned, and I am no help at all since I really don't know the first thing about SQL. Perhaps it's time for me to learn ...

[Reeve of shinra]

You can make a custom field required and there's a hack out that would force everyone to go in and update it

oh and maybe its only those 18 people without a sig at all?

[Sculli]

This is certainly very curious. The query looks like it should work, yet it doesn't, meaning it returns less than the expected number of results.

Here is an extension of this mod request: A mod that would allow the admin to SET the signature of all those who have not set one themselves. That would be most awesome.

P.S.: Still experimenting with queries to try and get to the bottom of why this doesn't work properly.