Can you use JOIN in a recursive function PHP/MySQL?

Question

I have the following table.

CREATE TABLE IF NOT EXISTS `omc_schedule` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `trainer_id` int(11) NOT NULL,
  `course` varchar(255) NOT NULL,
  `capacity` int(11) NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `parentid` int(10) NOT NULL,
  `order` int(11) NOT NULL,
  `booked` int(5) NOT NULL,
  `type` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ...

I can use the following function to generate an array tree.

 function generateTree(&$tree, $parentid = 0) {
    $this->db->select('*');
    $this->db->where ('parentid',$parentid);
$this->db->where ('active','1');
    $this->db->order_by('order asc, parentid asc');
     $res = $this->db->get('omc_schedule');
     if ($res->num_rows() > 0) {
        foreach ($res->result_array() as $r) {
    // push found result onto existing tree
            $tree[$r['id']] = $r;
            // create placeholder for children
            $tree[$r['id']]['children'] = array();
            // find any children of currently found child
            $this->generateTree($tree[$r['id']]['children'],$r['id']);
         }     
     }
      $res->free_result();
      return $tree;
 }

Now I want to join to a trainer table to get trainer's name.

CREATE TABLE IF NOT EXISTS `omc_trainer` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `trainer_name` varchar(50) NOT NULL,
  `trainer_image` varchar(100) NOT NULL,
  `video_url` text NOT NULL,
  `desc` text NOT NULL,
  PRIMARY KEY (`id`)
)

Now I tried to join in different ways, but I am not able to get trainer's name in every array.

Is there any way I can use JOIN to get trainer's name in a recursive function?

Above function is CodeIgniter, but please ignore it.

Thanks in advance.

Answer 1

I prefer to make a single call into the database and return a tree/subtree as it's much more efficient and keeps your app code clean.

The following is an example for you to review which you may find of interest:

full script here : http://paste.pocoo.org/show/274386/

-- TABLES

drop table if exists trainer;
create table trainer
(
trainer_id smallint unsigned not null auto_increment primary key,
name varchar(255) not null
)
engine=innodb;

drop table if exists schedule;
create table schedule
(
sched_id smallint unsigned not null auto_increment primary key,
trainer_id smallint unsigned null,
name varchar(255) not null,
parent_sched_id smallint unsigned null,
key schedule_parent_idx(parent_sched_id),
key schedule_trainer_idx(trainer_id)
)
engine=innodb;


-- STORED PROCEDURES

drop procedure if exists schedule_hier;

delimiter #

create procedure schedule_hier
(
in p_sched_id smallint unsigned
)
begin

declare v_done tinyint unsigned default 0;
declare v_depth smallint unsigned default 0;

create temporary table hier(
 parent_sched_id smallint unsigned, 
 sched_id smallint unsigned, 
 depth smallint unsigned default 0
)engine = memory;

insert into hier select parent_sched_id, sched_id, v_depth from schedule where sched_id = p_sched_id;

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table tmp engine=memory select * from hier;

while not v_done do

    if exists( select 1 from schedule p inner join hier on p.parent_sched_id = hier.sched_id and hier.depth = v_depth) then

        insert into hier 
            select p.parent_sched_id, p.sched_id,   v_depth + 1 from schedule p 
            inner join tmp on p.parent_sched_id = tmp.sched_id and tmp.depth = v_depth;

        set v_depth = v_depth + 1;          

        truncate table tmp;
        insert into tmp select * from hier where depth = v_depth;

    else
        set v_done = 1;
    end if;

end while;

select 
 s.sched_id,
 s.name as schedule_name,
 p.sched_id as parent_sched_id,
 p.name as parent_schedule_name,
 t.trainer_id,
 t.name as trainer_name,
 hier.depth
from 
 hier
inner join schedule s on hier.sched_id = s.sched_id
inner join schedule p on hier.parent_sched_id = p.sched_id
inner join trainer t on s.trainer_id = t.trainer_id
order by
 hier.depth, hier.sched_id;

drop temporary table if exists hier;
drop temporary table if exists tmp;

end #

delimiter ;

-- TEST DATA

insert into trainer (name) values ('trainer 1'),('trainer 2'),('trainer 3');

insert into schedule (name, trainer_id, parent_sched_id) values
('Schedules',null, null), 
   ('Schedule 1',3,1), 
   ('Schedule 2',2,1), 
      ('Schedule 2-1',1,3), 
      ('Schedule 2-2',3,3), 
        ('Schedule 2-2-1',3,5), 
        ('Schedule 2-2-2',2,5), 
           ('Schedule 2-2-2-1',1,7);

-- TESTING

-- just call this stored procedure from your php

call schedule_hier(3);

sched_id    schedule_name   parent_sched_id parent_schedule_name    trainer_id  trainer_name    depth
========    =============   =============== ====================    ==========  ============    =====
3           Schedule 2          1         Schedules                2              trainer 2    0
4           Schedule 2-1        3         Schedule 2               1              trainer 1    1
5           Schedule 2-2        3         Schedule 2               3              trainer 3    1
6           Schedule 2-2-1      5         Schedule 2-2             3              trainer 3    2
7           Schedule 2-2-2      5         Schedule 2-2             2              trainer 2    2
8           Schedule 2-2-2-1    7         Schedule 2-2-2           1              trainer 1    3