How To: Vertical Menus (non JS)?

[Eric Barnes +]

Whoops.

Try to change this:

PHP Code:

$sSQL="SELECT COUNT(*) AS total FROM ".PREFIX."listings WHERE display = 'Y' AND expiration > NOW() AND section=".$cat; 


TO:

PHP Code:

$sSQL="SELECT COUNT(*) AS total FROM ".PREFIX."products WHERE display = 'Y' AND expiration > NOW() AND section=".$cat; 


[dawnrae]

Perfect!
Thanks much, Eric!


Here's the full code in case someone else needs it:


<?php
/**
* smarty_function_categories_ul
*
* This function is useful for using your categories in the navigation.
*
* All source code & content (c) Copyright 2006, 68 Classifieds
* unless specifically noted otherwise.
*
* @package 68classifieds
* @author Eric Barnes
* @copyright 68 Classifieds
* @link http://www.68classifieds.com
* @$Revision: 1.1 $
* @Updated: $Date: 2007/01/08 19:12:22 $
*/

function smarty_function_categories_ul($params, &$smarty)
{
global $db;

$ul_attr = '';
$li_attr = '';
$show_total = 'N';

foreach ($params as $_key=>$_value)
{
switch ($_key)
{
case 'depth':
$$_key = (int)$_value;
break;

case 'ul_attr':
case 'li_attr':
case 'show_total':
$$_key = (string)$_value;
break;
}
}

$sSQL="SELECT id,name,parent_id,cOrder,cLink FROM ".PREFIX."categories WHERE parent_id=0 AND display<>'N' ORDER BY cORDER DESC, name ASC";
$result=$db->query($sSQL);
$output="<ul ".$ul_attr." class=\"menu\">\n";
while($row = $result->fetch())
{
$id=$row['id'];
$parent=$row['parent_id'];
if($row['cLink']<>'')
{
$link=$row['cLink'];
}
else
{
$link='category.php?type='.$id;
}
$output .= "<li ".$li_attr."><a href=\"".$link."\" class=\"topborder\">".$row['name']."</a>\n";
if($show_total=='N') {
$output.="( ". getTotal($id) .")";
}
//get sub categories
$sSQL2="SELECT id,name,parent_id,cOrder,cLink FROM ".PREFIX."categories WHERE parent_id=".$id." AND display<>'N' ORDER BY cORDER DESC, name ASC";
//$output.=$sSQL2."\n";
$result2=$db->query($sSQL2);
if($result2->size() > 0)
{
$output.="<ul class=\"submenu\">\n";
while($row = $result2->fetch())
{
$subid=$row['id'];
if($row['cLink']<>'')
{
$link=$row['cLink'];
}
else
{
if($subid>1)
{
$link='category.php?type='.$id.'&sec='.$subid;
}
else
{
$link='category.php?type='.$subid;
}
}
$output .= "<li ".$li_attr."><a href=\"".$link."\">".$row['name']."\n";
if($show_total=='Y') {
$output.="(". getTotal($subid) .")";
}
}
$output.="</a></li>\n";
$output.="</ul>\n";
}
$output.="</li>\n";
}
$output.="</ul>\n";
return $output;
}
function getTotal($cat)
{
global $db;
$sSQL="SELECT COUNT(*) AS total FROM ".PREFIX."products WHERE display = 'Y' AND expiration > NOW() AND section=".$cat;
$result=$db->query($sSQL);
$row = $result->fetch();
return $row['total'];
}
?>

[dawnrae]

One quick question.
The script is working great except that it displays a (0) beside the sub category even through there are ads in the sub-sub categories.

For Example:

Main Category (which has no listings, only subcategories)
>> Sub Category (which has no listings, only sub-subcategories)
>> >>Sub Sub Categories (this is the level ads will be inserted)

Is it possible to display the total tally of ads from the Sub-Sub Categories as the total for the parent Sub Category>

For Example:
Sub Category 1 has three Sub Sub Categories, each with three ads, totaling 9 ads. I would like then for the parent Sub Category which is shown in the menu to display (9).

Is that doable?


Dawn

[Eric Barnes +]

Try this:

PHP Code:

<?php
/**
 * smarty_function_categories_ul
 *
 * This function is useful for using your categories in the navigation.
 *
 * All source code & content (c) Copyright 2006, 68 Classifieds
 * unless specifically noted otherwise.
 *
 * @package 68classifieds
 * @author  Eric Barnes
 * @copyright 68 Classifieds
 * @link http://www.68classifieds.com
 * @$Revision: 1.1 $
 * @Updated: $Date: 2007/01/08 19:12:22 $
 */
 
function smarty_function_categories_ul($params, &$smarty)
{
    global $db;
    
    $ul_attr = '';
    $li_attr = '';
    $depth=2;
    $show_total = 'Y';

    foreach ($params as $_key=>$_value) 
    {
        switch ($_key) 
        {
            case 'depth':
                $$_key = (int)$_value;
                break;

            case 'ul_attr':
            case 'li_attr':
            case 'show_total':
                $$_key = (string)$_value;
                break;
        }
    }
    
    $sSQL="SELECT id,name,parent_id,cOrder,cLink FROM ".PREFIX."categories WHERE parent_id=0 AND display<>'N' ORDER BY cORDER DESC, name ASC";
    $result=$db->query($sSQL);
    $output="<ul ".$ul_attr.">\n";
    while($row = $result->fetch())
    {
        $id=$row['id'];
        $parent=$row['parent_id'];
        if($row['cLink']<>'')
        {
            $link=$row['cLink'];    
        }
        else
        {
            $link='category.php?type='.$id;    
        }
        $output .= "<li ".$li_attr."><a href=\"".$link."\">".$row['name']."</a>\n";
        if($show_total=='Y') {
            get_cat_ids(array($id));
            $total=get_cat_count($arr_childs);
            $output.="( ". $total .")";
        }
        //get sub categories
        $sSQL2="SELECT id,name,parent_id,cOrder,cLink FROM ".PREFIX."categories WHERE parent_id=".$id." AND display<>'N' ORDER BY cORDER DESC, name ASC";
        //$output.=$sSQL2."\n";
        $result2=$db->query($sSQL2);
        if($result2->size() > 0)
        {
            $output.="<ul>\n";
            while($row = $result2->fetch())
            {
                $subid=$row['id'];
                if($row['cLink']<>'')
                {
                    $link=$row['cLink'];    
                }
                else
                {
                    if($subid>1)
                    {
                        $link='category.php?type='.$id.'&sec='.$subid;
                    }
                    else
                    {
                        $link='category.php?type='.$subid;    
                    }
                }
                $output .= "<li ".$li_attr."><a href=\"".$link."\">".$row['name']."</a>\n";
                if($show_total=='Y') {
                    get_cat_ids(array($subid));
                    $total=get_cat_count($arr_childs);
                    $output.="( ". $total .")";
                }
            }
            $output.="</ul>\n";
        }
        $output.="</li>\n";
    }
    $output.="</ul>\n";
    return $output;
}
function get_cat_ids($arr_ids)
{
    $arr_new= array();
    global $arr_childs, $db;
    $sSQL="SELECT id FROM ".PREFIX."categories WHERE parent_id In (". implode(', ', $arr_ids) .")";
    $result=$db->query($sSQL);
    if($result->size() >= '1')
    {
        while($rs=$result->fetch())
        {
            if (!in_array($rs['id'], $arr_childs))
            {
                $arr_childs[] = $rs['id'];
                $arr_new[] = $rs['id'];  
            }
        }
        if (count($arr_new) >= '1')
        {
            get_cat_ids($arr_new);
        }
        else
        {
            return;
        }
    }
    else
    {
        return;
    }
}
function get_cat_count($arr_ids)
{
    global $db, $modules;
    $sSQL="SELECT COUNT(*) AS size FROM ".PREFIX."products WHERE ".$where." section In (". implode(', ', $arr_ids) .") AND expiration > NOW() AND (display='Y' OR display='S')";
    $result=$db->query($sSQL);
    $row = $result->fetch();
    $total=$row['size'];
    return $total;
}

?>

Okay, I tried the new code and here's the result:

Notice: Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND expiration > NOW() AND (display='Y' OR display='S')' at line 1 SQL: SELECT COUNT(*) AS size FROM class_products WHERE section In () AND expiration > NOW() AND (display='Y' OR display='S') in /home/hshomefr/public_html/classifieds/includes/classes/database/mysql.php on line 135

Looks like something in the "count" code again.

Dawn

[Eric Barnes +]

Ok one more time. Hopefully

PHP Code:

<?php
/**
 * smarty_function_categories_ul
 *
 * This function is useful for using your categories in the navigation.
 *
 * All source code & content (c) Copyright 2006, 68 Classifieds
 * unless specifically noted otherwise.
 *
 * @package 68classifieds
 * @author  Eric Barnes
 * @copyright 68 Classifieds
 * @link http://www.68classifieds.com
 * @$Revision: 1.1 $
 * @Updated: $Date: 2007/01/08 19:12:22 $
 */
 
function smarty_function_categories_ul($params, &$smarty)
{
    global $db;
    
    $ul_attr = '';
    $li_attr = '';
    $depth=2;
    $show_total = 'Y';

    foreach ($params as $_key=>$_value) 
    {
        switch ($_key) 
        {
            case 'depth':
                $$_key = (int)$_value;
                break;

            case 'ul_attr':
            case 'li_attr':
            case 'show_total':
                $$_key = (string)$_value;
                break;
        }
    }
    
    $sSQL="SELECT id,name,parent_id,cOrder,cLink FROM ".PREFIX."categories WHERE parent_id=0 AND display<>'N' ORDER BY cORDER DESC, name ASC";
    $result=$db->query($sSQL);
    $output="<ul ".$ul_attr.">\n";
    while($row = $result->fetch())
    {
        $id=$row['id'];
        $parent=$row['parent_id'];
        if($row['cLink']<>'')
        {
            $link=$row['cLink'];    
        }
        else
        {
            $link='category.php?type='.$id;    
        }
        $output .= "<li ".$li_attr."><a href=\"".$link."\">".$row['name']."</a>\n";
        if($show_total=='Y') 
        {
            $arr_childs='';
            $arr_childs = array($id);
            get_cat_ids($arr_childs);
            $total=get_cat_count($arr_childs);
            $output.="( ". $total .")";
        }
        //get sub categories
        $sSQL2="SELECT id,name,parent_id,cOrder,cLink FROM ".PREFIX."categories WHERE parent_id=".$id." AND display<>'N' ORDER BY cORDER DESC, name ASC";
        //$output.=$sSQL2."\n";
        $result2=$db->query($sSQL2);
        if($result2->size() > 0)
        {
            $output.="<ul>\n";
            while($row = $result2->fetch())
            {
                $subid=$row['id'];
                if($row['cLink']<>'')
                {
                    $link=$row['cLink'];    
                }
                else
                {
                    if($subid>1)
                    {
                        $link='category.php?type='.$id.'&sec='.$subid;
                    }
                    else
                    {
                        $link='category.php?type='.$subid;    
                    }
                }
                $output .= "<li ".$li_attr."><a href=\"".$link."\">".$row['name']."</a>\n";
                if($show_total=='Y') {
                    $arr_childs='';
                    $arr_childs = array($subid);
                    get_cat_ids($arr_childs);
                    $total=get_cat_count($arr_childs);
                    $output.="( ". $total .")";
                }
            }
            $output.="</ul>\n";
        }
        $output.="</li>\n";
    }
    $output.="</ul>\n";
    return $output;
}
function get_cat_ids($arr_ids)
{
    $arr_new= array();
    global $arr_childs, $db;
    $sSQL="SELECT id FROM ".PREFIX."categories WHERE parent_id In (". implode(', ', $arr_ids) .")";
    $result=$db->query($sSQL);
    if($result->size() >= '1')
    {
        while($rs=$result->fetch())
        {
            if (!in_array($rs['id'], $arr_childs))
            {
                $arr_childs[] = $rs['id'];
                $arr_new[] = $rs['id'];  
            }
        }
        if (count($arr_new) >= '1')
        {
            get_cat_ids($arr_new);
        }
        else
        {
            return;
        }
    }
    else
    {
        return;
    }
}
function get_cat_count($arr_ids)
{
    global $db, $modules;
    $sSQL="SELECT COUNT(*) AS size FROM ".PREFIX."products WHERE ".$where." section In (". implode(', ', $arr_ids) .") AND expiration > NOW() AND (display='Y' OR display='S')";
    $result=$db->query($sSQL);
    $row = $result->fetch();
    $total=$row['size'];
    return $total;
}

?>

Well, no error, but the 2 test ads we have entered are now showing in the total.
There should be (2) beside the Business, Commercial & Industrial sub category under Realtor Listings.

http://www.hotspringshomefront.com/c...?type=9&sec=10

I really appreciate your help with this, Eric, and am wholly impressed with your software and the support and resources available on this board.

Dawn

Originally Posted by Unregistered

Well, no error, but the 2 test ads we have entered are now showing in ...

That should say, are NOT showing, not now....

Come on, brain and fingers, work together now

[Eric Barnes +]

Can you send me a private message with your ftp information? I think that might make this whole process a little easier.

[dawnrae]

For anyone else who wishes a vertical menu in a similar fashion, here's the final code (many, many thanks to Eric).

This will give you a menu in list fashion (Yeah, CSS!), where the (#) is the sum total of listings in the Sub Sub Categories of the parent Sub Category ... gee, I hope that makes sense. If not, hopefully the illustration below will help.

Menu Structure Example:

<ul>
<li>Main Category
<ul>
<li>Sub Category (#)</li>
<li>Sub Category (#)</li>
<li>Sub Category (#)</li>
<li>Sub Category (#)</li>
</ul>
</li>
</ul>


Essentially, the actual listings themselves are found only in the Sub Sub Categories:

The Main Category does not allow listings.
The Sub Categories do not allow listings.
The Sub Sub Categories have listings.




To Implement the Menu ...
----------------------------------------------------------------------------------------
First create a new file named:
includes/classes/smarty/plugins/function.categories_ul.php

Then include this code:

<?php
/**
* smarty_function_categories_ul
*
* This function is useful for using your categories in the navigation.
*
* All source code & content (c) Copyright 2006, 68 Classifieds
* unless specifically noted otherwise.
*
* @package 68classifieds
* @author Eric Barnes
* @copyright 68 Classifieds
* @link http://www.68classifieds.com
* @$Revision: 1.1 $
* @Updated: $Date: 2007/01/08 19:12:22 $
*/

function smarty_function_categories_ul($params, &$smarty)
{
global $db;

$ul_attr = '';
$li_attr = '';
$depth=2;
$show_total = 'Y';

foreach ($params as $_key=>$_value)
{
switch ($_key)
{
case 'depth':
$$_key = (int)$_value;
break;

case 'ul_attr':
case 'li_attr':
case 'show_total':
$$_key = (string)$_value;
break;
}
}

$sSQL="SELECT id,name,parent_id,cOrder,cLink FROM ".PREFIX."categories WHERE parent_id=0 AND display<>'N' ORDER BY cORDER DESC, name ASC";
$result=$db->query($sSQL);
$output="<ul ".$ul_attr." class=\"menu\">\n";
while($row = $result->fetch())
{
$id=$row['id'];
$parent=$row['parent_id'];
if($row['cLink']<>'')
{
$link=$row['cLink'];
}
else
{
$link='category.php?type='.$id;
}
$output .= "<li ".$li_attr."><a class=\"topborder\" href=\"".$link."\">".$row['name']."</a>\n";
/*
if($show_total=='N')
{
$arr_childs='';
$arr_childs = array($id);
get_cat_ids($arr_childs);
$total=get_cat_count($arr_childs);
$output.="( ". $total .")";
}
*/
//get sub categories
$sSQL2="SELECT id,name,parent_id,cOrder,cLink FROM ".PREFIX."categories WHERE parent_id=".$id." AND display<>'N' ORDER BY cORDER DESC, name ASC";
//$output.=$sSQL2."\n";
$result2=$db->query($sSQL2);
if($result2->size() > 0)
{
$output.="<ul class=\"submenu\">\n";
while($row = $result2->fetch())
{
$subid=$row['id'];
if($row['cLink']<>'')
{
$link=$row['cLink'];
}
else
{
if($subid>1)
{
$link='category.php?type='.$id.'&sec='.$subid;
}
else
{
$link='category.php?type='.$subid;
}
}
$output .= "<li ".$li_attr."><a href=\"".$link."\">".$row['name']."\n";
if($show_total=='Y') {
$arr_childs = array($subid);
$total=get_cats($subid);
$output.="(". $total .")</a>";
}
}
$output.="</li>\n";
$output.="</ul>\n";
}
$output.="</li>\n";
}
$output.="</ul>\n";
return $output;
}
function get_cats($parent)
{
global $db;
$sSQL="SELECT id FROM ".PREFIX."categories WHERE parent_id = ". $parent;
//echo $sSQL;
$result=$db->query($sSQL);
$i=0;
while($rs=$result->fetch())
{
$cat[]=$rs['id'];
$i++;
}
if($cat == '')
{
$total=0;
}
else
{
$total=get_cat_count($cat);
}
return $total;
}
function get_cat_count($arr_ids)
{
global $db, $modules;
$sSQL="SELECT COUNT(*) AS size FROM ".PREFIX."products WHERE ".$where." section In (". implode(', ', $arr_ids) .") AND expiration > NOW() AND (display='Y' OR display='S')";
$result=$db->query($sSQL);
$row = $result->fetch();
$total=$row['size'];
return $total;
}

?>



Next in your template file add:

{categories_ul show_total='Y'}



-------------------------------------------------------------------------------------------
If your listings are nested only one level deep (Main Category>Sub Category), then use this code in the functions.categories_ul.php instead of that above:


<?php
/**
* smarty_function_categories_ul
*
* This function is useful for using your categories in the navigation.
*
* All source code & content (c) Copyright 2006, 68 Classifieds
* unless specifically noted otherwise.
*
* @package 68classifieds
* @author Eric Barnes
* @copyright 68 Classifieds
* @link http://www.68classifieds.com
* @$Revision: 1.1 $
* @Updated: $Date: 2007/01/08 19:12:22 $
*/

function smarty_function_categories_ul($params, &$smarty)
{
global $db;

$ul_attr = '';
$li_attr = '';
$show_total = 'N';

foreach ($params as $_key=>$_value)
{
switch ($_key)
{
case 'depth':
$$_key = (int)$_value;
break;

case 'ul_attr':
case 'li_attr':
case 'show_total':
$$_key = (string)$_value;
break;
}
}

$sSQL="SELECT id,name,parent_id,cOrder,cLink FROM ".PREFIX."categories WHERE parent_id=0 AND display<>'N' ORDER BY cORDER DESC, name ASC";
$result=$db->query($sSQL);
$output="<ul ".$ul_attr." class=\"menu\">\n";
while($row = $result->fetch())
{
$id=$row['id'];
$parent=$row['parent_id'];
if($row['cLink']<>'')
{
$link=$row['cLink'];
}
else
{
$link='category.php?type='.$id;
}
$output .= "<li ".$li_attr."><a href=\"".$link."\" class=\"topborder\">".$row['name']."</a>\n";
if($show_total=='N') {
$output.="( ". getTotal($id) .")";
}
//get sub categories
$sSQL2="SELECT id,name,parent_id,cOrder,cLink FROM ".PREFIX."categories WHERE parent_id=".$id." AND display<>'N' ORDER BY cORDER DESC, name ASC";
//$output.=$sSQL2."\n";
$result2=$db->query($sSQL2);
if($result2->size() > 0)
{
$output.="<ul class=\"submenu\">\n";
while($row = $result2->fetch())
{
$subid=$row['id'];
if($row['cLink']<>'')
{
$link=$row['cLink'];
}
else
{
if($subid>1)
{
$link='category.php?type='.$id.'&sec='.$subid;
}
else
{
$link='category.php?type='.$subid;
}
}
$output .= "<li ".$li_attr."><a href=\"".$link."\">".$row['name']."\n";
if($show_total=='Y') {
$output.="(". getTotal($subid) .")";
}
}
$output.="</a></li>\n";
$output.="</ul>\n";
}
$output.="</li>\n";
}
$output.="</ul>\n";
return $output;
}
function getTotal($cat)
{
global $db;
$sSQL="SELECT COUNT(*) AS total FROM ".PREFIX."products WHERE display = 'Y' AND expiration > NOW() AND section=".$cat;
$result=$db->query($sSQL);
$row = $result->fetch();
return $row['total'];
}
?>