Show total number of links and categories

[ilushkin]

Show total number of links and categories in the footer.

[David]

I think this could be done using the code we have in admin:

$stats[0] = $db->GetOne("SELECT COUNT(*) FROM {$tables['link']['name']} WHERE STATUS > 1");
$stats[3] = $db->GetOne("SELECT COUNT(*) FROM {$tables['category']['name']}");

Then somewhere in the footer.tpl (or whereever you want) put:

<div align="center">
{l}Active Links{/l} - {$stats[0]}

{l}Categories{/l} - {$stats[3]}
</div>

I haven't tested this, but I believe it will work.

[Mike77]

David,I placed both codes into main.tpl (one after another) and I got this:

Fatal error: Smarty error: [in main.tpl line 81]: syntax error: unrecognized tag: $tables['link']['name'] (Smarty_Compiler.class.php, line 436) in libs/smarty/Smarty.class.php on line 1088

I replaced my server info with *********.

If I put only the second code then I get only text displayed. $stats variable display nothing.

[yktan]

Hi Mike77, what David posted is almost right. Here's what you should do:

Open index.php
Find:

echo $tpl->fetch('main.tpl', $id);

Add BEFORE:

$stats[0] = $db->GetOne("SELECT COUNT(*) FROM {$tables['link']['name']} WHERE STATUS = 2"); 
$stats[3] = $db->GetOne("SELECT COUNT(*) FROM {$tables['category']['name']}  WHERE STATUS = 2");
$tpl->assign('stats', $stats);

Open main.tpl
Add this whereever you want:

<div align="center"> 
{l}Active Links{/l} - {$stats[0]}
 
{l}Categories{/l} - {$stats[3]} 
</div>

Regards,
York Kie

[Mike77]

Thank you David and yktan,it is working now! Why do we need to insert this at that exact place? How did you know this? I really like to tweak with my website but this just looks so complicated (by this I mean inserting thigns at exactly correct location).

[yktan]

Hi Mike77, it is not necessary to insert the code at the exact place in the php file but it does have to be after the templating engine is initialised. So the easiest way is for me to tell you a place where it will definitely work.

PHP Link Directory is developed using PHP and Smarty Templates, so if you are interested, you can learn both of them and you will understand even more

Regards,
York Kie

[ilushkin]

Originally Posted by yktan

Hi Mike77, what David posted is almost right. Here's what you should do:

Open index.php
Find:

echo $tpl->fetch('main.tpl', $id);

Add BEFORE:

$stats[0] = $db->GetOne("SELECT COUNT(*) FROM {$tables['link']['name']} WHERE STATUS = 2"); 
$stats[3] = $db->GetOne("SELECT COUNT(*) FROM {$tables['category']['name']}  WHERE STATUS = 2");
$tpl->assign('stats', $stats);

Open main.tpl
Add this whereever you want:

<div align="center"> 
{l}Active Links{/l} - {$stats[0]}
 
{l}Categories{/l} - {$stats[3]} 
</div>

Regards,
York Kie

works good. great job. thanks.

[Jim_Westergren]

I have also implemented this and it is actually very nice.

Thanks.

[superlinks]

Is there a possibility to show the total nummer (count) of all hits of the links in de directory?

[yktan]

Hi superlinks, you sure can.

You will need something like the following in index.php

$stats[4] = $db->GetOne("SELECT SUM(HITS) FROM {$tables['link']['name']} WHERE STATUS = 2"); 
$tpl->assign('stats', $stats);

And to display it in main.tpl:

<div align="center"> 
{l}Total Hits for Links{/l} - {$stats[4]}</div>

[superlinks]

thanx, that's it!

[vkaryl]

This is really nifty! Thanks, David and yktan....

Now, anyone have any idea how I would go about making the numbers change depending on the page accessed?

In other words, the main index shows, say, Active Links 128 and Categories 56; then I click on the first category, go to that page, and I'd like to see the numbers refer to specifically THAT category: Active Links 12 and SubCategories 3.... something like that....

[Romario]

Originally Posted by yktan

Hi superlinks, you sure can.

You will need something like the following in index.php

$stats[4] = $db->GetOne("SELECT SUM(HITS) FROM {$tables['link']['name']} WHERE STATUS = 2"); 
$tpl->assign('stats', $stats);

And to display it in main.tpl:

<div align="center"> 
{l}Total Hits for Links{/l} - {$stats[4]}</div>

Mod "Total Hits for Links" dosn't work, why? Script version: RC5.2.

[Romario]

Sorry... It's work... Big Thanks...

[pragent]

where I find:

echo $tpl->fetch('main.tpl', $id);

in my index.php

With me there is not that! :(

[neurosis4u]

open index.php in windows text editor, and try the search funktion of your editor.

in most editors you have a search/replace funktion. but after changing this line dont forget to save it as a .php file

[pragent]

<?php


require_once 'init.php';

$http_status = (isset($_REQUEST['httpstatus']) ? intval($_REQUEST['httpstatus']) : 404);
httpstatus($http_status);

if(empty($_REQUEST['q']) || strlen($_REQUEST['q']) < 2) {
unset($_REQUEST['q']);
}

if(!empty($_SESSION['regular_user_id'])) {
$uid = (!empty($_REQUEST['uid']) && preg_match('!^\d$!', $_REQUEST['uid']) ? intval($_REQUEST['uid']) : 0);
if(!empty($uid) && $uid == $_SESSION['regular_user_id']) {
$user_where = " AND `OWNER_ID` = ".$db->qstr($uid);
}
else {
unset($uid);
}
}

$pg = preg_match("/page-(\d+)\.html/", $_SERVER['REQUEST_URI'], $matches);
$pg = $matches[1];


$_SERVER['REQUEST_URI'] = preg_replace("/page-(\d+)\.html/", "", $_SERVER['REQUEST_URI']);


define('DIR_LPP', 20);
$sort_cols = array ( 'P' => 'PAGERANK', 'H' => 'HITS', 'A' => 'TITLE', 'D' => 'DATE_ADDED');
$sort_ord = array ( 'P' => 'DESC', 'H' => 'DESC', 'A' => 'ASC', 'D' => 'DESC');

// Paging 1
if(!empty($pg) && preg_match('!^\d$!', $pg)) {
$page = $pg;
}
else {
$page = preg_match('`\d+`', $_REQUEST['p']) ? $_REQUEST['p'] : 1;
}
$page = intval($page);

if ($page != 1) {
$min = PAGER_LPP * $page - (PAGER_LPP);
$max = PAGER_LPP * $page;
}
else {
$min = 0;
$max = PAGER_LPP;
}
$limit = " LIMIT {$min},".PAGER_LPP;
// End Paging 1

if(array_key_exists($_REQUEST['s'], $sort_cols)) {
$sort = $_REQUEST['s'];
}
else {
$sort = DEFAULT_SORT;
}
if(!ENABLE_PAGERANK || !SHOW_PAGERANK && $sort == 'P') {
$sort = 'H';
}

$tpl->assign('sort', $sort);
$path = array();
$path[] = array ('ID' => '0', 'TITLE' => _L(SITE_NAME), 'TITLE_URL' => DOC_ROOT, 'DESCRIPTION' => SITE_DESC);

if(FTR_ENABLE) {
$feat_where = 'AND (`FEATURED` = 0)';
}

$expire_where = "AND (`EXPIRY_DATE` >= ".$db->DBDate(time())." OR `EXPIRY_DATE` IS NULL)";

if(isset($_REQUEST['p'])) {
switch($_REQUEST['p']) {
case 'd':
$links = $db->GetAll("SELECT * FROM `{$tables['link']['name']}` WHERE `STATUS` = 2 {$expire_where} ORDER BY `DATE_ADDED` DESC LIMIT 0, ".LINKS_TOP);
$path[] = array ('ID' => '0', 'TITLE' => _L('Latest Links'), 'TITLE_URL' => '', 'DESCRIPTION' => '');
break;
case 'h':
$links = $db->GetAll("SELECT * FROM `{$tables['link']['name']}` WHERE `STATUS` = 2 {$expire_where} ORDER BY `HITS` DESC LIMIT 0, ".LINKS_TOP);
$path[] = array ('ID' => '0', 'TITLE' => _L('Top Hits'), 'TITLE_URL' => '', 'DESCRIPTION' => '');
break;
// Paging 2
default:
$id = get_category();
if(!$tpl->is_cached('main.tpl', $id)) {
$path = get_path($id);
if(FTR_ENABLE) {
$feat_links = $db->GetAll("SELECT * FROM `{$tables['link']['name']}` WHERE `STATUS` = 2 AND `CATEGORY_ID` = ".$db->qstr($id)." AND `FEATURED` = 1 {$expire_where} ORDER BY `EXPIRY_DATE` DESC");
$tpl->assign('feat_links', $feat_links);
}

$count = $db->GetOne("SELECT COUNT(*) FROM `{$tables['link']['name']}` WHERE `STATUS` = 2 AND `CATEGORY_ID` = ".$db->qstr($id)." {$feat_where} {$expire_where} ORDER BY `{$sort_cols[$sort]}` {$sort_ord[$sort]}");

$links = $db->GetAll("SELECT * FROM `{$tables['link']['name']}` WHERE `STATUS` = 2 AND `CATEGORY_ID` = ".$db->qstr($id)." {$feat_where} {$expire_where} ORDER BY `{$sort_cols[$sort]}` {$sort_ord[$sort]}{$limit}");
$rs = $db->Execute("SELECT * FROM `{$tables['category']['name']}` WHERE `STATUS` = 2 AND `PARENT_ID` = ".$db->qstr($id)." ORDER BY `TITLE`");
while(!$rs->EOF) {
$row = $rs->FetchRow();
if ($id == 0 && CATS_PREVIEW > 0) {
$rs2 = $db->SelectLimit("SELECT * FROM `{$tables['category']['name']}` WHERE `STATUS` = 2 AND `SYMBOLIC` <> 1 AND `PARENT_ID` = ".$db->qstr($row['ID'])." ORDER BY `HITS` DESC, `TITLE`", CATS_PREVIEW);
$row['SUBCATS'] = $rs2->GetRows();
$rs2->Close();
}
if (ENABLE_REWRITE && empty ($row['TITLE_URL'])) {
$row['TITLE_URL'] = preg_replace('`[^\w_-]`', '_', $row['TITLE']);
$row['TITLE_URL'] = str_replace('__', '_', $row['TITLE_URL']);
}

if ($row['SYMBOLIC'] == 1) {
$row['ID'] = $row['SYMBOLIC_ID'];
$tempcat = $db->GetRow("SELECT * FROM `{$tables['category']['name']}` WHERE `ID` = ".$db->qstr($row['SYMBOLIC_ID']));
if (empty($row['TITLE'])) {
$row['TITLE'] = $tempcat['TITLE'];
}
$row['TITLE'] = "@" . $row['TITLE'];
if(ENABLE_REWRITE){
$row['TITLE_URL'] = construct_mod_rewrite_path($row['SYMBOLIC_ID']);
}
$row['COUNT'] = $db->GetOne("SELECT COUNT(*) FROM `{$tables['category']['name']}` WHERE `STATUS` = '2' AND `PARENT_ID` = ".$db->qstr($row['SYMBOLIC_ID']));
$row['COUNT'] += $db->GetOne("SELECT COUNT(*) FROM `{$tables['link']['name']}` WHERE `STATUS` = '2' AND `CATEGORY_ID` = ".$db->qstr($row['SYMBOLIC_ID']));
}
else {
$row['COUNT'] = $db->GetOne("SELECT COUNT(*) FROM `{$tables['category']['name']}` WHERE `STATUS` = '2' AND `PARENT_ID` = ".$db->qstr($row['ID']));
$row['COUNT'] += $db->GetOne("SELECT COUNT(*) FROM `{$tables['link']['name']}` WHERE `STATUS` = '2' AND `CATEGORY_ID` = ".$db->qstr($row['ID']));
}

$categs[] = $row;
}
$rs->Close();
}
// $count = $db->GetOne("SELECT COUNT(*) FROM `{$tables['link']['name']}` WHERE `STATUS` = 2 AND `CATEGORY_ID` = ".$db->qstr($id));
$links = $db->GetAll("SELECT * FROM `{$tables['link']['name']}` WHERE `STATUS` = 2 AND `CATEGORY_ID` = ".$db->qstr($id)." {$feat_where} {$expire_where} ORDER BY `{$sort_cols[$sort]}` {$sort_ord[$sort]}{$limit}");
$path = get_path($id);

$tpl->assign('title_prefix', $path[count($path) - 1]['TITLE']." - ");

$path[] = array ('ID' => $id, 'TITLE' => _L('Page ' . $page), 'TITLE_URL' => '', 'DESCRIPTION' => '');

break;
// End Paging 2
}
$tpl->assign('p', $_REQUEST['p']);

}
elseif(!empty($user_where) && !empty($uid)) {

if(FTR_ENABLE) {
$feat_links = $db->GetAll("SELECT * FROM `{$tables['link']['name']}` WHERE `STATUS` = 2 AND AND `FEATURED` = 1 {$expire_where} {$user_where} ORDER BY `EXPIRY_DATE` DESC");
$tpl->assign('feat_links', $feat_links);
}

$count_link_results = $db->GetOne("SELECT COUNT(*) FROM `{$tables['link']['name']}` WHERE `STATUS` = 2 {$expire_where} {$user_where} ORDER BY `{$sort_cols[$sort]}` {$sort_ord[$sort]}");

$links = $db->GetAll("SELECT * FROM `{$tables['link']['name']}` WHERE `STATUS` = 2 {$feat_where} {$expire_where} {$user_where} ORDER BY `{$sort_cols[$sort]}` {$sort_ord[$sort]}");

$tpl->assign('have_search_results', 1);
$tpl->assign('uid', $uid);

$path[] = array ('ID' => '0', 'TITLE' => _L('Personal links'), 'TITLE_URL' => '', 'DESCRIPTION' => _L('Search results for personal links'));
}
elseif(isset($_REQUEST['q'])) {

$_REQUEST['q'] = clean_string_paranoia($_REQUEST['q']);
$q = (!empty($_REQUEST['q']) ? $_REQUEST['q'] : ' ');

$tpl->assign('q', $q);
$tpl->assign('search_terms', $q);
$tpl->assign('qu', rawurlencode($q));

$search = $db->qstr('%'.preg_replace('`\s+`', '%', $q).'%');

$first = $_REQUEST['first'];
$cat_page = (!empty($_REQUEST['cat_page']) ? intval($_REQUEST['cat_page']) : 0);

$num_per_page = (!empty($first) ? 5 : 5);

if(!empty($q)) {
$x1 = $num_per_page * $cat_page;

$count_cat_results = $db->GetOne("SELECT COUNT(*) FROM `{$tables['category']['name']}` WHERE `TITLE` LIKE {$search}");

if(!empty($count_cat_results)) {
$cat_search_sql = "SELECT `ID`, `TITLE`, `CACHE_TITLE`, `CACHE_URL` FROM `{$tables['category']['name']}` WHERE `TITLE` LIKE {$search} AND `STATUS` = '2' AND `CACHE_TITLE` IS NOT NULL AND `CACHE_URL` IS NOT NULL ORDER BY `ID` ASC, `PARENT_ID` ASC, `TITLE` ASC, `CACHE_TITLE` ASC LIMIT {$x1}, {$num_per_page}";

$list_cat = $db->GetAll($cat_search_sql);

$num_pages = ceil($count_cat_results / $num_per_page);

if((($cat_page + 1) * $num_per_page) > $count_cat_results) {
$maxn = $count_cat_results;
}
else {
$maxn = (($cat_page + 1) * $num_per_page);
}

$tpl->assign('list_cat', $list_cat);
$tpl->assign('cat_page', $cat_page);
$tpl->assign('count_cat_results', $count_cat_results);
$tpl->assign('maxn', $maxn);
$tpl->assign('first', $first);
$tpl->assign('num_per_page', $num_per_page);
$tpl->assign('num_pages', $num_pages);
if(!empty($list_cat)) {
$search_cat = $tpl->fetch('category_search.tpl');
$tpl->assign('search_category', $search_cat);
}
}

if(FTR_ENABLE) {
$feat_links = $db->GetAll("SELECT * FROM `{$tables['link']['name']}` WHERE `STATUS` = 2 AND (`URL` LIKE {$search} OR `TITLE` LIKE {$search} OR `DESCRIPTION` LIKE {$search}) AND `FEATURED` = 1 {$expire_where} ORDER BY `EXPIRY_DATE` DESC");
$tpl->assign('feat_links', $feat_links);
}

$count_link_results = $db->GetOne("SELECT COUNT(*) FROM `{$tables['link']['name']}` WHERE `STATUS` = 2 AND (`URL` LIKE {$search} OR `TITLE` LIKE {$search} OR `DESCRIPTION` LIKE {$search}) {$expire_where} ORDER BY `{$sort_cols[$sort]}` {$sort_ord[$sort]}");

$links = $db->GetAll("SELECT * FROM `{$tables['link']['name']}` WHERE `STATUS` = 2 AND (`URL` LIKE {$search} OR `TITLE` LIKE {$search} OR `DESCRIPTION` LIKE {$search}) {$feat_where} {$expire_where} ORDER BY `{$sort_cols[$sort]}` {$sort_ord[$sort]}");

if($count_link_results > 0 || $count_cat_results > 0) {
$tpl->assign('have_search_results', 1);
}
else {
$tpl->assign('have_search_results', 0);
}

$categs = array();
$path[] = array ('ID' => '0', 'TITLE' => _L('Search Results'), 'TITLE_URL' => '', 'DESCRIPTION' => _L('Search results for: ').$_REQUEST['q']);
}

}
else {
$id = get_category();
if(!$tpl->is_cached('main.tpl', $id)) {
$path = get_path($id);
if(FTR_ENABLE) {
$feat_links = $db->GetAll("SELECT * FROM `{$tables['link']['name']}` WHERE `STATUS` = 2 {$bydate} AND `CATEGORY_ID` = ".$db->qstr($id)." AND `FEATURED` = 1 {$expire_where} ORDER BY `EXPIRY_DATE` DESC");
$tpl->assign('feat_links', $feat_links);
}

// Paging 3
$count = $db->GetOne("SELECT COUNT(*) FROM `{$tables['link']['name']}` WHERE `STATUS` = 2 {$bydate} AND `CATEGORY_ID` = ".$db->qstr($id)." {$feat_where} {$expire_where} ORDER BY `{$sort_cols[$sort]}` {$sort_ord[$sort]}");
// End Paging 3

$links = $db->GetAll("SELECT * FROM `{$tables['link']['name']}` WHERE `STATUS` = 2 {$bydate} AND `CATEGORY_ID` = ".$db->qstr($id)." {$feat_where} {$expire_where} ORDER BY `{$sort_cols[$sort]}` {$sort_ord[$sort]}{$limit}");

$rs = $db->Execute("SELECT * FROM `{$tables['category']['name']}` WHERE `STATUS` = 2 AND PARENT_ID = ".$db->qstr($id)." ORDER BY `TITLE`");
while(!$rs->EOF) {
$row = $rs->FetchRow();
if ($id == 0 && CATS_PREVIEW > 0) {
$rs2 = $db->SelectLimit("SELECT * FROM `{$tables['category']['name']}` WHERE `STATUS` = 2 AND `SYMBOLIC` <> 1 AND `PARENT_ID` = ".$db->qstr($row['ID'])." ORDER BY `HITS` DESC, `TITLE` ", CATS_PREVIEW);
$row['SUBCATS'] = $rs2->GetRows();
$rs2->Close();
}
if(ENABLE_REWRITE && empty ($row['TITLE_URL'])) {
$row['TITLE_URL'] = preg_replace('`[^\w_-]`', '_', $row['TITLE']);
$row['TITLE_URL'] = str_replace('__', '_', $row['TITLE_URL']);
}

if ($row['SYMBOLIC'] == 1) {
$row['ID'] = $row['SYMBOLIC_ID'];
$tempcat = $db->GetRow("SELECT * FROM `{$tables['category']['name']}` WHERE `ID` = ".$db->qstr($row['SYMBOLIC_ID']));
if (empty($row['TITLE'])) {
$row['TITLE'] = $tempcat['TITLE'];
}
$row['TITLE'] = "@" . $row['TITLE'];
if(ENABLE_REWRITE){
$row['TITLE_URL'] = construct_mod_rewrite_path($row['SYMBOLIC_ID']);
}
$row['COUNT'] = $db->GetOne("SELECT COUNT(*) FROM `{$tables['category']['name']}` WHERE `STATUS` = '2' AND `PARENT_ID` = ".$db->qstr($row['SYMBOLIC_ID']));
$row['COUNT'] += $db->GetOne("SELECT COUNT(*) FROM `{$tables['link']['name']}` WHERE `STATUS` = '2' AND `CATEGORY_ID` = ".$db->qstr($row['SYMBOLIC_ID']));
}
else {
$row['COUNT'] = $db->GetOne("SELECT COUNT(*) FROM `{$tables['category']['name']}` WHERE `STATUS` = '2' AND `PARENT_ID` = ".$db->qstr($row['ID']));
$row['COUNT'] += $db->GetOne("SELECT COUNT(*) FROM `{$tables['link']['name']}` WHERE `STATUS` = '2' AND `CATEGORY_ID` = ".$db->qstr($row['ID']));
}

$categs[] = $row;
}
$rs->Close();
}
if($id > 0) {
$db->Execute("UPDATE `{$tables['category']['name']}` SET `HITS` = `HITS` + 1 WHERE `ID` = ".$db->qstr($id));
}
}

// Paging 4
$tpl->assign('list_total', $count);
// End Paging 4

$tpl->assign('category', $path[count($path) - 1]);
$tpl->assign('path', $path);
$tpl->assign('links', $links);
$tpl->assign('categs', $categs);


//Rewrite Page URL's
$PageContent = $tpl->fetch('main.tpl', $id);
if(ENABLE_REWRITE) {
$matches=array();
preg_match_all('!"(\S*)\?p=(\d+)"!', $PageContent, $matches);//Take care abt double quotes
$urls = $matches[1];
$page_nums = $matches[2];
//Populate Urls to be replaced with
for($i = 0; $i < count($urls); $i++) {
$temp_url = trim($urls[$i],'"'); //remove quotes and slashes
$page_nums[$i] = '"'.$temp_url.'page-'.$page_nums[$i].'.html"';//put the quotes again
}
$PageContent = str_replace($matches[0], $page_nums, $PageContent);
}//if ENABLE_REWRITE
echo $PageContent;
?>

I do not find the code! :shock:

[wtms]

Originally Posted by pragent

I do not find the code! :shock:

Look near the bottom of the page and replace my red comment with the code

[anon]

Originally Posted by pragent

where I find:

echo $tpl->fetch('main.tpl', $id);

in my index.php

With me there is not that! :(

do a search for it.

hit CTRL+F to bring up fid in whatever editor you're using.

type in: $tpl->fetch('main.tpl', $id);

and ok to search.

it's near the bottom of the index.php file.

[ewwharhar]

How do you show how much pending links there are?

Like Links, Catergories, Pending