• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Querying xf_user

MOZ

Well-known member
#1
For further devlopment of my addon: http://xenforo.com/community/resources/raid101-com-member-list-manager.884/

I want to add the list of followers for each user. I have already add the required column (follow_count) into the xf_user table, however I am unsure how to query the DB to populate the column when the Member List page is shown.

Here is my query (This works fine, tested in phpmyadmin):
UPDATE 'xf_user' AS 'u1' LEFT JOIN (SELECT 'follow_user_id' AS 'user_id2', COUNT('follow_user_id') AS 'follow_count' FROM 'xf_user_follow' GROUP BY 'follow_user_id') AS 'u2' ON 'u1.user_id'='u2.user_id2' SET 'u1.follow_coun't='u2.follow_count';
 

Jake Bunce

XenForo moderator
Staff member
#3
I think you want this:

Code:
UPDATE xf_user AS u
SET u.follow_count = (
	SELECT COUNT(*)
	FROM xf_user_follow AS uf
	WHERE uf.follow_user_id = u.user_id
	GROUP BY uf.follow_user_id
);
 

MOZ

Well-known member
#4
OK, my question, is how do I add this to the PHP files? Just use mysql_query? Do I have to do anything specific?
 

Jake Bunce

XenForo moderator
Staff member
#5
This code will work pretty much anywhere:

Code:
$db = XenForo_Application::get('db');

$db->query("
	UPDATE xf_user AS u
	SET u.follow_count = (
		SELECT COUNT(*)
		FROM xf_user_follow AS uf
		WHERE uf.follow_user_id = u.user_id
		GROUP BY uf.follow_user_id
	)
");