XF 2.1 query to find all users showing as "guest"

[jmmp]

What query do I have to run to get a list of all GUEST users?

I tried this but it didn't work:

SELECT * FROM xf_user WHERE user_id = 0;

 

[Paul B]

Guests don't have accounts.

It's not clear what you're trying to do.

 

[jmmp]

Hi, Brogan.

I have a number of such guest users and I would like to know what they are and how many in total so I can assign them to other accounts and manage all their messages. Assigning other accounts I know what is the query to do, but not HOW TO FIND THEM (a list of them), so I ask how to locate them with the correct query.

 

[Kirby]

Your post doesn't make sense (to me).
Guests are not users and it is therefore impossible to "find them".

Example
If you have 10 posts by guests that could be 10 distinct people who made them - or it could be just one person (or anything in between), there is no way to tell for sure.

 

[jmmp]

I assure you that there are guest users due to an import.

Does anyone know how to find them all with a query? Thanks for the help.

 

[Kirby]

Sorry, I can't help you as guests are not users and therefore cannot be found. It's impossible.

What is surely possible:
Your import is messed up and missing users that did exist in the source system but do not exist in XenForo.

What was the import source?

 

[jmmp]

Other people had this happen to them after an import, for example:

XF 1.3 - User showing as "guest"

I have a user that shows as a "guest" he is not associated with any group. He was leftover from a import from IPB way back in the day. He later joined under a different name. All his post show but there is no account associated with him such as a user, banned, etc. He is just a "guest". Is there...

xenforo.com

I know how to merge guest user with another one with the same nick correctly registered. But first I want to know the list of all those guest users and then merge them.

I hope someone knows how to do this query.

 

[tajhay]

Guests can also be deleted accounts from memory

 

[jmmp]

It would be a query to find all the users in the database with this id: user_id = 0

 

[Paul B]

There may be posts and other content by guests, but there are no accounts with a user ID of 0.

As above, there is no way of determining which content belongs to which guest user, unless you assume the user name is unique for each one.

 

[jmmp]

Brogan, these queries work to merge john1988 guest to john1988's normal account (I assume john1988 is the same person with two accounts, one normal and one with guest posts):

UPDATE xf_post SET user_id = 741, username = 'john1988' WHERE username = 'john1988' AND user_id = 0;

UPDATE xf_thread SET user_id = 741, username = 'john1988' WHERE username = 'john1988' AND user_id = 0;

But first I need to know the list of all those guest users and then merge them one by one.

 

[Kirby]

Not sure if this what you want...

SELECT DISTINCT username FROM xf_post WHERE user_id = 0;

SELECT DISTINCT username FROM xf_thread WHERE user_id = 0;

 

[jmmp]

Interesting approach, Kirby.

Can you translate into words exactly what, for example, the first query is looking for? "List of usernames that have posted...¿?"

 

[Kirby]

As said before I don't really understand what you are looking for.

The queries return unique usernames used by guests for posts and threads.

 

[jmmp]

Those are good queries, Kirby. I can use it.

Thank you very much!