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


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;
Last edited:
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.
Your post doesn't make sense (to me).
Guests are not users and it is therefore impossible to "find them".

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.
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?
Other people had this happen to them after an import, for example:

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.
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.
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.
Last edited:
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;
Last edited:
Interesting approach, Kirby.

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