News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

Can't seem to get this left join limit one.

Started by nend, October 06, 2015, 02:53:03 AM

Previous topic - Next topic

nend

Ok, so I have this query
$request = $smcFunc['db_query']('substring', '
SELECT
m.poster_time, m.subject, m.id_topic, m.id_member, m.id_msg, b.thumb_board, b.id_board, b.name AS board_name, t.num_replies, t.num_views,
IFNULL(mem.real_name, m.poster_name) AS poster_name, mem.avatar, ' . ($user_info['is_guest'] ? '1 AS is_read, 0 AS new_from' : '
IFNULL(lt.id_msg, IFNULL(lmr.id_msg, 0)) >= m.id_msg_modified AS is_read,
IFNULL(lt.id_msg, IFNULL(lmr.id_msg, -1)) + 1 AS new_from') . ', m.body AS body, m.smileys_enabled, m.icon,
FROM {db_prefix}topics AS t
INNER JOIN {db_prefix}messages AS m ON (m.id_msg = t.id_first_msg)
INNER JOIN {db_prefix}boards AS b ON (b.id_board = t.id_board)
LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = m.id_member)' . (!$user_info['is_guest'] ? '
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:current_member})
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = b.id_board AND lmr.id_member = {int:current_member})' : '') . '
WHERE t.id_last_msg >= {int:min_message_id}
AND {query_wanna_see_board}' . ($modSettings['postmod_active'] ? '
AND t.approved = {int:is_approved}
AND m.approved = {int:is_approved}' : '') . '
ORDER BY t.id_first_msg DESC
LIMIT {int:numrecent}' ,
array(
'current_member' => $user_info['id'],
'include_boards' => empty($include_boards) ? '' : $include_boards,
'exclude_boards' => empty($exclude_boards) ? '' : $exclude_boards,
'min_message_id' => $modSettings['maxMsgID'] - 35 * min($num_recent, 5),
'is_approved' => 1,
'numrecent' => $num_recent,
)
);



I add this because I want the thumbnail id of the first attachment with a thumbnail if there is one.
LEFT JOIN (SELECT id_msg, id_thumb FROM {db_prefix}attachments ORDER BY id_attach WHERE id_thumb != 0 LIMIT 1) AS a ON a.id_msg = m.id_msg

and get this
QuoteAn Error Has Occurred!
Hacking attempt...

I know I been away from coding for around 2 years and may be a little rusty, but where am I going wrong here. I get no mysql errors just SMF throwing a hacking attempt.  ???

This has me stumped, the rest of information I got but can't do a selective left join, come on.  :'(


nend

Thanks Shambles that did the trick, I disabled the check before the query and enabled it back after.

Good thing I am caching this crazy query.

Advertisement: