0

I need help. I have table name called "Tasks", below 

SNO Taskname parentid
---------------------
46  Task-1     0
47  Task-2    46
48  Task-3    47
49  Task-4    46
50  task-5    47
51  task-6    50

For example, I am in Task page 48, now we would show the related Tasks no. of 48. So, the output would be 46,47,49,50,51.

I tried the code below, but it does not work:

function getTask($task_id) {                    
    $rs=mysql_query("select task_id,taskname,p_id from task where task_id='".$task_id."'");
    $rs_req=mysql_fetch_array($rs) or die(mysql_error());                    
    $reqt=$rs_req['p_id'];                   
    return $reqt;   
}

$task = getTask($task_id);               
$pid = $task;
$related_tasks = [];                

while ($pid != 0) {                 
    $pid = getTask($pid);
    $relatedTasks[] = $pid;
}

print_r($relatedTasks);
Philippe Signoret
  • 13,299
  • 1
  • 40
  • 58
IRAP
  • 1
  • 6
  • 1
    It's basically a recursive query. This might be a good starting point: http://stackoverflow.com/q/5291054 – Robert Harvey May 06 '15 at 16:32
  • possible duplicate of [How to create a MySQL hierarchical recursive query](http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) – AdamMc331 May 06 '15 at 16:38

1 Answers1

1

Relational databases are not meant to deal with recursive queries.

You can do it by selecting the first the row with ID 48 and then recursively check of parentId != 0 and execute another query if so. Something like

$task = getTask(48);
$related_tasks = [];
while( $task['parentID'] != 0) {
    $task = getTask( $task['parentId']);
    $relatedTasks[] = $task;
}

Take a look at How to create a MySQL hierarchical recursive query.

Community
  • 1
  • 1
gotha
  • 489
  • 1
  • 5
  • 20