Soft-deleted topics

[Killsparer]

I use a little "vbulletin-hack" to display the latest topics on a non-vb page on my server, but i currently have the problem, that it also displays soft-deleted topics.

Here is the current code:

<?
//////// NEUESTE BEITRAEGE ///////////
$postitems = mysql_query("SELECT * FROM thread WHERE visible=1 AND open!=10 AND forumid IN (2,62,4,5,123,93,134,6,8,95,111,126) ORDER BY dateline DESC LIMIT 24");

while($thread_get=mysql_fetch_array($postitems))
{
$threadid = $thread_get['threadid'];
$title = $thread_get['title'];

if (strlen($title)>55)
{$title = substr($title,0,55).'...';}

$postchunk = "<div style=\"padding-left: 10px;\"><img src=\"portal/v2/posticon.gif\"> <a href=\"forum/showthread.php?t=$threadid\">$title</a></div><img src=\"portal/v2/right_postline.gif\" /><br />";
echo $postchunk;
}
?>

So is there a way to add the "primaryid"-field of the "deletionlog"-table to the above piece of code, so that deleted topics are being "filtered" out of the query?

Thanks in advance,
Martin

[amykhar]

Yep. Don't reinvent the wheel. Go read the code for some of the portals that pull threads to the portal. See their query and use it in your own script.

[Killsparer]

Go read the code for some of the portals that pull threads to the portal. See their query and use it in your own script.

Already checked familiar scripts, but none of then had the needed query.

Would be great, if someone could help me there. It just needs to compare the "threadid" and "primaryid" fields. My problem is, that these two fields are in two seperate tables, which exceeds my sql-knowledge.

[amykhar]

LEFT JOIN ' . TABLE_PREFIX . 'deletionlog AS deletionlog ON (thread.threadid = deletionlog.primaryid AND type = \'thread\')';
WHERE thread.visible = 1 AND thread.open != 10 AND deletionlog.primaryid IS NULL'


This is the general idea. I grabbed the code from Advanced CMPS and haven't made sure all the punctuation is correct.