Display a row COUNT from the database in a theme template

[-FP]

Hi,

So basically I'm using the chatbox application, and I want to add to one of its templates the amount of messages that have been sent.

I guess I have to use \IPS\Db::i()->, but I need to count the amount of rows in a column of the chatbox table and then display it. Any ideas?

[-FP]

Bump, anyone?

[-FP]

Bumping this again...

[-FP]

Found a way to display it, but I think it's kinda cheesy... anyway this does what I needed:

{{$rows = 0;}}
{{$chatbox_rows = \IPS\Db::i()->select( 'chat', 'bimchatbox_chat' );}}
{{foreach $chatbox_rows as $row}}
	{{$rows = $rows + 1;}}
{{endforeach}}
{$rows}

 

Now onto the next problem, I want to grab the user IDs column, and find who has the most messages...

[-FP]

Made it more simple...

{{$chatbox_rows = \IPS\Db::i()->select( 'user', 'bimchatbox_chat' );}}
{{$rows = count($chatbox_rows);}}
{$rows}

 

Still trying to figure out how to find the most repeated values in $chatbox_rows.

{{array_count_values($chatbox_rows);}}

Throws an error.

[-FP]

I need to convert this query to some invision template logic...

SELECT user, COUNT(*) as Messages
FROM bimchatbox_chat 
GROUP BY user
ORDER BY COUNT(*) DESC
LIMIT 5;

I guess so far I only have the select part this this:

{{$chatbox_rows = \IPS\Db::i()->select( 'user', 'bimchatbox_chat' );}}

Been trying to do this for hours now lol... 

[Daniel F]

{{$chatbox_rows = \IPS\Db::i()->select( 'COUNT(*) as count', 'bimchatbox_chat' )->first(); }}

 

[-FP]

17 minutes ago, Daniel F said:

{{$chatbox_rows = \IPS\Db::i()->select( 'COUNT(*) as count', 'bimchatbox_chat' )->first(); }}

 

Thanks, that's to count the total rows I see. Can it be modified to return the value that is repeated the most in a column? Like I think it needs the GROUP BY and ORDER BY statements.

[jeece76]

I'm trying to do the same thing but i can't show all the results from the database. 

{{$mangas = \IPS\Db::i()->select( 'field_30', 'cms_custom_database_6', 'field_33 = 1' )->first();}}

How i can do for print full results and not only the first ?

[-FP]

@jeece76

I think this might work for you.

//Store all mangas
{{$mangas = \IPS\Db::i()->select( 'field_30', 'cms_custom_database_6', 'field_33 = 1' );}}

//Loop through all of them
{{foreach $mangas as $manga}}
	{$manga}
{{endforeach}}

 

[Daniel F]

11 hours ago, -FP said:

Thanks, that's to count the total rows I see. Can it be modified to return the value that is repeated the most in a column? Like I think it needs the GROUP BY and ORDER BY statements.

Yes, take a look at the method doc for the other params

	/**
	 * Build SELECT statement
	 *
	 * @param	array|string		$columns	The columns (as an array) to select or an expression
	 * @param	array|string		$table		The table to select from. Either (string) table_name or (array) ( name, alias ) or \IPS\Db\Select object
	 * @param	array|string|NULL	$where		WHERE clause - see \IPS\Db::compileWhereClause() for details
	 * @param	string|NULL			$order		ORDER BY clause
	 * @param	array|int			$limit		Rows to fetch or array( offset, limit )
	 * @param	string|NULL|array	$group		Column(s) to GROUP BY
	 * @param	array|string|NULL	$having		HAVING clause (same format as WHERE clause)
	 * @param	int					$flags		Bitwise flags
	 *	@li	\IPS\Db::SELECT_DISTINCT				Will use SELECT DISTINCT
	 *	@li	\IPS\Db::SELECT_SQL_CALC_FOUND_ROWS		Will add SQL_CALC_FOUND_ROWS
	 *	@li	\IPS\Db::SELECT_MULTIDIMENSIONAL_JOINS	Will return the result as a multidimensional array, with each joined table separately
	 * @return	\IPS\Db\Select
	 *
	 */
	public function select( $columns=NULL, $table, $where=NULL, $order=NULL, $limit=NULL, $group=NULL, $having=NULL, $flags=0 )

 

[-FP]

Thanks! Got it.

{{$top_shouters = \IPS\Db::i()->select( 'user', 'bimchatbox_chat', '', 'COUNT(*) DESC', '5', 'user' ); }}

 

[jeece76]

@-FP Thanks! But how to do for an entire table and sort results by fields like records in page (with conditions like "manga->field_33('1')") ?

& how I can return id from records? "$record->id" didn't work :/