Gossamer Forum
Home : Products : DBMan SQL : Discussion :

How many books in each category ?

Quote Reply
How many books in each category ?
Hi all !

I attempt to create simple (but hard for me :) global, which will calculate quantity of books in each category in categories list (bookstore template in DBMan SQL).

Something like this:
-------------------------
Fantasy - 156
Detective - 321
Psychology - 527


Now (after three days, about a hundred read threads and repairing my PC), I have two parts of this:

First, the global that makes categories list:
----------------------------------------------------
sub {
my $tags = shift;
my $table = $DB->table('Category');
my $sth = $table->select (['CatName','CatID']);
my $output;
my $num;
while (my $row = $sth->fetchrow_hashref){
$num = $num + 1;
($output .= "<font face=\"verdana,arial,helvetica\" size=\"-2\"><a href=db.cgi?&sid=$tags->{session_id}&db=Book&do=search_results&CatID=$row->{CatID}>$row->{CatName}</a></font><br><br>\n");
}
return $output;
}


Second, the global that counts number of books in separate category:
-----------------------------------------------------------------------------------
sub {
$DB->table('Book')->select('count( DISTINCT(Title) )' =>GT::SQL::Condition->new('CatID', 'LIKE', '1'))->fetchrow
}


I've tried thousands different methods, attempting to connect these parts, but in vain :(
I've read the GT Module Documentation (Tables, Conditions and other useful stuff) five times! Now I feel myself almost like a real programmer, but all the same I can't understand, HOW it should be made.
Whether somebody can give me small advice on this problem?

Regards,

Paul

Last edited by:

Skiff: Apr 24, 2003, 1:28 PM
Quote Reply
Re: [Skiff] How many books in each category ? In reply to
Hi,

Have a look at the template global below:

sub {
my $db = $DB->table('Category', 'Book');
$db->select_options('GROUP BY CatID, CatName');
my $sth = $db->select(['CatID', 'CatName', 'Count(BookID) as num_books']);

my @output;
while (my $rs = $sth->fetchrow_hashref) {
push @output, $rs;
}
return { loop_results => \@output };
}

And then, change your template like:

<%global_name%>
<%loop loop_results%>
<%CatName%> - <%num_books%><BR>
<%endloop%>

Ensure that the Book is related to Category table ( you can create a relationship by using the Relationship feature).

Hope that helps.

TheStone.

B.
Quote Reply
Re: [TheStone] How many books in each category ? In reply to
Book table (related table) is related to Category table (primary table). Primary key: CatID. Foreign key: CatID.
ISBN column is the Primary Key for table "Book", so, I've replaced Count(BookID) with Count(ISBN), but all the same:

A fatal error has occured:

GT::SQL::Relation (24951): Bad columns / column clash: columns named 'CatID' have been found in current relation, please qualify your expression. at (eval 30) line 4.

Please enable debugging in setup for more details.

Stack Trace
======================================
Dbsql (24951): GT::Base::error called at GT::SQL::Relation::_complete_name line 1044 with arguments
(GT::SQL::Relation=HASH(0x856a674), BADCOLS, FATAL, CatID).
Dbsql (24951): GT::SQL::Relation::_complete_name called at /home/d/dlovaya/public_html/cgi-bin/admin/GT/SQL/Relation.pm line 242 with arguments
(GT::SQL::Relation=HASH(0x856a674), CatID).
Dbsql (24951): GT::SQL::Relation::select called at (eval 30) line 4 with arguments
(GT::SQL::Relation=HASH(0x856a674), ARRAY(0x856a77c)).
Dbsql (24951): Dbsql::__ANON__ called at /home/d/dlovaya/public_html/cgi-bin/admin/Dbsql/Home.pm line 1506 with arguments
(HASH(0x850f1e0)).
Dbsql (24951): Dbsql::Home::__ANON__ called at /home/d/dlovaya/public_html/cgi-bin/admin/GT/Template.pm line 841 with arguments
(HASH(0x850f1e0)).
Dbsql (24951): GT::Template::_get_var called at /home/d/dlovaya/public_html/cgi-bin/admin/templates/bookstore/compiled/home.html.compiled line 371 with arguments
(GT::Template=HASH(0x8461ac8), countbook, 0, 1).
Dbsql (24951): GT::Template::parsed_template called at /home/d/dlovaya/public_html/cgi-bin/admin/GT/Template.pm line 600 with arguments
(GT::Template=HASH(0x8461ac8)).
Dbsql (24951): GT::Template::_parse called at /home/d/dlovaya/public_html/cgi-bin/admin/GT/Template.pm line 97 with arguments
(GT::Template=HASH(0x8461ac8), home.html, HASH(0x8461a38)).
Dbsql (24951): GT::Template::parse called at /home/d/dlovaya/public_html/cgi-bin/admin/Dbsql/Home.pm line 123 with arguments
(GT::Template, home.html, HASH(0x8461a2c), HASH(0x8461a38)).
Dbsql (24951): Dbsql::Home::print called at /home/d/dlovaya/public_html/cgi-bin/admin/Dbsql/Home.pm line 83 with arguments
(Dbsql::Home=HASH(0x804c14c), home.html, HASH(0x8461a2c)).
Dbsql (24951): Dbsql::Home::process called at /home/d/dlovaya/public_html/cgi-bin/db.cgi line 29 with arguments
(Dbsql::Home=HASH(0x804c14c), in, GT::CGI=HASH(0x8460660), sql, GT::SQL=HASH(0x8526978), cfg, Dbsql::Config=HASH(0x80652ec)).
Dbsql (24951): main::main called at /home/d/dlovaya/public_html/cgi-bin/db.cgi line 22 with no arguments.

Paul

Last edited by:

Skiff: Apr 25, 2003, 7:17 PM
Quote Reply
Re: [Skiff] How many books in each category ? In reply to
Hi,

Try:

$db->select_options('GROUP BY Category.CatID, Category.CatName');
my $sth = $db->select(['Category.CatID', 'Category.CatName', 'Count(BookID) as num_books']);

istead of:
$db->select_options('GROUP BY CatID, CatName');
my $sth = $db->select(['CatID', 'CatName', 'Count(BookID) as num_books']);

TheStone.

B.
Quote Reply
Re: [TheStone] How many books in each category ? In reply to
It works !! IT WORKS !!!!!!!!
Thank You TheStone !!!

It is TheGreatGlobal, because it allows to present DBMan SQL's database as a classified data (in Links SQL or Yahoo style).

<%countbook%>
<%loop loop_results%>
<a href=db.cgi?&sid=&db=Book&do=search_results&CatID=<%CatID%>><%CatName%></A> (<%num_books%>)<BR>
<%endloop%>

It's DBMan SQL squared ! More power to search and discover an information !

You are TheGreatStone !!! (It's not a joke!)

Yours faithfully,

Paul