Counter in modified Recent.php bugged

Started by themavesite, May 06, 2017, 10:52:17 AM

Previous topic - Next topic

themavesite

Hello, I modified my Recent.php page to show only the latest TOPICS instead of POSTS.

What I did:


$request = $smcFunc['db_query']('', '
SELECT
m.id_msg, m.subject, m.smileys_enabled, m.poster_time, m.body, m.id_topic, t.id_board, b.id_cat,
b.name AS bname, c.name AS cname, t.num_replies, m.id_member, m2.id_member AS id_first_member,
IFNULL(mem2.real_name, m2.poster_name) AS first_poster_name, t.id_first_msg,
IFNULL(mem.real_name, m.poster_name) AS poster_name, t.id_last_msg
FROM {db_prefix}messages AS m
INNER JOIN {db_prefix}topics AS t ON (t.id_topic = m.id_topic)
INNER JOIN {db_prefix}boards AS b ON (b.id_board = t.id_board)
INNER JOIN {db_prefix}categories AS c ON (c.id_cat = b.id_cat)
INNER JOIN {db_prefix}messages AS m2 ON (m2.id_msg = t.id_first_msg)
LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = m.id_member)
LEFT JOIN {db_prefix}members AS mem2 ON (mem2.id_member = m2.id_member)
WHERE m.id_msg IN ({array_int:message_list}) AND m.id_msg = t.id_first_msg
ORDER BY m.id_msg DESC
LIMIT ' . count($messages),
array(
'message_list' => $messages,
)
);


I only added the AND m.id_msg = t.id_first_msg

-> There should be 10 items per page, but it only shows 4 on the first page.

Live example: http://forums.themavesite.com/index.php?action=recenttopics

How do I change Recent.php to show 10 TOPICS/page?
TMS Forums
Since 2008 and still going strong! Join today! http://forums.themavesite.com/index.php

shawnb61

First thought is not to set the limit based on a count of messages.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Arantor

If that were the problem, I'd agree - except it isn't.

The list of relevant messages has already been processed before that query is even run - $messages already contains the relevant ids. You need to modify further up the code to build $messages itself.

Kindred

Or, you can use the existing ssi_recentTopics which has already built the function that you are trying to rebuild
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

Arantor

It doesn't come with a pre-made page for it though.

themavesite

Quote from: Arantor on May 06, 2017, 12:37:18 PM
If that were the problem, I'd agree - except it isn't.

The list of relevant messages has already been processed before that query is even run - $messages already contains the relevant ids. You need to modify further up the code to build $messages itself.

Any suggestions in how to do this exactly?
I guess the part that needs to be modified is this one?

$messages = array();
while ($row = $smcFunc['db_fetch_assoc']($request))
$messages[] = $row['id_msg'];
$smcFunc['db_free_result']($request);
if (!empty($cache_results))
cache_put_data($key, $messages, 120);


TMS Forums
Since 2008 and still going strong! Join today! http://forums.themavesite.com/index.php

Arantor

No, that just fetches the results from the database query. You need to modify the lines immediately before that where the query itself fetches the data - to make roughly the same change you wanted to make in the first place.

themavesite

Quote from: Arantor on May 07, 2017, 07:43:22 AM
No, that just fetches the results from the database query. You need to modify the lines immediately before that where the query itself fetches the data - to make roughly the same change you wanted to make in the first place.

I guess you mean this bit?

// Find the 10 most recent messages they can *view*.
// !!!SLOW This query is really slow still, probably?
$request = $smcFunc['db_query']('', '
SELECT m.id_msg
FROM {db_prefix}messages AS m
INNER JOIN {db_prefix}boards AS b ON (b.id_board = m.id_board)
WHERE ' . $query_this_board . '
AND m.approved = {int:is_approved}
ORDER BY m.id_msg DESC
LIMIT {int:offset}, {int:limit}',
array_merge($query_parameters, array(
'is_approved' => 1,
'offset' => $_REQUEST['start'],
'limit' => 10,
))
);


EDIT: full code = https://pastebin.com/iTMmzQY8
TMS Forums
Since 2008 and still going strong! Join today! http://forums.themavesite.com/index.php

themavesite

So I ended up turning the query above into the same query as the other one:

$request = $smcFunc['db_query']('', '
SELECT m.id_msg, t.id_first_msg
FROM {db_prefix}messages AS m
INNER JOIN smf_topics AS t ON (t.id_topic = m.id_topic)
INNER JOIN smf_boards AS b ON (b.id_board = t.id_board)
INNER JOIN smf_categories AS c ON (c.id_cat = b.id_cat)
INNER JOIN smf_messages AS m2 ON (m2.id_msg = t.id_first_msg)
LEFT JOIN smf_members AS mem ON (mem.id_member = m.id_member)
LEFT JOIN smf_members AS mem2 ON (mem2.id_member = m2.id_member)
WHERE ' . $query_this_board . '
AND m.approved = {int:is_approved}
AND m.id_msg = t.id_first_msg
ORDER BY m.id_msg DESC
LIMIT {int:offset}, {int:limit}',
array_merge($query_parameters, array(
'is_approved' => 1,
'offset' => $_REQUEST['start'],
'limit' => 10,
))
);


And this seems to work  ;D
http://forums.themavesite.com/index.php?action=recenttopics

Can anyone tell me if this is a "good" way to do this? Then I can mark this topic as solved.

My sql knowledge is limited but I feel like I'm doing an overkill of joins for this result?
TMS Forums
Since 2008 and still going strong! Join today! http://forums.themavesite.com/index.php

Advertisement: