If it helps for comparison, here's my code (LSQL 1.11 stable):
# --------------------------------------------------------
# This routine builds all the category pages. Each category uses
# the same template which is defined in &site_html_category.
#
my ($s, $e, $f, $limit, $offset, $get_related, $subcat_info, $get_links, $get_alt);
print "Building category pages ... \n\n";
$s = time();
$limit = 100; $offset = 0;
$subcat_info = $CATDB->prepare ( qq!
SELECT Category.*
FROM Category, CategoryHierarchy
WHERE CategoryHierarchy.CategoryID = ? AND
CategoryHierarchy.Depth = 1 AND
CategoryHierarchy.SubCategoryID = Category.ID
!) or die "Can't prepare: $DBI::errstr";
$get_related = $LINKDB->prepare (" SELECT Category.Name FROM CategoryRelations, Category WHERE CategoryRelations.CategoryID = ? AND CategoryRelations.RelatedID = Category.ID");
$get_links = $LINKDB->prepare (" SELECT * FROM Links WHERE CategoryID = ? ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");
$get_alt = $LINKDB->prepare (" SELECT l.* FROM Links as l, CategoryAlternates as c WHERE l.ID = c.LinkID AND c.CategoryID = ? ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");
my ($categories_r, $links_r, $category_r, $subcategory_r, $alt_r, $total_links);
my ($directory, $url, $numlinks, $page_num, $tmp, $name, %OUT);
while (1) {
$categories_r = $CATDB->query ( { ID => '*', mh => $limit, nh => $offset+1, sb => 'Name', so => 'ASC', tb => 0 });
last unless ($categories_r and @{$categories_r} > 0);
foreach $category_r (@{$categories_r}) {
# Get the subcategory info and store as a ref to a list of array refs.
$category_r = $CATDB->array_to_hash ($category_r);
$subcat_info->execute($category_r->{'ID'});
$subcategory_r = undef;
if ($subcat_info->rows()) {
$tmp = $subcat_info->fetchall_arrayref();
foreach my $cat (@{$tmp}) { $subcategory_r->{${$cat}[1]} = $CATDB->array_to_hash($cat) }
}Wasn't sure if there may be a slight version difference, maybe not?
All the best
Shaun
Code:
sub build_category_pages { # --------------------------------------------------------
# This routine builds all the category pages. Each category uses
# the same template which is defined in &site_html_category.
#
my ($s, $e, $f, $limit, $offset, $get_related, $subcat_info, $get_links, $get_alt);
print "Building category pages ... \n\n";
$s = time();
$limit = 100; $offset = 0;
$subcat_info = $CATDB->prepare ( qq!
SELECT Category.*
FROM Category, CategoryHierarchy
WHERE CategoryHierarchy.CategoryID = ? AND
CategoryHierarchy.Depth = 1 AND
CategoryHierarchy.SubCategoryID = Category.ID
!) or die "Can't prepare: $DBI::errstr";
$get_related = $LINKDB->prepare (" SELECT Category.Name FROM CategoryRelations, Category WHERE CategoryRelations.CategoryID = ? AND CategoryRelations.RelatedID = Category.ID");
$get_links = $LINKDB->prepare (" SELECT * FROM Links WHERE CategoryID = ? ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");
$get_alt = $LINKDB->prepare (" SELECT l.* FROM Links as l, CategoryAlternates as c WHERE l.ID = c.LinkID AND c.CategoryID = ? ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");
my ($categories_r, $links_r, $category_r, $subcategory_r, $alt_r, $total_links);
my ($directory, $url, $numlinks, $page_num, $tmp, $name, %OUT);
while (1) {
$categories_r = $CATDB->query ( { ID => '*', mh => $limit, nh => $offset+1, sb => 'Name', so => 'ASC', tb => 0 });
last unless ($categories_r and @{$categories_r} > 0);
foreach $category_r (@{$categories_r}) {
# Get the subcategory info and store as a ref to a list of array refs.
$category_r = $CATDB->array_to_hash ($category_r);
$subcat_info->execute($category_r->{'ID'});
$subcategory_r = undef;
if ($subcat_info->rows()) {
$tmp = $subcat_info->fetchall_arrayref();
foreach my $cat (@{$tmp}) { $subcategory_r->{${$cat}[1]} = $CATDB->array_to_hash($cat) }
}
All the best
Shaun