Wiki Navigation

recursive function / Help Please!

recursive function

function get_categs_tree($id=0)
{
   global $db;
   static $categs = array ("0" => "Top");
   static $level = 0;
   $level++;
   $r = $db->select("SELECT * FROM CATEGORII where PARENT_ID = $id and ACTIV = 1 ORDER BY CATEGORIE");
   //$db->print_last_query();    
   while ($row = $db->get_row($r, 'MYSQL_ASSOC')) {
           if ($level==1) {
            $categs[$row["ID_CATEGORIE"]] = $row["CATEGORIE"];        
        } else {
            $categs[$row["ID_CATEGORIE"]] = str_repeat ('&nbsp;&nbsp;', $level-1).'|___'.$row["CATEGORIE"];        
        }  
        get_categs_tree($row["ID_CATEGORIE"]);
   }   
   $level--;
  return $categs;
}
 
can someone give an idea how i can change this with orm? pls pls

Reply Quote

Message #295

If you can provide the database schema being used along with a simple description of what the code is doing, it will be easier for others to give you suggestions.

Reply Quote

wbond

06/12/09 09:06:18

Message #297

Schema is very simple, have an id, title, and a parent_id. this must be a function who references itself to find parent who have parent_id = 0, and children as many levels exist, like categories and subcategories of that category.

Reply Quote

Mungiu

06/12/09 20:23:05

Message #418

I comeback with this because i need this very much. more details, so:

table:

--
-- Table structure for table `categorii`
--
 
CREATE TABLE IF NOT EXISTS `categorii` (
  `id_categorie` int(11) NOT NULL AUTO_INCREMENT,
  `categorie` varchar(100) NOT NULL,
  `parent_id` int(11) NOT NULL,
  `activ` tinyint(1) NOT NULL,
  PRIMARY KEY (`id_categorie`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
 
--
-- Dumping data for table `categorii`
--
 
INSERT INTO `categorii` (`id_categorie`, `categorie`, `parent_id`, `activ`) VALUES
(1, 'Vanzari', 0, 1),
(2, 'Cumparari', 0, 1),
(3, 'Masini', 1, 1);

php function:

#GET CATEGORY TREE
function get_categs_tree($id=0)
{
   global $db;
   static $categs = array ("0" => "Top");
   static $level = 0;
   $level++;
   $r = $db->select("SELECT * FROM CATEGORII where PARENT_ID = $id and ACTIV = 1 ORDER BY CATEGORIE");
   //$db->print_last_query();    
   while ($row = $db->get_row($r, 'MYSQL_ASSOC')) {
           if ($level==1) {
            $categs[$row["ID_CATEGORIE"]] = $row["CATEGORIE"];        
        } else {
            $categs[$row["ID_CATEGORIE"]] = str_repeat ('&nbsp;&nbsp;', $level-1).'|___'.$row["CATEGORIE"];        
        }  
        get_categs_tree($row["ID_CATEGORIE"]);
   }   
   $level--;
  return $categs;
}
 
 
use function :
 
 
{{{
#!php
### Populare select
 
#DENUMIREA SELECT-ului in template-ul HTML
$xtpl->assign('SELECT_NAME', 'parent_id');
 
$parent_categ = Categorii::get_categs_tree(0);
//print_r($parent_categ);
foreach ($parent_categ as $value => $option) {
    # selectare pt edit  / iau id de mai sus pentru test
    
    if ($value == $parent_id) {
        $xtpl->assign('SELECTED', "selected='selected'");
    } else {
        $xtpl->assign('SELECTED', "");
    }
    # end selectare pt edit
    
    $xtpl->assign('VALUE_P', $value);
    $xtpl->assign('NAMESELECT_P', $option);
    $xtpl->parse('main.options');
}
 
### End Populare select
}}}

i want this made it with fActiveRecord, can somebody please help me?

a link maybe helps : http://deadlytechnology.com/scripts/simple-recursive-php-tree-menu-example/ about recursive function. if you need any other details please let me note.

Reply Quote

Message #424

@Mungiu

I've put together an example of how I would approach this. Because I don't quite understand your recursive function, I haven't touched it very much. I've just modified it enough to show how to use it.

First, I like to completely separate all database interaction from the rest of my application. So, I build a class for each and every table in my application. That class does NOTHING except setup objects to extract data from the db. It does not do anything with the data. This makes it very easy to have a collection of all the queries for a database table. Again, you need one class for each table. Each class can have different tables.

<?php
// This should be stored in a file called Categorii.php
class Categorii extends fActiveRecord
{
    protected function configure()
    {
        // Flourish would try to use humanize to make your table name something like categoriis.
        // This method maps this class to the table called categorii and prevents the humanization of the word
        fORM::addCustomClassTableMapping($this, 'categorii');        
    }
    
    /**
     * Fetch all records associated with a parent_id from table categorii
     * @param Integer $parent_id
     * @return Object|BOOL      // Returns a flourish resultset object or FALSE if no records exist.
     */
    public static function fetchCategorii($parent_id)
    {
        try
        {
            // Get the database connection.  This is better than using "global $db"
            $db = fORMDatabase::retrieve();
            
            // Note I am using the %i placeholder to represent an integer value
            $sql = "SELECT * FROM CATEGORII where PARENT_ID = %i and ACTIV = 1 ORDER BY CATEGORIE";
            
            // Escape the parent id
            $sql = $db->escape($sql, $parent_id);
            
            $result = $db->query($sql);
            
            // If there are no rows, throw an exception
            $result->tossIfNoRows();
            
            // Return the entire resultset
            return $result;
            
        } catch( fNoRowsException $e) 
            {
                // If there were no rows, simply return false
                 return FALSE; 
            } catch ( fSQLException $e ) 
                {
                    // Catch bad SQL statements.
                    echo "ARGH!  I wrote a bad SQL statement and need to manage this error!";
                    exit;
                } catch ( fException $e ) 
                    {
                        // Catch any other exception
                        echo "ARGH!  I failed and need to manage this error!";
                        exit;
                    }
    }
}
 
?>

Now, I only have to use the static function above to process the data. See how my actual logic code is so much cleaner now that there is no database interaction in it:

<?php
 
// Don't forget to do your includes &/or autoloads to get flourish pulled in and to have access to Categorii.php
 
 
### Populare select
 
#DENUMIREA SELECT-ului in template-ul HTML
$xtpl->assign('SELECT_NAME', 'parent_id');
 
$parent_categ = get_categs_tree(0);
//print_r($parent_categ);
foreach ($parent_categ as $value => $option) {
    # selectare pt edit  / iau id de mai sus pentru test
    
    if ($value == $parent_id) {
        $xtpl->assign('SELECTED', "selected='selected'");
    } else {
        $xtpl->assign('SELECTED', "");
    }
    # end selectare pt edit
    
    $xtpl->assign('VALUE_P', $value);
    $xtpl->assign('NAMESELECT_P', $option);
    $xtpl->parse('main.options');
}
 
### End Populare select
 
function get_categs_tree($id=0)
{
    static $categs = array ("0" => "Top");
    static $level = 0;
    $level++;
    
    
    // Get a flourish resultset object of all categories matching this parent_id
    $categories = Categorii::fetchCategorii($parent_id);
    if($categories === FALSE)
    {
        // There weren't any matching records.
        return FALSE;
    }
    
    // Flourish lets you use foreach to iterate through resultsets and recordset objects.
    foreach($categories as $row)
    {
        if ($level==1)
        {
            $categs[$row["ID_CATEGORIE"]] = $row["CATEGORIE"];        
        } else 
            {
                $categs[$row["ID_CATEGORIE"]] = str_repeat ('&nbsp;&nbsp;', $level-1).'|___'.$row["CATEGORIE"];        
            }  
        get_categs_tree($row["ID_CATEGORIE"]);
    }   
    
    $level--;
    
    return $categs;
}
 
 
?>

I hope this helps.

Justin

Reply Quote

Message #426

Tank you very much for your help, i will look tommorow if it's working. How to say this function, must search in a table for parent category and child category, and child of child category, etc. till don't remain any child, it's for menu with 'n' level.

Reply Quote

Eu

09/25/09 18:29:35

Message #449

with a little changes it's workin have posted the result array. Here is the code maybe whould help someone who need to use recursive function. i think is very useful when have categories, subcategories and so long and we want to store in same table.

/**
                 * Fetch all records associated with a parent_id from table categorii
                 * @param Integer $parent_id
                 * @return Object|BOOL      // Returns a flourish resultset object or FALSE if no records exist.
                 */
                public static function fetchCategorii($parent_id)
                {
                    try
                    {
                        // Get the database connection.  This is better than using "global $db"
                        $db = fORMDatabase::retrieve();
                        
                        // Note I am using the %i placeholder to represent an integer value
                        $sql = "SELECT * FROM categorii where parent_id = %i and activ = 1 ORDER BY categorie";
                        
                        // Escape the parent id
                        $sql = $db->escape($sql, $parent_id);
                        
                        $result = $db->query($sql);
                        
                        // If there are no rows, throw an exception
                        $result->tossIfNoRows();
                        
                        // Return the entire resultset
                        return $result;
                        
                    } catch( fNoRowsException $e) 
                        {
                            // If there were no rows, simply return false
                             return FALSE; 
                        } catch ( fSQLException $e ) 
                            {
                                // Catch bad SQL statements.
                                echo "ARGH!  I wrote a bad SQL statement and need to manage this error!";
                                exit;
                            } catch ( fException $e ) 
                                {
                                    // Catch any other exception
                                    echo "ARGH!  I failed and need to manage this error!";
                                    exit;
                                }
                }
            
                        
            
                            
                public static function get_categs_tree($id=0)
                
                {
                    static $categs = array ("0" => "Top");
                    static $level = 0;
                    $level++;
                    
                    // Get a flourish resultset object of all categories matching this parent_id
                    $categories = Categorii::fetchCategorii($id);
                    if($categories === FALSE)
                    {
                        // There weren't any matching records.
                        return FALSE;
                    }
                    
                    // Flourish lets you use foreach to iterate through resultsets and recordset objects.
                    foreach($categories as $row)
                    {
                        
                        
                        if ($level==1)
                        {
                            $categs[$row["id_categorie"]] = $row["categorie"];        
                        } else 
                            {
                                $categs[$row["id_categorie"]] = str_repeat ('&nbsp;&nbsp;', $level-1).'|___'.$row["categorie"];        
                            } 
                            
                        Categorii::get_categs_tree($row["id_categorie"]);
                            $level--;
                    }   
                    
 
                    return $categs;
                    
                }

Reply Quote

Message #450

Array
(
    [0] => Top
    [2] => Cumparari
    [6] => &nbsp;&nbsp;|___Foto - Video
    [5] => &nbsp;&nbsp;|___Masini
    [1] => Vanzari
    [8] => &nbsp;&nbsp;|___Diverse
    [7] => &nbsp;&nbsp;|___Electronice
    [9] => &nbsp;&nbsp;&nbsp;&nbsp;|___Calculatoare
    [4] => &nbsp;&nbsp;|___Imobiliare
    [3] => &nbsp;&nbsp;|___Masini
)

Reply Quote