The Arcive of vBulletin Modifications Site.

Mornagest · #1 12-21-2015, 05:32 PM

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 :

Code:

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 · #2 12-22-2015, 04:39 AM

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

This is the PHP code:

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:

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 · #3 12-22-2015, 11:16 AM

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 !

Code:

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 · #4 12-22-2015, 11:40 AM

Try this:

PHP 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 . "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 · #5 12-22-2015, 05:29 PM

Hello Mark, and thank you

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

Code:

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 :

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 · #6 12-22-2015, 05:38 PM

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

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 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 · #7 12-22-2015, 05:44 PM

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 · #8 12-22-2015, 05:48 PM

Quote:

Originally Posted by 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 !

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 · #9 12-22-2015, 06:18 PM

I would be honored

though it's not only my idea, it was also members of my forum's !

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.06051 seconds Memory Usage 4,828KB Queries Executed 12 (?)
More Information
Template Usage: (1)SHOWTHREAD (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)ad_showthread_beforeqr (1)ad_showthread_firstpost (1)ad_showthread_firstpost_sig (1)ad_showthread_firstpost_start (4)bbcode_code (3)bbcode_php (1)bbcode_quote (1)footer (1)forumjump (1)forumrules (1)gobutton (1)header (1)headinclude (1)navbar (3)navbar_link (120)option (9)post_thanks_box (2)post_thanks_box_bit (9)post_thanks_button (1)post_thanks_javascript (1)post_thanks_navbar_search (2)post_thanks_postbit (9)post_thanks_postbit_info (9)postbit (2)postbit_attachment (9)postbit_onlinestatus (9)postbit_wrapper (1)spacer_close (1)spacer_open (1)tagbit_wrapper Phrase Groups Available: global inlinemod postbit posting reputationlevel showthread	Included Files: ./showthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/functions_bigthree.php ./includes/class_postbit.php ./includes/class_bbcode.php ./includes/functions_reputation.php ./includes/functions_post_thanks.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete showthread_start showthread_getinfo forumjump showthread_post_start showthread_query_postids showthread_query bbcode_fetch_tags bbcode_create showthread_postbit_create postbit_factory postbit_display_start post_thanks_function_post_thanks_off_start post_thanks_function_post_thanks_off_end post_thanks_function_fetch_thanks_start fetch_musername post_thanks_function_fetch_thanks_end post_thanks_function_thanked_already_start post_thanks_function_thanked_already_end postbit_imicons bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete postbit_display_complete post_thanks_function_can_thank_this_post_start postbit_attachment post_thanks_function_fetch_thanks_bit_start post_thanks_function_show_thanks_date_start post_thanks_function_show_thanks_date_end post_thanks_function_fetch_thanks_bit_end post_thanks_function_fetch_post_thanks_template_start post_thanks_function_fetch_post_thanks_template_end tag_fetchbit_complete forumrules navbits navbits_complete showthread_complete
Messages:

Благодарность от:
MarkFL

Благодарность от:
Mornagest