Crazy OFFSET in SQL statement with sql_query_limit

Discussion forum for MOD Writers regarding MOD Development.
Locked
olsserik
Registered User
Posts: 155
Joined: Tue Aug 21, 2007 6:18 am

Crazy OFFSET in SQL statement with sql_query_limit

Post by olsserik » Wed Aug 26, 2015 11:58 am

Hi,
Im trying to build a "hot new threads" function that basically sorts out the 100 newest topics and sort in topic_views DESC, limit 5.

That works fine but not when Im trying build it into

Code: Select all

sql_query_limit
Here are the query:

Code: Select all

SELECT t.*, MAX(p.post_id) AS last_post FROM phpbb3_posts AS p, phpbb3_topics AS t WHERE t.forum_id IN (1, 2, 3, 4, 5, 6, 8, 9, 10, 11, 12, 13, 14, 15, 17, 18, 19, 20, 21, 22, 24, 25, 26, 27, 28, 29, 30, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 106, 107, 108, 109, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125) AND p.topic_id = t.topic_id AND p.forum_id = t.forum_id GROUP BY p.topic_id ORDER BY t.topic_views DESC LIMIT 100, 5
If I remove the OFFSET in sql_query_limit the query works fine, however it displays most viewed topics from the whole database, if I put in OFFSET it shows a top 5 list taken from approx the middle of the db.

I have this as reference:

Code: Select all

$start = request_var('start', 0);

$sql = 'SELECT post_text
    FROM ' . POSTS_TABLE . '
    WHERE forum_id = ' . (int) $forum_id;
// return 100 rows
$result = $db->sql_query_limit($sql, 100, $start);
Taken from: https://wiki.phpbb.com/Dbal.sql_query_limit

What am I doing wrong?

User avatar
AmigoJack
Registered User
Posts: 5602
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: Crazy OFFSET in SQL statement with sql_query_limit

Post by AmigoJack » Fri Aug 28, 2015 9:27 am

olsserik wrote:

Code: Select all

LIMIT 100, 5

Code: Select all

$db->sql_query_limit($sql, 100, $start); 
You mixed it up: look up how LIMIT works in MySQL (http://dev.mysql.com/doc/refman/5.6/en/ ... 2457390048), because you're selecting 5 datasets from offset 100, not the other way around. phpBB's sql_query_limit() never switches arguments: second one is always the count and third one is always the offset - in contrast to MySQL, where the first argument is the count (when used alone) or the offset (when a second one follows).

Either use

Code: Select all

$db-> sql_query_limit( $sql, 5, $start );  // 5 datasets at offset $start  
or

Code: Select all

LIMIT 5, 100   /* 100 datasets at offset 5 */
The worst thing about censorship is ███████████
Affin wrote:
Tue Nov 20, 2018 9:51 am
The problem is probably not my English but you do not want to understand correctly.
...
We will not come anybody anyway, nevertheless, it's best to shit this.

olsserik
Registered User
Posts: 155
Joined: Tue Aug 21, 2007 6:18 am

Re: Crazy OFFSET in SQL statement with sql_query_limit

Post by olsserik » Fri Aug 28, 2015 9:30 am

Ah, that sorted it out, thank you!

Locked

Return to “[3.0.x] MOD Writers Discussion”