Get all boards that have messages with image attachments

[moyack]

Hello SMF community:

I've been working in a kind of mod where I can show all the attachments a forum has, as a way of a media gallery.

Now I have this requirement: how to get all the boards that effectively has topics with messages that have atttachments on them?

I've tried this SQL query but it's not working well:

$request = $smcFunc['db_query']('', '
SELECT  DISTINCT

b.name, m.poster_time, m.modified_time, b.num_topics, b.num_posts, b.id_board,' . (!$user_info['is_guest'] ? ' 1 AS is_read' : '

(IFNULL(lb.id_msg, 0) >= b.id_last_msg) AS is_read') . ',

CASE WHEN b.redirect != {string:blank_string} THEN 1 ELSE 0 END AS is_redirect
FROM {db_prefix}boards AS b
    INNER JOIN {db_prefix}messages AS m ON (m.id_msg = b.id_msg_updated)
    LEFT JOIN {db_prefix}attachments AS a ON (m.id_msg = a.id_msg AND a.width > 0 AND a.height > 0 AND a.attachment_type != 3)

LEFT JOIN {db_prefix}log_boards AS lb ON (lb.id_board = b.id_board AND lb.id_member = {int:current_member})
WHERE {query_wanna_see_board}',
array(

'current_member' => $user_info['id'],

'blank_string' => '',
);
(Modified from mkress sitemap mod)

I've read that I would need a subquery, but my attemps doesnt¿ work.

I really appreciate your help.

[moyack]

Well, I think I've got something:

Code Select Expand

SELECT DISTINCT
b.name, ml.poster_time, ml.modified_time, b.num_topics, b.num_posts, b.id_board
FROM smf_boards AS b
INNER JOIN (SELECT DISTINCT
    t.id_topic, t.id_board
    FROM smf_topics AS t
    INNER JOIN (SELECT DISTINCT
            m.id_msg, m.id_topic
        FROM smf_messages AS m
            INNER JOIN smf_attachments AS a ON (m.id_msg = a.id_msg)
        WHERE a.width > 0 AND a.height > 0 and a.attachment_type != 3) AS m
    WHERE m.id_topic = t.id_topic) AS t
    INNER JOIN smf_messages AS ml ON (ml.id_msg = b.id_msg_updated)
WHERE b.id_board = t.id_board

With MySQL Admin it works perfectly, but when I send this code into a $request = $smcFunc['db_query'], it doesn't work.

Code sent into SMF query function:

Code Select Expand

SELECT DISTINCT
b.name, ml.poster_time, ml.modified_time, b.num_topics, b.num_posts, b.id_board, 0 AS is_redirect
FROM {db_prefix}boards AS b
INNER JOIN (SELECT DISTINCT
    t.id_topic, t.id_board
    FROM {db_prefix}topics AS t
    INNER JOIN (SELECT DISTINCT
            m.id_msg, m.id_topic
        FROM {db_prefix}messages AS m
            INNER JOIN {db_prefix}attachments AS a ON (m.id_msg = a.id_msg)
        WHERE a.width > 0 AND a.height > 0 and a.attachment_type != 3) AS m
    WHERE m.id_topic = t.id_topic) AS t
    INNER JOIN {db_prefix}messages AS ml ON (ml.id_msg = b.id_msg_updated)
WHERE b.id_board = t.id_board
Probably a typo??

[Sorck]

$smcFunc won't allow sub queries to run (this is a security measure in case somebody finds a way to do SQL injection). For more info see Sources/Subs-Db-mysql.php lines 369 to 382 in SMF 2.0.15.

The following will do the lookup without the sub queries:

Code Select Expand


SELECT b.name, ml.poster_time, ml.modified_time, b.num_topics, b.num_posts, b.id_board, b.redirect
FROM {db_prefix}boards AS b
INNER JOIN {db_prefix}topics AS t
    ON t.id_board = b.id_board
INNER JOIN {db_prefix}messages AS m
    ON m.id_topic = t.id_topic
INNER JOIN {db_prefix}attachments AS a
    ON m.id_msg = a.id_msg
INNER JOIN {db_prefix}messages AS ml
    ON ml.id_msg = b.id_msg_updated
WHERE a.width > 0
     AND a.height > 0
     AND a.attachment_type != 3
GROUP BY b.id_board


This query will be fine for a small attachments table but you might need to add a new index to the table if dealing with lots of data.

[vbgamer45]

If you try this before your sql call in SMF it will allow you to run subqueries

Code Select Expand


$modSettings['disableQueryCheck'] = true;


[moyack]

If you try this before your sql call in SMF it will allow you to run subqueries

Code Select Expand


$modSettings['disableQueryCheck'] = true;


Thank you very much!!

Works like a charm.

$smcFunc won't allow sub queries to run (this is a security measure in case somebody finds a way to do SQL injection). For more info see Sources/Subs-Db-mysql.php lines 369 to 382 in SMF 2.0.15.

Hmmm, but if I activate this, do the query and then I unset the variable, can I solve the security issue?

Topic solved.