This is my select:

{{$limit 	= 20;}}
{{$page 	= (isset( \IPS\Request::i()->page ) and (intval( \IPS\Request::i()->page ) > 0)) ? \IPS\Request::i()->page : 1;}}
{{$start 	= ( $limit * ( $page - 1 ) );}}
{{$select = \IPS\Db::i()->select( 'cms_custom_database_8.field_68 as nome_do_suplemento, cms_custom_database_8.record_image_thumb as imagem, cms_custom_database_8.primary_id_field as id_do_suplementos, cms_custom_database_8.member_id as autor, cms_custom_database_8.record_updated as data_de_atualizacao, cms_custom_database_8.record_comments as numero_de_comentarios', 'cms_custom_database_8', array( 'field_140=?', $record->primary_id_field ), 'cms_custom_database_8.field_68 ASC', array( $start, $limit ), NULL, NULL, \IPS\Db::SELECT_SQL_CALC_FOUND_ROWS );}}
{{$total = count( $select );}}

Total should be 64, but it gives me 20, the number of rows used as query LIMIT.

Edited December 2, 20204 yr by sobrenome

What happens if you echo $select->query, and copy the query into phpmyadmin or some other mysql console?

Does the query look like you think it should?  Often times I've used this method to figure out a syntax error in the php version of the query.

It's no longer supported. Do a separate count query:

{{$total = \IPS\Db::i()->select( 'COUNT(*)', 'cms_custom_database_8', array( 'field_140=?', $record->primary_id_field ) )->first();}}

 

 

It's no longer supported. Do a separate count query:

{{$total = \IPS\Db::i()->select( 'COUNT(*)', 'cms_custom_database_8', array( 'field_140=?', $record->primary_id_field ) )->first();}}

 

Perfect! Thanks!

Please update the developer docs:

https://invisioncommunity.com/developers/docs/fundamentals/accessing-the-database-r166/

I didn't see SELECT_SQL_CALC_FOUND_ROWS referenced there still. I believe the documentation was already updated.

It is updated now! Thanks @bfarber!

I changed it few hours ago.
Didn't want to mention it, but now I have, just to make sure that nobody thinks that he's crazy:D

 

I changed it few hours ago.
Didn't want to mention it, but now I have, just to make sure that nobody thinks that he's crazy:D

Shhhh