[ic] Displaying number of rows in a table

[Jon Jensen]

On Tue, 1 Jul 2003, Jerry wrote:

> > I would like to access the number of items in a SQL table.  I haven't  
> > had much luck getting at the results using SQL, and I'm wondering if  
> > there's a simple way to access mv_return_fields after pointing a  
> > [search] tag at one of the tables, or if it's easier to use the [query]  
> > tag.
> > 
> > 	I know I'm doing something wrong , but this is what I've 
> > been trying:
> > ------------------------------------------------------------------------ 
> > -----
> > [query
> > 	sql="select count(*) as requestcount from www_mediarequest"
> > 	type=list
> > 	list=1
> > 	st=db
> > 	]
> > 	[list]
> > 		[sql-param requestcount]
> > 	[/list]
> > [/query]

What you're doing there is right. Options type=list and list=1 are
redundant, but that doesn't matter. If your tables are all in the same 
MySQL database, your query should be working. It's possible that your 
www_mediarequest table is not in the same database as products, or 
whatever the first ProductFiles table is. In that case you may need to add 
table=www_mediarequest to the [query] tag.

> A little perl:
> 
>     [perl tables="products"]
>        my $results = $Tag->query({
> 	   type => 'row_count',
> 	    sql => "select sku from products",
>     });
> 	return $results;
> 	[/perl]	
> 
> or even easier with the query tag:
> 
>  [query
>  	sql="select sku from products"
>  	type=row_count
>  	st=db
>  	]
>  [/query]	

Both of those are less optimal ways to go about it if you have an SQL
database, because Interchange has to read in every row in the table, and
then count how many it retrieved. If your table is large, that will be 
slow, and in any case, the SQL database knows the answer to the row count 
almost instantaneously, so why not just ask it? So I'd stick with your 
original query and figure out what's wrong.

Jon