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

Querying xf_user

Discussion in 'XenForo Development Discussions' started by MOZ, Jul 21, 2012.

  1. MOZ

    MOZ Well-Known Member

    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';
     
  2. MOZ

    MOZ Well-Known Member

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

    Jake Bunce XenForo Moderator Staff Member

    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
    );
    
     
  4. MOZ

    MOZ Well-Known Member

    OK, my question, is how do I add this to the PHP files? Just use mysql_query? Do I have to do anything specific?
     
  5. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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
    	)
    ");
    
     
  6. MOZ

    MOZ Well-Known Member

    Thanks!
     

Share This Page