how can I make loop within a loop in codeigniter?

[eluser]zoreli[/eluser]
Hi

I have table faq_categories with following fields

Code:

CREATE TABLE IF NOT EXISTS `faq_categories` (
  `catid` int(11) NOT NULL AUTO_INCREMENT,
  `categoryname` varchar(37) NOT NULL,
  `parentid` int(11) DEFAULT NULL,
  `description` text NOT NULL,
  `metatags` text NOT NULL,
  `sorder` int(11) NOT NULL,
  `visible` tinyint(4) NOT NULL,
  `categoryphoto` varchar(255) NOT NULL,
  PRIMARY KEY (`catid`),
  KEY `parentid_fk` (`parentid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=52 ;

In my php code I have the following code :

Code:

$query="SELECT * FROM categories";
    $result=mysql_query($query);
    $num=mysql_num_rows($result);
    $i=0;
    while ($i < $num) {
        $id=mysql_result($result,$i,"id");
        $name=mysql_result($result,$i,"name");
        $parentid=mysql_result($result,$i,"parentid");
        $categoryphoto=mysql_result($result,$i,"categoryphoto");
        $sorder=mysql_result($result,$i,"sorder");
        $visible=mysql_result($result,$i,"visible");
      

echo $id;

echo $name;

// THIS IS WHAT I DON'T KNOW HOW TO DO IN CODEIGNITER
// HOW TO GET THE CATEGORYNAME ON BASE ON PARENTID

if ($parentid) {
                        
                        //echo $parentid;
                         $query1="SELECT name as parentname FROM categories WHERE id = ".$parentid;
                         echo mysql_result(mysql_query($query1),0,"parentname");
                        
                        } else {
                            echo "Root Category";
                        }

How can I do this in codeigniter? If possible, I would like to avoid joins.

Regards, Zoreli

[eluser]aquary[/eluser]
Just a question, why avoiding JOIN? Your concept would slow down the code a lot more, and I have not yet spoken about the usage of mysql_result() to retrieve columns like that....

And for the answer, I'd like to point you to the userguide and read atleast part about SELECT the data and retriving the result... but I'll drop a quick example here:

Code:

$result=$this->db->query("SELECT * FROM categories")->result();
    if(!empty($result)){
        foreach($result as $row){
            $id=$row->id;
            $name=$row->name;
            ...
            ...
            echo $id, $name;
            if($parentid){
               // Try it yourself.
            }
            else
               echo 'Root'
        }
    }

Then why don't you just use JOIN, which remove 40% of the code, and speed up the code A LOT.

Code:

$result=$this->db
       ->query("SELECT MAIN.*,
             SUB.name as parentName
             FROM categories MAIN
             LEFT JOIN categories SUB ON
             MAIN.parentid=SUB.id"
          )
       ->result();
    if(!empty($result)){
        foreach($result as $row){
            echo $row->id, $row->name;
            if($parentid){
               echo $row->parentName;
            }
            else
               echo 'Root';
        }
    }

I didn't run the code, so not 100% bugs free.

[eluser]zoreli[/eluser]
First of all, thanks for your time and help. I really appreciate when people spent their time to help other people.

As for the answer...Well, I am trying to make general models. I am novice in CodeIgniter, but not novice in programming, so I am trying to reuse my code as much as I can.

Having that in mind, please take a look how I select records:

Code:

public function selectOneRecord($selectWhat = array())
        {
            $data = array();
            $rid = $selectWhat['rid'];
            $tname = $selectWhat['tname'];
            $fname = $selectWhat['fname'];
          
            $this->db->where($fname,$rid);
            $query = $this->db->get($tname,1);
            if ($query->num_rows() > 0)
            {
                $data = $query->row_array();
            }
            
            $query->free_result();
            return $data;        
        } // end of function selectOneRecord
        
        public function selectAllRecords($selectWhat = array())
        {
            $data = array();
            $tname = $selectWhat['tname'];
            $sortby = $selectWhat['sortby'];
            $how = $selectWhat['how'];
            $this->db->order_by($sortby,$how);
            $query = $this->db->get($tname);
            if($query->num_rows() > 0)
            {
                foreach($query->result_array() as $row)
                {
                    $data[] = $row;  
                }
            }
            
            $query->free_result();
            return $data;
        } // end of function selectAllRecords

In attempt to avoid creating separate function for each call, I am trying to make general models. Many people here can even make those 2 functions in one, but my knowledge is not on that level.

This example in my first post is for the table faq_categories, but I would like to use it for news_categories, products_categories etc...Thats why I wanted to avoid join.

Once again, thanks for your time and code snippet. Any other ideas are also welcomed. If you have any idea how to make the join reusable as well, it will help me a lot.

Regards, Zoreli