Querying xf_user

[MOZ]

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';

 

[MOZ]

Help, please. Also the title is misleading. It should be "Querying mysql database"

 

[Jake Bunce]

I think you want this:

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]

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]

This code will work pretty much anywhere:

$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
	)
");

 

[MOZ]

Thanks!