Xenforo Coding Help - Grabbing custom user field from DB

Discussion in 'Web Development and Programming' started by bauss, Apr 3, 2014.

  1. bauss

    bauss Regular Member

    I'm trying to code a custom addon for my site, and there's a bug that I can't fix. I can't get it to display the poker usernames which is stored in the DB as a custom user field.

    The ID is for the custom user field is: 626c61636b5f636869705f706f6b65725f757365726e616d65

    Any ideas on how to display the $poker_user variable?
    The page is located at http://thepokertalk.com/pages/leaderboards if you need to see a demo of how it works.

    Here's the code:
    Code:
    <?php
 
class Freerolls_leaders extends XenForo_Model{
 
public static function getMembers(){
    $db = XenForo_Application::get('db');
$query = $db->fetchAll("SELECT * FROM xf_user WHERE secondary_group_ids IN (2,4,5)");
    foreach($query AS $rowName => $results)
            {
$poker_user = $db->fetchRow("SELECT * FROM xf_user_field_value WHERE user_id=1 AND field_id='626c61636b5f636869705f706f6b65725f757365726e616d65'");
                $rowName++;
                print "<tr><td>#." . $rowName . "</td>";
                print "<td>" .$results['username'] . "</td>";
                print "<td>" . $poker_user['field_value'] . "</td>";
                print "</td><td align=\"center\">". $results['bcp_lbp'] ."</td>";
                print "</td><td align=\"center\">$". $results['bcp_freeroll_money_won'] ."</td></tr>";
            }

}
}

?>
     
    bauss, Apr 3, 2014
    #1
  2. Cerberus

    Cerberus Admin Talk Staff

    PHP:
    <?PHP

    $user_name     = "";
    $password = "";
    $database = "";
    $server = "";
    $db_handle = mysql_connect($server, $user_name, $password);
    $db_found = mysql_select_db($database, $db_handle);

    if (!    $db_handle)
      {
      die(    'Could not connect: ' . mysql_error());
      }

    //Get long ip
    $LongIP = ip2long(getip());

    //Check long ip against xf_ip table .. If there out put the ip_id
    $SQL = "SELECT * FROM xf_ip WHERE ip = '".$LongIP."' ORDER BY log_date DESC LIMIT 1";

    $result = mysql_query($SQL);
    $db_field = mysql_fetch_assoc($result);
    $Uid = $db_field['ip_id'];

    //Error checking so if empty closes connection
    If($Uid == ""){
        echo     "Nothing There";
            mysql_close($db_handle);
    }Else{

    //Check ip_id against the xf_post table to get the username..
    $SQL = "SELECT * FROM xf_post WHERE ip_id = '".$Uid."' ORDER BY post_id DESC LIMIT 1";
    $result = mysql_query($SQL);
    $db_field = mysql_fetch_assoc($result);
    $UName = $db_field['username'];

    //Now you can get whatever you want from xf_user .. Checks username against xf_user
    $SQL = "SELECT * FROM xf_user WHERE username = '".$UName."' ORDER BY user_id DESC LIMIT 1";
    $result = mysql_query($SQL);
    $db_field = mysql_fetch_assoc($result);

    //$variable = $db_field['whateverentryyouwant'] Posts as example
    $Posts = $db_field['message_count'];

    //Display Whatever you grabbed
    echo "Posts: $Posts<br />";

    mysql_close($db_handle);

    }

    // Written by Precise
    function getip(){
      if (!empty(    $_SERVER['HTTP_CLIENT_IP']))
      {
            $ip = $_SERVER['HTTP_CLIENT_IP'];
      }
      elseif (!empty(    $_SERVER['HTTP_X_FORWARDED_FOR']))
      {
            $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
      }
      else {    $ip = $_SERVER['REMOTE_ADDR'];}  
      return     $ip;
    }

    ?>
    I wrote this awhile back for a client of mine, to help them with a script. This should give you enough info to do what you want. Obviously to test it you would need to fill out the info at the top of the script. Also, if it keeps coming up empty just make a post on your site so your ip is updated in the table.
     
    Cerberus, Apr 3, 2014
    #2
  3. bauss

    bauss Regular Member

    I didn't think that would work using mysql_query instead of OOP(and connecting to DB in file), but I'll give it a try. Thanks!
     
    bauss, Apr 3, 2014
    #3
  4. Adrian Schneider

    Adrian Schneider Regular Member

    Something like this looks more like what you want...

    http://pastebin.com/4XKMxu0W

    You can remove the nested query by using a proper join.
     
    Adrian Schneider, Apr 3, 2014
    #4
  5. bauss

    bauss Regular Member

    @Adrian Schneider I tried using that code and I'm getting this error:
    Fatal error: Method XenForo_Template_Public::__toString() must not throw an exception in /home/thepoker/public_html/library/XenForo/Template/Abstract.php(265) : eval()'d code on line 0

    Here's the code I'm using, any help would be greatly appreciated:
    Code:
    <?php

class Freerolls_Leaders
{
    public static function getMembers()
    {
        $db = XenForo_Application::get('db');

        return $db->fetchAll("
           SELECT user.username
                , user.bcp_lbp
                , user.bcp_freeroll_money_won
                , profile.field_value AS poker_user
             FROM xf_user
           LEFT OUTER
            JOIN xf_user_field_value AS profile
              ON profile.user_id = user.user_id
             AND profile.field_id = '626c61636b5f636869705f706f6b65725f757365726e616d65'
           WHERE user.secondary_group_ids IN (2,4,5)
       ");
    }
}

$num = 0;
foreach (Freerolls_Leaders::getMembers() as $member) {
    $num++;
     print "<tr><td>#." . $num ."</td>";
                print "<td>" .$member['username'] . "</td>";
                print "<td>" . $member['poker_user'] ."</td>";
                print "<td align=\"center\">". $member['bcp_lbp'] ."</td>";
                print "<td align=\"center\">$". $member['bcp_freeroll_money_won'] ."</td></tr>";
}

?>
     
    Last edited: Apr 5, 2014
    bauss, Apr 5, 2014
    #5
  6. Adrian Schneider

    Adrian Schneider Regular Member

    I'd try running that code directly (ie, not from a template) to see the actual error message.

    It could be your last print line. You should escape the $ (\$) if you want to print it directly. However, I'm confused why you're not using your templates for this. You should be passing $members into your template, and doing a standard <xen:foreach> over it.
     
    Adrian Schneider, Apr 8, 2014
    #6

Share This Page