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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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
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
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 ;)
Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

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
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?
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.

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.
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.