MyBB Community Forums

In the recent threads block from the portal, if a thread contains a poll, I'd like the user to be able to tell if he has voted, something like a tick along side the thread title for example. Is this possible? Also if possible I'd like to be able to show the total number of votes that a poll has had in the recent threads too, but first thing is the most important.

Thanks

Search (portal.php):

	$query = $db->query("
		SELECT t.*, u.username
		FROM ".TABLE_PREFIX."threads t
		LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=t.uid)
		WHERE 1=1 $unviewwhere AND t.visible='1' AND t.closed NOT LIKE 'moved|%'
		ORDER BY t.lastpost DESC 
		LIMIT 0, ".$mybb->settings['portal_showdiscussionsnum']
	);
	while($thread = $db->fetch_array($query))
	{

Replace with:

	$query = $db->query("
		SELECT t.*, u.username, p.numvotes as pollnumvotes, pv.vid
		FROM ".TABLE_PREFIX."threads t
		LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=t.uid)
		LEFT JOIN ".TABLE_PREFIX."polls p ON (p.pid=t.poll AND p.tid=t.tid)
		LEFT JOIN ".TABLE_PREFIX."pollvotes pv ON (pv.pid=p.pid AND pv.uid={$mybb->user['uid']})
		WHERE 1=1 $unviewwhere AND t.visible='1' AND t.closed NOT LIKE 'moved|%'
		ORDER BY t.lastpost DESC 
		LIMIT 0, ".$mybb->settings['portal_showdiscussionsnum']
	);
	while($thread = $db->fetch_array($query))
	{
		$voted = '';
		$votescount = 'Votes: '.my_number_format(isset($thread['pollnumvotes']) ? (int)$thread['pollnumvotes'] : 0);
		if((int)$thread['poll'] > 0 && isset($thread['vid']) && (int)$thread['vid'] > 0)
		{
			$voted = '*';
		}

This does seems to work

Thanks that works!

Just one small problem, one of the threads is showing up five times for me, but just once like it's supposed to for anyone else

For me / For others

Weird, I see nothing in my code to cause that. Are you sure this only happens after applying my edit?

Yep, when I replace it with the code I was using before it only shows up once.

Maybe I should have this before... but basically I created a custom php page to show all polls that had been posted on my forum, and as I don't know how to do things from scratch, I used the recent threads code straight from portal.php with a slight modification.

I added "AND t.poll > 0 " to the end of this line;

WHERE 1=1 $unviewwhere AND t.visible='1' AND t.closed NOT LIKE 'moved|%' 

to get this;

WHERE 1=1 $unviewwhere AND t.visible='1' AND t.closed NOT LIKE 'moved|%' AND t.poll > 0 

That worked fine and only showed threads with polls in the block using my old code, but as soon as I add "AND t.poll > 0" to your code, the duplicate thread problem happens. So it seems there's something conflicting between that and the code you gave but I don't know what

Is there a different bit of code that will work in the same way along with your code?

Maybe if you post your query I can apply something to it directly.

Ok cool thanks, this is what I was using;

        $query = $db->query("
                SELECT t.*, u.username, u.displaygroup, u.avatar, u.usergroup, u.avatardimensions, f.name AS forumname, lp.avatar as lpavatar, t.username AS threadusername
                FROM ".TABLE_PREFIX."threads t
                LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=t.uid)
                LEFT JOIN ".TABLE_PREFIX."users lp ON (lp.uid=t.lastposteruid)
                LEFT JOIN ".TABLE_PREFIX."forums f ON (f.fid=t.fid)	
                WHERE 1=1 $unviewwhere AND t.visible='1' AND t.closed NOT LIKE 'moved|%' AND t.poll > 0 
                ORDER BY t.lastpost DESC
                LIMIT 0, 10"
        );
        while($thread = $db->fetch_array($query))
        {

Try this:

$query = $db->query("
                SELECT t.*, u.username, u.displaygroup, u.avatar, u.usergroup, u.avatardimensions, f.name AS forumname, lp.avatar as lpavatar, t.username AS threadusername, p.numvotes as pollnumvotes, pv.vid
                FROM ".TABLE_PREFIX."threads t
                LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=t.uid)
                LEFT JOIN ".TABLE_PREFIX."users lp ON (lp.uid=t.lastposteruid)
                LEFT JOIN ".TABLE_PREFIX."forums f ON (f.fid=t.fid) 
                LEFT JOIN ".TABLE_PREFIX."polls p ON (p.pid=t.poll)
                LEFT JOIN ".TABLE_PREFIX."pollvotes pv ON (pv.pid=t.poll AND pv.uid='{$mybb->user['uid']}')
                WHERE 1=1 $unviewwhere AND t.visible='1' AND t.closed NOT LIKE 'moved|%' AND t.poll > 0 
                ORDER BY t.lastpost DESC
                LIMIT 0, 10"
        );

If it doesn't work, do you mind up to two extra queries?

Thanks, but that duplicates the threads too

Nope I don't mind the extra queries, there's almost nothing else on that page anyway so it wont add up to too much I wouldn't think

Something like this should work:

$query = $db->query("
                SELECT t.*, u.username, u.displaygroup, u.avatar, u.usergroup, u.avatardimensions, f.name AS forumname, lp.avatar as lpavatar, t.username AS threadusername
                FROM ".TABLE_PREFIX."threads t
                LEFT JOIN ".TABLE_PREFIX."users u ON (u.uid=t.uid)
                LEFT JOIN ".TABLE_PREFIX."users lp ON (lp.uid=t.lastposteruid)
                LEFT JOIN ".TABLE_PREFIX."forums f ON (f.fid=t.fid)    
                WHERE 1=1 $unviewwhere AND t.visible='1' AND t.closed NOT LIKE 'moved|%' AND t.poll > 0 
                ORDER BY t.lastpost DESC
                LIMIT 0, 10"
        );
$pids = $polls = $votes = array();
while($pid = $db->fetch_field($query, 'poll'))
{
    $pids[] = (int)$pid;
}

$query_p = $db->simple_select('polls', 'pid, numvotes', "pid IN ('".implode("','", $pids)."')");
while($poll = $db->fetch_array($query_p))
{
    $polls[(int)$poll['pid']] = (int)$poll['numvotes'];
}

$query_p = $db->simple_select('pollvotes', 'pid', "pid IN ('".implode("','", $pids)."') AND uid='{$mybb->user['uid']}'");
while($vote = $db->fetch_field($query_p, 'pid'))
{
    $votes[(int)$vote['pid']] = 1;
}

// this is your while loop
while($thread = ...)
{
    $votes = 'This poll has '.my_number_format($polls[(int)$poll['pid']]).' votes.';
    $mark = '';
    if(isset($votes[(int)$thread['poll']]))
    {
        $mark = '<span title="You already voted for this poll.">*</span>';
    }
}

I didn't test it but it should work :p Also, note that this assumes guests can't vote, that will probably need something else.