We help IT Professionals succeed at work.

php/mysql parent/child query

sukotto100
sukotto100 asked
on
I am trying to display something like the question posed in this question.
I have a table which is something like:

id name parent_id
1 |project |
2 |project2 |
3 |subproject |2
4 |subproject2 |2
5 |project3 |

And am trying to display parent/child (project/subproject) as well as projects without children

I have the code to where it displays any project with subprojects:
project2:subproject
project2:subproject2

but I need to add:
project
project3

so it looks like:
project
project2:subproject
project2:subproject2
project3

function getCategories($parent_id) {
	$res = mysql_query("SELECT * FROM projects WHERE parent_id=$parent_id");
	if (!mysql_num_rows($res) || mysql_error()) return false;
  $return = '';
	while($r = mysql_fetch_assoc($res)) {
		$sub = getCategories($r['id']); //is it ID?
                if ($sub != false) $return .= $r['name'].':'.$sub;
                else {
                  $par = mysql_query("SELECT * FROM projects WHERE id=$parent_id");
                  $p = mysql_fetch_assoc($par);
                  $return .=$p['name'].':'.$r['name'].'<br \>';
                }
	}
	return $return;
}

Open in new window

Comment
Watch Question

Commented:
Assign all the root level projects a project id of 0 and make your all projects or default projects query for a parent id of 0. This will return all the toplevel projects...

And update this query:
$res = mysql_query("SELECT * FROM projects WHERE parent_id=$parent_id");

to:
$res = mysql_query("SELECT id FROM projects WHERE parent_id=$parent_id");

Just return the data you need, it is poor practice to return more and teaches you for later queries, when you are parsing data from a huge table, returning everything costs you in efficiency..

Just my thoughts.

Please let me know if this answers your question or if you need additional information..

Thanks,

P1isken

Commented:
Oooops.. Ugh, change "Assign all the root level projects a project id of 0" to "Assign all the root level projects a Parent id of 0" ;)

Sorry for the typo...

Thanks,

P1isken

Commented:
Sorry, tired here... Took a second to look at it and here should be your working php syntax...

Please let me know if this does not work out for you..

 
function getCategories($parent_id) {
  $res = mysql_query("SELECT id FROM projects WHERE parent_id=$parent_id");
  if (!mysql_num_rows($res) || mysql_error()) return false;
  $return = '';
  if ($parent_id == '0') {
    foreach ( $res as $root_parent) {
      $subs = mysql_query("SELECT id FROM projects WHERE parent_id=$root_parent");
      while($r = mysql_fetch_assoc($subs)) {
        $sub = getCategories($r['id']);
        if ($sub != false) {
          $return .= $r['name'].':'.$sub;
        } else {
          $par = mysql_query("SELECT * FROM projects WHERE id=$parent_id");
          $p = mysql_fetch_assoc($par);
          $return .=$p['name'].':'.$r['name'].'<br \>';
        }
      }
    }
  } else {
    while($r = mysql_fetch_assoc($res)) {
      $sub = getCategories($r['id']); //is it ID?
      if ($sub != false) {
        $return .= $r['name'].':'.$sub;
      } else {
        $par = mysql_query("SELECT * FROM projects WHERE id=$parent_id");
        $p = mysql_fetch_assoc($par);
        $return .=$p['name'].':'.$r['name'].'<br \>';
      }
    }
  }
  return $return;
}

Open in new window


PS. You still need to set the root categories with a parent_id of 0 ;)

Author

Commented:
I really appreciate your help! I won't be able to update the parent_id; I'm connecting to a redmine db used for bug tracking and don't want to risk affecting other functionality

Author

Commented:
The real query I need is more complex, it will be a date range. So maybe can I use id as the arg and look for where parent_id is not null?
I fixed it by creating a temp table and updating the parent/child relationship so the values are part of a field, then just exporting. I wish I could have done something more elegant, but it works.

Author

Commented:
I had to come up with something on my own.