Crazy OFFSET in SQL statement with sql_query_limit

[olsserik]

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?

[AmigoJack]

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 */

[olsserik]

Ah, that sorted it out, thank you!