php/mysql parent/child query

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

sukotto100Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

P1iskenCommented:
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
0
P1iskenCommented:
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
0
P1iskenCommented:
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 ;)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sukotto100Author 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
0
sukotto100Author 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?
0
sukotto100Author Commented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sukotto100Author Commented:
I had to come up with something on my own.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.