[RESOLVED] help with inner join

harmor

I have this query which may be intimidating.
What I want to do is show latest 5 posts but only in the forums that the user can access.

$getposts = $db->query_read("
    SELECT post.postid AS postid, user.usergroupid AS usergroupid, post.pagetext AS pagetext,
    forumpermission.usergroupid AS fpusergroupid, forumpermission.forumpermissions AS forumpermissions
    FROM " . TABLE_PREFIX . "post AS post
    INNER JOIN " . TABLE_PREFIX . "user AS user
    ON post.userid = user.userid 
    INNER JOIN " . TABLE_PREFIX . "forumpermission
    ON user.usergroupid = forumpermission.usergroupid
    WHERE forumpermissions != '0'
    ORDER BY postid DESC LIMIT 5");    

    while($post = $db->fetch_array($getposts))
    {

    echo "<b>".$post['postid']."</b> - ".$post['pagetext']."<br /><br />";
    // eval('$latestposts .= "' . fetch_template('latestposts') . '";');

}

What it's doing is getting the results twice.
Example

3. my name's scott
3. my name's scott
2. take a look at this
2. take a look at this
1. vBulletin vs IPB

Weedpacket

Moving this to a more appropriate forum.

Not knowing anything about what is in your database or how it's arranged, it looks like SELECT DISTINCT would have an effect.

harmor

That helped.

Here's the final code

$getposts = $db->query_read("
    SELECT DISTINCT *
    FROM " . TABLE_PREFIX . "post AS post
    LEFT JOIN " . TABLE_PREFIX . " thread AS thread
    ON post.threadid = thread.threadid 
    ORDER BY postid DESC LIMIT 5");    

    while($post = $db->fetch_array($getposts))
    {
	    $forumperms = fetch_permissions($post['forumid']);
	    if (($forumperms & $vbulletin->bf_ugp_forumpermissions['canview']) OR ($forumperms & $vbulletin->bf_ugp_forumpermissions['canviewthreads']))
		{

    echo "<b>".$post['postid']."</b> - ".$post['pagetext']."<br /><br />";
    // eval('$latestposts .= "' . fetch_template('latestposts') . '";');

	}


}

harmor

This is my final code.
The last problem I had is that it would return 5 results but filter them. So if if it blocked a result the query wouldn't replace it.

$getposts = $db->query_read("
    SELECT DISTINCT *
    FROM " . TABLE_PREFIX . "post AS post
    LEFT JOIN " . TABLE_PREFIX . " thread AS thread
    ON post.threadid = thread.threadid 
    ORDER BY postid DESC");    

    while($post = $db->fetch_array($getposts))
    {

    $forumperms = fetch_permissions($post['forumid']);
    if (($forumperms & $vbulletin->bf_ugp_forumpermissions['canview']) OR ($forumperms & $vbulletin->bf_ugp_forumpermissions['canviewthreads']))
	{
		if($count <= 5)
		{

	        echo "<b>".$post['postid']."</b> - ".$post['pagetext']."<br /><br />";
	        // eval('$latestposts .= "' . fetch_template('latestposts') . '";');

    	}

    $count++;

	}

}