Resetting a User's post count

[Valkyr]

An admin friend of mine messed with the post count of another user, I was wondering how I could reset this to the actual amount instead of "69" . He did this on the "profile info" page of the user.

 

[Jake Bunce]

There isn't a rebuild for this. But you can manually edit the count in their profile:

Admin CP -> Users -> Search for Users -> [find their account]

If you need to know the user's true post count then you can run this query on the database:

SELECT user_id, COUNT(*) AS posts
FROM xf_post
WHERE user_id = 1
GROUP BY user_id

Change the user_id appropriately.

 

[ragtek]

that's the COMPLETE postcount ignoring the different post states (soft deleted, moderated)

SELECT user_id, COUNT(*) AS posts FROM xf_post WHERE user_id = 1
AND
message_state = 'visible'
GROUP BY user_id

 

[Valkyr]

that's the COMPLETE postcount ignoring the different post states (soft deleted, moderated)

SELECT user_id, COUNT(*) AS posts FROM xf_post WHERE user_id = 1
AND
message_state = 'visible'
GROUP BY user_id

That worked a charm, don't suppose you have one for likes and trophy points?

 

[Valkyr]

That worked a charm, don't suppose you have one for likes and trophy points?

Bump D:? <3

 

[Jake Bunce]

This query will return the number of likes for the specified content_user_id:

SELECT content_user_id, COUNT(*) AS likes
FROM xf_liked_content
WHERE content_user_id = 1
GROUP BY content_user_id;

This query will return the number of trophy points for the specified user_id:

SELECT SUM(t.trophy_points) AS tpoints
FROM xf_user_trophy AS ut
LEFT JOIN xf_trophy AS t ON (t.trophy_id = ut.trophy_id)
WHERE ut.user_id = 1;

 

[Valkyr]

This query will return the number of likes for the specified content_user_id:

SELECT content_user_id, COUNT(*) AS likes
FROM xf_liked_content
WHERE content_user_id = 1
GROUP BY content_user_id;

This query will return the number of trophy points for the specified user_id:

SELECT SUM(t.trophy_points) AS tpoints
FROM xf_user_trophy AS ut
LEFT JOIN xf_trophy AS t ON (t.trophy_id = ut.trophy_id)
WHERE ut.user_id = 1;

Realized I could just count the trophies the user has received. Haha.

The likes worked perfectly though, thankyou!