SimplePortal

Customization => Custom Coding => Topic started by: transparencia on December 11, 2009, 01:17:32 PM

Title: Choose specific boards to fetch attachments from on Recent Attachments block
Post by: transparencia on December 11, 2009, 01:17:32 PM

Hi there,

I want the block to fetch attachments only for the specific boards I chose.

How I can I change the query?

Code: [Select]

$request = db_query("
SELECT
att.ID_ATTACH, att.ID_MSG, att.filename, IFNULL(att.size, 0) AS filesize, att.downloads, mem.ID_MEMBER,
IFNULL(mem.realName, m.posterName) AS posterName, m.ID_TOPIC, m.subject, t.ID_BOARD, m.posterTime,
att.width, att.height" . (empty($modSettings['attachmentShowImages']) || empty($modSettings['attachmentThumbnails']) ? "" : ", IFNULL(thumb.ID_ATTACH, 0) AS id_thumb, thumb.width AS thumb_width, thumb.height AS thumb_height") . "
FROM {$db_prefix}attachments AS att
INNER JOIN {$db_prefix}messages AS m ON (m.ID_MSG = att.ID_MSG)
INNER JOIN {$db_prefix}topics AS t ON (t.ID_TOPIC = m.ID_TOPIC)
LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)" . (empty($modSettings['attachmentShowImages']) || empty($modSettings['attachmentThumbnails']) ? "" : "
LEFT JOIN {$db_prefix}attachments AS thumb ON (thumb.ID_ATTACH = att.ID_THUMB)") . "
LEFT JOIN {$db_prefix}boards AS b ON (b.ID_BOARD = t.ID_BOARD)
WHERE att.attachmentType = 0
AND $user_info[query_see_board]
$boards
ORDER BY att.ID_ATTACH DESC
LIMIT $limit", __FILE__, __LINE__);
Thanks for reading!

Title: Re: Choose specific boards to fetch attachments from on Recent Attachments block
Post by: Nathaniel on December 11, 2009, 07:00:30 PM

Try the query below, you will need to change the $allowed_boards array as required for the different boards that you want to be shown.

Code: [Select]

$allowed_boards = array(1, 2, 3, 4);
$request = db_query("
SELECT
att.ID_ATTACH, att.ID_MSG, att.filename, IFNULL(att.size, 0) AS filesize, att.downloads, mem.ID_MEMBER,
IFNULL(mem.realName, m.posterName) AS posterName, m.ID_TOPIC, m.subject, t.ID_BOARD, m.posterTime,
att.width, att.height" . (empty($modSettings['attachmentShowImages']) || empty($modSettings['attachmentThumbnails']) ? "" : ", IFNULL(thumb.ID_ATTACH, 0) AS id_thumb, thumb.width AS thumb_width, thumb.height AS thumb_height") . "
FROM {$db_prefix}attachments AS att
INNER JOIN {$db_prefix}messages AS m ON (m.ID_MSG = att.ID_MSG)
INNER JOIN {$db_prefix}topics AS t ON (t.ID_TOPIC = m.ID_TOPIC)
LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)" . (empty($modSettings['attachmentShowImages']) || empty($modSettings['attachmentThumbnails']) ? "" : "
LEFT JOIN {$db_prefix}attachments AS thumb ON (thumb.ID_ATTACH = att.ID_THUMB)") . "
LEFT JOIN {$db_prefix}boards AS b ON (b.ID_BOARD = t.ID_BOARD)
WHERE att.attachmentType = 0
AND $user_info[query_see_board]
$boards
AND t.ID_BOARD IN (" . explode(',', $allowed_boards) . ")
ORDER BY att.ID_ATTACH DESC
LIMIT $limit", __FILE__, __LINE__);

Title: Re: Choose specific boards to fetch attachments from on Recent Attachments block
Post by: transparencia on December 11, 2009, 09:02:07 PM

That doesn't work, it doesn't return an error but loads forever (time out).

Title: Re: Choose specific boards to fetch attachments from on Recent Attachments block
Post by: ccbtimewiz on December 11, 2009, 10:33:09 PM

Nat, why are you not using IN_SET?