See latest attached files on sidebar block ?

[Mornagest]

Hello,

We have some video game mod developers on our forums and they asked me if that was possible to see the X latest attached files posted on the forum, in a sidebar block.

All I found is the PHP script that shows them in the admin panel : http://www.example.fr/forum/admin/attachment.php?do=search&search[orderby]=dateline&search[ordering]=DESC

Would it be possible to have the same showing those results in a sidebar block, such as the latest posts ?

Maybe it isn't difficult to find how but I know nothing about PHP...

Thank you in advance !

[MarkFL]

This is how I set up the forum block in the "Forum Blocks Manager":

https://vborg.vbsupport.ru/attachment.php?attachmentid=153872&stc=1&d=1450766041

This is the PHP code:

global $vbulletin, $db;
$number_of_attachments = 5;
$output = '';

$last_attachments = $vbulletin->db->query_read_slave("
SELECT attachment.*, user.*, post.postid, post.threadid, thread.title, filedata.filesize
FROM " . TABLE_PREFIX . "attachment
INNER JOIN " . TABLE_PREFIX . "user
ON user.userid = attachment.userid
INNER JOIN " . TABLE_PREFIX . "post
ON post.postid = attachment.contentid
INNER JOIN " . TABLE_PREFIX . "thread
ON thread.threadid = post.threadid
INNER JOIN " . TABLE_PREFIX . "filedata
ON filedata.filedataid = attachment.filedataid
ORDER BY attachment.dateline DESC
LIMIT " . $number_of_attachments
);

$n = 0;
while ($attachment = $db->fetch_array($last_attachments))
{
$output .= '<div';
if ($n++)
{
$output .= ' style="border-top: 1px solid #CCCCCC"';
}
$output .= '>Posted By: <div style="display: inline-block">' . attach_user_link($attachment) . '</div><div title="Uploaded: ' . vbdate($vbulletin->options['dateformat'], $attachment['dateline'], 1) . ' at ' . vbdate($vbulletin->options['timeformat'], $attachment['dateline']) . PHP_EOL . 'Views: ' . $attachment['counter'] . PHP_EOL . 'Size: ' . number_format($attachment['filesize']/1024,1) . ' KB">' . $attachment['filename'] . '</div><a title="Go To Post With Attachment" href="showthread.php?' . $attachment['threadid'] . '-' . str_replace(' ', '-', $attachment['title']) . '&p=' . $attachment['contentid'] . '&viewfull=1#post' . $attachment['contentid'] . '">' . $attachment['title'] . '</a></div>';
}

return $output;

function attach_user_link($user_name)
{
global $vbulletin;
$link = 'member.php?do=getinfo&username=' . $user_name['username'];

if ($user_name['displaygroupid'])
{
$groupid = $user_name['displaygroupid'];
}
else
{
$groupid = $user_name['usergroupid'];
}

$open_tag = $vbulletin->usergroupcache[$groupid]['opentag'];
$close_tag = $vbulletin->usergroupcache[$groupid]['closetag'];
return '<a title="Go To ' . $user_name['username'] . '\'s Profile" href="' . $link . '">' . $open_tag . $user_name['username'] . $close_tag . '</a>';
}

And this is the result on my local dev site:

https://vborg.vbsupport.ru/attachment.php?attachmentid=153873&stc=1&d=1450766041

The usernames are shown in their usergroup HTML markup, and link to their profiles. The attachment filename has a tooltip that shows when it was uploaded, the number of views and the size of the file (in KB).

Below the filename is a link to the post containing the attachment.

You can edit the PHP code, the second line, to change the number of most recent attachments to display...replace the "5" with whatever positive integer you want.

Please let me know of any changes you would like. :)

[Mornagest]

Hello Mark,

And thank you for your quick answer :)

I made a try and got a database error. This comes up because we put a prefix to all our vBulletin tables (we have several other databases so we chose to prefix their tables). I tried to add the prefix to the query but it still doesn't work ; I don't know where to add it.

As you can see, the prefix is vb_ ; where should I add it ?

Again, thank you !

Database error in vBulletin 4.2.2:

Invalid SQL:

SELECT attachment.*, user.*, post.postid, post.threadid, thread.title, filedata.filesize
FROM vb_attachment
INNER JOIN vb_user
ON user.userid = attachment.userid
INNER JOIN vb_post
ON post.postid = attachment.contentid
INNER JOIN vb_thread
ON thread.threadid = post.threadid
INNER JOIN vb_filedata
ON filedata.filedataid = attachment.filedataid
ORDER BY attachment.dateline DESC
LIMIT 5;

MySQL Error : Unknown table 'attachment'
Error Number : 1051
Request Date : Tuesday, December 22nd 2015 @ 01:31:53 PM
Error Date : Tuesday, December 22nd 2015 @ 01:31:54 PM
Script : http://www.myforum.fr/forum.php
Referrer : http://www.myforum.fr/forum.php
IP Address : 80.67.176.207
Username : Mornagest
Classname : vB_Database
MySQL Version : 5.5.46-0ubuntu0.12.04.2

[MarkFL]

Try this:

global $vbulletin, $db;
$number_of_attachments = 5;
$output = '';

$last_attachments = $vbulletin->db->query_read_slave("
SELECT vb_attachment.*, vb_user.*, vb_post.postid, vb_post.threadid, vb_thread.title, vb_filedata.filesize
FROM " . TABLE_PREFIX . "vb_attachment
INNER JOIN " . TABLE_PREFIX . "vb_user
ON vb_user.userid = vb_attachment.userid
INNER JOIN " . TABLE_PREFIX . "vb_post
ON vb_post.postid = vb_attachment.contentid
INNER JOIN " . TABLE_PREFIX . "vb_thread
ON vb_thread.threadid = vb_post.threadid
INNER JOIN " . TABLE_PREFIX . "vb_filedata
ON vb_filedata.filedataid = vb_attachment.filedataid
ORDER BY vb_attachment.dateline DESC
LIMIT " . $number_of_attachments
);

$n = 0;
while ($attachment = $db->fetch_array($last_attachments))
{
$output .= '<div';
if ($n++)
{
$output .= ' style="border-top: 1px solid #CCCCCC"';
}
$output .= '>Posted By: <div style="display: inline-block">' . attach_user_link($attachment) . '</div><div title="Uploaded: ' . vbdate($vbulletin->options['dateformat'], $attachment['dateline'], 1) . ' at ' . vbdate($vbulletin->options['timeformat'], $attachment['dateline']) . PHP_EOL . 'Views: ' . $attachment['counter'] . PHP_EOL . 'Size: ' . number_format($attachment['filesize']/1024,1) . ' KB">' . $attachment['filename'] . '</div><a title="Go To Post With Attachment" href="showthread.php?' . $attachment['threadid'] . '-' . str_replace(' ', '-', $attachment['title']) . '&p=' . $attachment['contentid'] . '&viewfull=1#post' . $attachment['contentid'] . '">' . $attachment['title'] . '</a></div>';
}

return $output;

function attach_user_link($user_name)
{
global $vbulletin;
$link = 'member.php?do=getinfo&username=' . $user_name['username'];

if ($user_name['displaygroupid'])
{
$groupid = $user_name['displaygroupid'];
}
else
{
$groupid = $user_name['usergroupid'];
}

$open_tag = $vbulletin->usergroupcache[$groupid]['opentag'];
$close_tag = $vbulletin->usergroupcache[$groupid]['closetag'];
return '<a title="Go To ' . $user_name['username'] . '\'s Profile" href="' . $link . '">' . $open_tag . $user_name['username'] . $close_tag . '</a>';
}

[Mornagest]

Hello Mark, and thank you :)

I tried this but it still didn't work. Here's the error message : Database error in vBulletin 4.2.2:

Invalid SQL:

SELECT vb_attachment.*, vb_user.*, vb_post.postid, vb_post.threadid, vb_thread.title, vb_filedata.filesize
FROM vb_vb_attachment
INNER JOIN vb_vb_user
ON vb_user.userid = vb_attachment.userid
INNER JOIN vb_vb_post
ON vb_post.postid = vb_attachment.contentid
INNER JOIN vb_vb_thread
ON vb_thread.threadid = vb_post.threadid
INNER JOIN vb_vb_filedata
ON vb_filedata.filedataid = vb_attachment.filedataid
ORDER BY vb_attachment.dateline DESC
LIMIT 5;

MySQL Error : Table 'myforum.vb_vb_attachment' doesn't exist
Error Number : 1146
Request Date : Tuesday, December 22nd 2015 @ 08:22:03 PM
Error Date : Tuesday, December 22nd 2015 @ 08:22:03 PM
Script : http://www.myforum.fr/forum.php
Referrer : http://www.myforum.fr/
IP Address : 80.67.176.207
Username : Mornagest
Classname : vB_Database
MySQL Version : 5.5.46-0ubuntu0.12.04.2

So I tried by adding the vb_ prefix only for the rows (I guess "vb_attachment.*, vb_user.*, vb_post.postid, vb_post.threadid, vb_thread.title, vb_filedata.filesize" are rows in tables ?) and NOT for the tables and that works perfectly now :up:

Many many thanks to you, Mark, for your patience and receptiveness :)



edit : here's the final code :global $vbulletin, $db;
$number_of_attachments = 5;
$output = '';

$last_attachments = $vbulletin->db->query_read_slave("
SELECT vb_attachment.*, vb_user.*, vb_post.postid, vb_post.threadid, vb_thread.title, vb_filedata.filesize
FROM " . TABLE_PREFIX . "attachment
INNER JOIN " . TABLE_PREFIX . "user
ON vb_user.userid = vb_attachment.userid
INNER JOIN " . TABLE_PREFIX . "post
ON vb_post.postid = vb_attachment.contentid
INNER JOIN " . TABLE_PREFIX . "thread
ON vb_thread.threadid = vb_post.threadid
INNER JOIN " . TABLE_PREFIX . "filedata
ON vb_filedata.filedataid = vb_attachment.filedataid
ORDER BY vb_attachment.dateline DESC
LIMIT " . $number_of_attachments
);

$n = 0;
while ($attachment = $db->fetch_array($last_attachments))
{
$output .= '<div';
if ($n++)
{
$output .= ' style="border-top: 1px solid #CCCCCC"';
}
$output .= '>Posted By: <div style="display: inline-block">' . attach_user_link($attachment) . '</div><div title="Uploaded: ' . vbdate($vbulletin->options['dateformat'], $attachment['dateline'], 1) . ' at ' . vbdate($vbulletin->options['timeformat'], $attachment['dateline']) . PHP_EOL . 'Views: ' . $attachment['counter'] . PHP_EOL . 'Size: ' . number_format($attachment['filesize']/1024,1) . ' KB">' . $attachment['filename'] . '</div><a title="Go To Post With Attachment" href="showthread.php?' . $attachment['threadid'] . '-' . str_replace(' ', '-', $attachment['title']) . '&p=' . $attachment['contentid'] . '&viewfull=1#post' . $attachment['contentid'] . '">' . $attachment['title'] . '</a></div>';
}

return $output;

function attach_user_link($user_name)
{
global $vbulletin;
$link = 'member.php?do=getinfo&username=' . $user_name['username'];

if ($user_name['displaygroupid'])
{
$groupid = $user_name['displaygroupid'];
}
else
{
$groupid = $user_name['usergroupid'];
}

$open_tag = $vbulletin->usergroupcache[$groupid]['opentag'];
$close_tag = $vbulletin->usergroupcache[$groupid]['closetag'];
return '<a title="Go To ' . $user_name['username'] . '\'s Profile" href="' . $link . '">' . $open_tag . $user_name['username'] . $close_tag . '</a>';
}

[MarkFL]

Okay, I understand what you did and why it works. I am thinking that a version that would work for any prefix would be:

global $vbulletin, $db;
$number_of_attachments = 5;
$output = '';

$last_attachments = $vbulletin->db->query_read_slave("
SELECT attachment.*, user.*, post.postid, post.threadid, thread.title, filedata.filesize
FROM " . TABLE_PREFIX . "attachment AS attachment
INNER JOIN " . TABLE_PREFIX . "user AS user
ON user.userid = attachment.userid
INNER JOIN " . TABLE_PREFIX . "post AS post
ON post.postid = attachment.contentid
INNER JOIN " . TABLE_PREFIX . "thread AS thread
ON thread.threadid = post.threadid
INNER JOIN " . TABLE_PREFIX . "filedata AS filedata
ON filedata.filedataid = attachment.filedataid
ORDER BY attachment.dateline DESC
LIMIT " . $number_of_attachments
);

$n = 0;
while ($attachment = $db->fetch_array($last_attachments))
{
$output .= '<div';
if ($n++)
{
$output .= ' style="border-top: 1px solid #CCCCCC"';
}
$output .= '>Posted By: <div style="display: inline-block">' . attach_user_link($attachment) . '</div><div title="Uploaded: ' . vbdate($vbulletin->options['dateformat'], $attachment['dateline'], 1) . ' at ' . vbdate($vbulletin->options['timeformat'], $attachment['dateline']) . PHP_EOL . 'Views: ' . $attachment['counter'] . PHP_EOL . 'Size: ' . number_format($attachment['filesize']/1024,1) . ' KB">' . $attachment['filename'] . '</div><a title="Go To Post With Attachment" href="showthread.php?' . $attachment['threadid'] . '-' . str_replace(' ', '-', $attachment['title']) . '&p=' . $attachment['contentid'] . '&viewfull=1#post' . $attachment['contentid'] . '">' . $attachment['title'] . '</a></div>';
}

return $output;

function attach_user_link($user_name)
{
global $vbulletin;
$link = 'member.php?do=getinfo&username=' . $user_name['username'];

if ($user_name['displaygroupid'])
{
$groupid = $user_name['displaygroupid'];
}
else
{
$groupid = $user_name['usergroupid'];
}

$open_tag = $vbulletin->usergroupcache[$groupid]['opentag'];
$close_tag = $vbulletin->usergroupcache[$groupid]['closetag'];
return '<a title="Go To ' . $user_name['username'] . '\'s Profile" href="' . $link . '">' . $open_tag . $user_name['username'] . $close_tag . '</a>';
}

[Mornagest]

It tried this version and that works perfectly :)

Maybe this trick could be proposed as a small mod or else ? I guess I'm not the only one in the universe to seek this kind of widget...

Thank you once more, Mark !

[MarkFL]

It tried this version and that works perfectly :)

Maybe this trick could be proposed as a small mod or else ? I guess I'm not the only one in the universe to seek this kind of widget...

Thank you once more, Mark !

Yes, I am considering creating a product that will auto-create the forum block, and allow the user to configure the block (# of attachments to show (or all within the last X time periods), the CSS of the block and whatever other options I can think of) from settings in the AdminCP.

If I do, I will list you as the co-author, because it was your idea. :)

[Mornagest]

I would be honored :o though it's not only my idea, it was also members of my forum's !