I need some help with some sql and php please

I know you would probably look at this code and wonder what the heck dose it do. Well The function of this code is to display the latest forum posts in a dolphin page block from a vbulletin forum. When I get it finished the end result is it will look just like the Boonex.com /forums.

So now that you know what it is, this is what I need your help with.

I need to figure out how I to call and display  the avatar of the thread creator.

The only way I can see doing this is  I will have to match the username of the post creator in vBulletin with a dolphin username and get the avatar from dolphin that way. Since I cant use the user id from vbulletin because the do not match the dolphin user id's. But I really have no clue on how to do this. Your help would be greatly appreciated :)

I want to display the avatar where the  code is highlighted in red. 

 

 

 

 

<?php
/**
 * VB Recent Topics - main object
 */

require_once('register.class.php');
require('config.php');


/**
 * Main recent topics object
 * @package Recent_Topics
 */
class recent_topics
{
    var $db;
    var $from;
    var $forums = array();
    var $quantity = 20;
    var $data = array();
    var $time_format = 'g:i a, D M j, Y';

    /**
     * This constructor will setup the database, parse options, and grab the data
     * @param array $options Options package with things like forums to include/exclude,
     * quantity of recent topics to return, etc.
     * @return void
     */
    public function __construct($options)
    {
        if (!register::exists('db_host')) {
            trigger_error('Recent topics: Check database settings.', E_USER_ERROR);
        }
   
        $this->db = mysql_connect(register::get('db_host'), register::get('db_user'), register::get('db_password'));

        if (!$this->db) {
            trigger_error('Recent topics: Cannot connect to the database. Check your database settings in config.php.', E_USER_ERROR);
        }
       
        $result = mysql_select_db(register::get('db_name'), $this->db);
       
        if (!$result) {
            trigger_error('Recent topics: Cannot select the database. Check the database options in config.php.', E_USER_ERROR);       
        }
       
        $this->parseOptions($options);
        $this->getData();
    }
   
    /**
     * Parse Options
     * @param array $options The options package consisting of exclude_from, include_from,
     * quantity, and time_format. all of those are optional
     */   
    private function parseOptions($options)
    {
        if (!empty($options['include_from']) && !empty($options['exclude_form'])) {
            trigger_error('Recent topics: Can only include or exclude, not both.', E_USER_ERROR);
        }
       
        // include these forums...
        if (!empty($options['include_from'])) {
            $this->extract_forums($options['include_from']);
            $this->from = 'INCLUDE';
        }
        // exclude these forums . . .
        elseif (!empty($options['exclude_from'])) {
            $this->extract_forums($options['exclude_from']);
            $this->from = 'EXCLUDE';
        }
        else {
            $this->from = 'ALL';
        }
       
        if (!isset($options['quantity']) || empty($options['quantity'])) {
            // do nothing (uses the default value)
        }
        elseif (intval($options['quantity']) == $options['quantity'] ) {
            $this->quantity = $options['quantity'];
        }
        else {
            trigger_error('Recent topics: Check the quantity option.', E_USER_ERROR);
        }
       
        if (isset($options['time_format']) && !empty($options['time_format'])) {
            $this->time_format = $options['time_format'];
        }
    }
   
    /**
     * Extracts forums from comma seperated list into an array and puts it into a
     * class property
     * @param string $forums_csv The comma seperated forum ID list
     */   
    private function extract_forums($forums_csv)
    {
        $forums_csv = str_replace(' ', '', $forums_csv);
        $this->forums = explode(',', $forums_csv);
    }
   
    /**
     * Gets the actual topics data from the database and populates the $this->data property
     */   
    public function getData()
    {   
        $pre = register::get('db_table_prefix');
        $url = register::get('forum_url');
        $url = str_ireplace('http://', '', $url);
       
        switch($this->from)
        {
            case 'INCLUDE':
                $forums = 'AND t.forumid IN ('.implode(', ', $this->forums).')';
            break;
            case 'EXCLUDE':
                $forums = 'AND t.forumid NOT IN ('.implode(', ', $this->forums).')';   
            break;
            default:
                $forums = '';
            break;
        }
                   
        $q = "    SELECT     t.threadid, t.title, t.firstpostid, t.lastpost, t.replycount, t.postusername, t.lastposter, t.dateline, t.lastpostid, f.title_clean
                       
                FROM vbthread t
                INNER JOIN vbforum f ON (f.forumid = t.forumid)
                WHERE t.visible = '1'
                AND t.open = '1'
                {$forums}
                ORDER BY t.lastpost desc
                LIMIT {$this->quantity}";
               
        $results = mysql_query($q, $this->db);
        if(!$results) { print "(".mysql_error().") with query $q"; }
       
        while($row = mysql_fetch_array($results)) {
       
            $row['last_post_time'] = date($this->time_format, $row['lastpost']);
            $row['first_post_time'] = date($this->time_format, $row['dateline']);
            $row['url'] = 'http://'.$url.'showthread.php?t='.$row['threadid'];
            $row['url_last'] = 'http://'.$url.'showthread.php?p='.$row['lastpostid'].'#post'.$row['lastpostid'];
            $row['url_first'] = 'http://'.$url.'showthread.php?p='.$row['firstpostid'].'#post'.$row['firstpostid'];
            $row['url_reply'] = 'http://'.$url.'showthread.php?p='.$row['replycount'].'#post'.$row['replycount'];
           
            $this->data[] = $row;
            unset($row);
        }
    }
   
    /**
     * Gets the raw data array
     * @return array|boolean The data array or false if there is none
     */   
    public function get_array()
    {
        return (!empty($this->data)) ? $this->data : false;
    }
   
    /**
     * Gets an HTML table of the recent topics
     * @return string HTML table of the recent topics
     */   
    public function get_html_table()
    {
        $html = "\n\n".'    <div class="dbContent"><tbody><td>'."\n";
                   
        foreach($this->data as $topic) {
            $html .= '<link rel="stylesheet" type="text/css" href="vb_recent_topics/css/main.css" />
            <div class="sys_block_divider"> </div>
            <div class="sys_bl_content">
<table class="forum_table_list" cellspacing="0">
<tbody>
<tr>
<td class="forum_table_column_first forum_table_fixed_height">
<span class="forum_icon_title_desc > span">

<!--<img class="forum_user_icon" src="http:../vbforum/profile.php?$session[sessionurl]do=editavatar"><img class="forum_user_icon" src="http:vbforum/image.php?u=$bbuserinfo[postuserid]" alt="Your avatar" border="0" height="32" width="32" /> -->
<img class="forum_icon_title_desc" src="http:vb_recent_topics/images/topic.png">

<div class="forum_icon_title_desc">
<a class="forum_topic_title" <a href="'.$topic['url'].'">'.$topic['title'].'</a>
<span>
<div>
<div class="forum_stat">Post by: <a href="'.$topic['postusername'].'">'.$topic['postusername'].'</a> at  '.$topic['first_post_time'].'</a></div>
</div><div>
<div class="forum_stat">last reply by: <a href="'.$topic['lastposter'].'">'.$topic['lastposter'].'</a> at  '.$topic['last_post_time'].'</div>
</div>
</span>
</span>
</div>
</td>
<td class="forum_table_column_stat""'.$topic['replycount'].'">'.$topic['replycount'].' Replies</td>
</tr>
</table></div><div class="clear_both">&nbsp;</div>'."\n";           
        }       
           
        $html .=    '</tbody>
        </div>';   
       
        return $html;
    }
   

   
       
   
    /**
     * Directly echos an HTML table of the recent topics
     */       
    public function display_html_table()
    {
        echo $this->get_html_table();
    }
   
   
    /**
     * Closes down the database connection
     */       
    public function __destruct()
    {
        mysql_close($this->db);
    }
}


?>

Give me something to believe in...
Quote · 7 Jun 2011

I did not read your entire code.

But if vBullettin is installed in the same domain of Dolphin and

If the user is logged in Dolphin while browsing vBulletting you can get the memberID cookie.

In fact you will have both vBullettin and Dolphin ID in the cookies.

You will be able to also insert the ID in the database just inside the Profiles table, in this way you will be able to compare both IDs in the future.

The cookie for dolphin is $_COOKIE['memberID']

Good luck

Web Development, Multimedia Design and Social Media.
Quote · 7 Jun 2011

 

I did not read your entire code.

But if vBullettin is installed in the same domain of Dolphin and

If the user is logged in Dolphin while browsing vBulletting you can get the memberID cookie.

In fact you will have both vBullettin and Dolphin ID in the cookies.

You will be able to also insert the ID in the database just inside the Profiles table, in this way you will be able to compare both IDs in the future.

The cookie for dolphin is $_COOKIE['memberID']

Good luck

 I'm not sure I understand how that would work. How do I do it?

Give me something to believe in...
Quote · 8 Jun 2011

Whether you have VB integrated with dolphin?

Also, you can just take the username search for that in dolphin database.

That will help.

----
Quote · 8 Jun 2011

Yes VB is integrated with dolphin and everything is contained in same database. I just need some code that will look for the VB username in the dolphin "Profiles" table so I can grab the avatar code from the "Avatar" column.

Give me something to believe in...
Quote · 8 Jun 2011

I do not know of a built in function in dolphin that will do that, so you will need to query the database.

Something like this.

$sNickName = $topic['postusername'];
$iMemID = db_value("SELECT `ID` FROM `Profiles` WHERE `NickName`='$sNickName'");

Then get the avatar.

$sAvatar = getMemberAvatar($iMemID);


https://www.deanbassett.com
Quote · 8 Jun 2011

 

I do not know of a built in function in dolphin that will do that, so you will need to query the database.

Something like this.

$sNickName = $topic['postusername'];
$iMemID = db_value("SELECT `ID` FROM `Profiles` WHERE `NickName`='$sNickName'");

Then get the avatar.

$sAvatar = getMemberAvatar($iMemID);


 Thank you deano, I'll see if I can do something with this.

Give me something to believe in...
Quote · 9 Jun 2011

I am not still sure if this is what you r looking for.

So if the member is logged in both Vbullettin and Dolphin at the same time, it means that both ID cookies are in place.

Now if you need to get the avatar of the logged in member from the Profiles table in Dolphin you can just do something like this

$getavatar = "SELECT * FROM Profiles WHERE ID='".(int)$_COOKIE['memberID']."'";

$result=mysql_query($getavatar) or die("Error select avatar: ".mysql_error());

while($row = mysql_fetch_array($result)) {

$avatar = $row['Avatar'];

}

Now the variable $avatar contains the result from the DB for the Avatar table and you can do whatever you want with that.

I used a simple mySQL query because I do not know where you are using that code, so it will work in any case.

Web Development, Multimedia Design and Social Media.
Quote · 13 Jun 2011

Finally try this.

This should work, if not contact me.

 

$getavatar = "SELECT * FROM Profiles WHERE ID='".(int)$_COOKIE['memberID']."'";

$result=mysql_query($getavatar) or die("Error select avatar: ".mysql_error());

while($row = mysql_fetch_array($result)) {

$avatar = $row['Avatar'];

}

<img class="forum_user_icon" src="$site['url']modules/boonex/avatar/data/images/$avatar.jpg" alt="Your avatar" border="0" height="32" width="32" />

Web Development, Multimedia Design and Social Media.
Quote · 13 Jun 2011

@ YobiLab I sent you a message

Give me something to believe in...
Quote · 13 Jun 2011
 
 
Below is the legacy version of the Boonex site, maintained for Dolphin.Pro 7.x support.
The new Dolphin solution is powered by UNA Community Management System.