Clicky

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; }                             
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 

Select allOpen in new window

asked 12/03/2011 12:31

sukotto100's gravatar image

sukotto100 ♦♦


7 Answers:
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
link

answered

P1isken's gravatar image

P1isken

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
link

answered 2011-12-03 at 20:56:33

P1isken's gravatar image

P1isken

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

 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
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;
}


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

answered 2011-12-03 at 20:59:10

P1isken's gravatar image

P1isken

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
link

answered 2011-12-03 at 21:15:34

sukotto100's gravatar image

sukotto100

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

answered 2011-12-03 at 21:41:48

sukotto100's gravatar image

sukotto100

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

answered 2011-12-03 at 21:52:58

sukotto100's gravatar image

sukotto100

I had to come up with something on my own.

link

answered 2011-12-12 at 14:12:32

sukotto100's gravatar image

sukotto100

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×81
×191

Asked: 12/03/2011 12:31

Seen: 395 times

Last updated: 12/16/2011 05:20