PHP recursive step through mysql table.

[synking]

I am trying to setup a navigtion link section on my home page. what i have is a table called section in mysql and this is the way it is setup up.

+----+-----------+----------+
| ID | name      | parentid |
+----+-----------+----------+
|  2 | News      |        0 |
|  3 | Service   |        0 |
|  4 | Terms     |        3 |
|  5 | Recent    |        2 |
|  6 | Policy    |        4 |
|  7 | Downloads |        0 |
|  8 | Links     |        0 |
|  9 | Tech      |        8 |
+----+-----------+----------+

I have a start on the php with this

<?PHP
ERROR_REPORTING ('E_ALL ^ E_NOTICE');
require_once ('../includes/DbConnector.php');


$connector = new DbConnector();

$result = $connector->query('SELECT * FROM cmssection');
  if(!$result) {
      echo ('<p class="error">Error from query: ' .$connector->getSqlError(). '</p>');

} else {

     echo ("<table border=\"0\" width=\"180\"> \n");
     echo ("<tr> \n");
     echo ("<td><h5>Main Links</h5></td></tr> \n");

       while ($row = $connector->fetchArray($result)) {

               echo ("\n <tr> \n");
               echo ('<td width="100%" bgcolor="#E6E6E6"><a href="test.php?ID='.$row['ID'].'" class="menulink">'.$row['name']. '</a></td>');
               echo ("\n </tr> \n");
 }
   echo ("</table> \n");
}


?>

but what i want to do is place each row under it's parent can anyone help

[langsor]

I love programming, programming is my friend ... :-\

Mapping is tricky stuff -- at least for my brain.
Here I've made a multidimensional array of your menu hierarchy from your database results...
Of course then you have to write it to your table-menu structure -- but first things first.

<?php

$rows[] = array( 2, 'News', 0 );
$rows[] = array( 3, 'Service', 0 );
$rows[] = array( 4, 'Terms', 3 );
$rows[] = array( 5, 'Recent', 2 );
$rows[] = array( 6, 'Policy', 4 );
$rows[] = array( 7, 'Downloads', 0 );
$rows[] = array( 8, 'Links', 0 );
$rows[] = array( 9, 'Tech', 8 );

// Equivelant to: while ( $row = mysql_fetch_row( $result ) )
$i = 0;
while( $row = $rows[$i] ) {
  $mapped[$row[0]] = array( label => $row[1], child => array() );
  $source[$row[2]] = $row[0];
  $i ++;
}

krsort( $source );

foreach ( $source as $pid => $id ) {
  if ( $pid && array_key_exists( $pid, $mapped ) ) {
    $values =  $mapped[$id];
    unset( $mapped[$id] );
    $mapped[$pid]['child'][] = $values;
  }
}

print_r( $mapped );

?>

Hope this helps you get started anyway

...

[langsor]

With a couple minor modifications and a recursive nested list writing function we get this ...
But without the fancy formatting -- that I did by hand to check my work.

<ul>
    <li><a href="test.php?ID=2" class="menulink">News</a></li>
    <li>
      <ul>
        <li><a href="test.php?ID=5" class="menulink">Recent</a></li>
      </ul>
    </li>
    <li><a href="test.php?ID=3" class="menulink">Service</a></li>
    <li>
      <ul>
        <li><a href="test.php?ID=4" class="menulink">Terms</a></li>
        <li>
          <ul>
            <li><a href="test.php?ID=6" class="menulink">Policy</a></li>
          </ul>
        </li>
      </ul>
    </li>
    <li><a href="test.php?ID=7" class="menulink">Downloads</a></li>
    <li><a href="test.php?ID=8" class="menulink">Links</a></li>
    <li>
      <ul>
        <li><a href="test.php?ID=9" class="menulink">Tech</a></li>
      </ul>
    </li>
  </ul>

Here's the revised code in total ...

<?php

$rows[] = array( 2, 'News', 0 );
$rows[] = array( 3, 'Service', 0 );
$rows[] = array( 4, 'Terms', 3 );
$rows[] = array( 5, 'Recent', 2 );
$rows[] = array( 6, 'Policy', 4 );
$rows[] = array( 7, 'Downloads', 0 );
$rows[] = array( 8, 'Links', 0 );
$rows[] = array( 9, 'Tech', 8 );

// Equivelant to: while ( $row = mysql_fetch_row( $result ) )
$i = 0;
while( $row = $rows[$i] ) {
  $mapped[$row[0]] = array( label => $row[1], query => $row[0], child => array() );
  $source[$row[2]] = $row[0];
  $i ++;
}

krsort( $source );

foreach ( $source as $pid => $id ) {
  if ( $pid && array_key_exists( $pid, $mapped ) ) {
    $values =  $mapped[$id];
    unset( $mapped[$id] );
    $mapped[$pid]['child'][] = $values;
  }
}

$menu = recursive_list_menu( $mapped );

function recursive_list_menu ( $data ) {
  $html = "<ul>";
  foreach ( $data as $values ) {
    $label = $values['label'];
    $query = $values['query'];
    $child = $values['child'];
    $html .= "<li><a href=\"test.php?ID=$query\" class=\"menulink\">$label</a></li>";
    if ( !empty( $child ) ) {
      $html .= "<li>".recursive_list_menu( $child )."</li>";
    }
  }
  $html .= "</ul>";
  return $html;
}

print $menu;
//print_r( $mapped );

?>

I hate tables, so you're on your own there, but maybe this will give you an example of one way to do this stuff.

Good luck

...

[synking]

With a couple minor modifications and a recursive nested list writing function we get this ... But without the fancy formatting -- that I did by hand to check my work.

<ul>
    <li><a href="test.php?ID=2" class="menulink">News</a></li>
    <li>
      <ul>
        <li><a href="test.php?ID=5" class="menulink">Recent</a></li>
      </ul>
    </li>
    <li><a href="test.php?ID=3" class="menulink">Service</a></li>
    <li>
      <ul>
        <li><a href="test.php?ID=4" class="menulink">Terms</a></li>
        <li>
          <ul>
            <li><a href="test.php?ID=6" class="menulink">Policy</a></li>
          </ul>
        </li>
      </ul>
    </li>
    <li><a href="test.php?ID=7" class="menulink">Downloads</a></li>
    <li><a href="test.php?ID=8" class="menulink">Links</a></li>
    <li>
      <ul>
        <li><a href="test.php?ID=9" class="menulink">Tech</a></li>
      </ul>
    </li>
  </ul>

Here's the revised code in total ...

<?php

$rows[] = array( 2, 'News', 0 );
$rows[] = array( 3, 'Service', 0 );
$rows[] = array( 4, 'Terms', 3 );
$rows[] = array( 5, 'Recent', 2 );
$rows[] = array( 6, 'Policy', 4 );
$rows[] = array( 7, 'Downloads', 0 );
$rows[] = array( 8, 'Links', 0 );
$rows[] = array( 9, 'Tech', 8 );

// Equivelant to: while ( $row = mysql_fetch_row( $result ) )
$i = 0;
while( $row = $rows[$i] ) {
  $mapped[$row[0]] = array( label => $row[1], query => $row[0], child => array() );
  $source[$row[2]] = $row[0];
  $i ++;
}

krsort( $source );

foreach ( $source as $pid => $id ) {
  if ( $pid && array_key_exists( $pid, $mapped ) ) {
    $values =  $mapped[$id];
    unset( $mapped[$id] );
    $mapped[$pid]['child'][] = $values;
  }
}

$menu = recursive_list_menu( $mapped );

function recursive_list_menu ( $data ) {
  $html = "<ul>";
  foreach ( $data as $values ) {
    $label = $values['label'];
    $query = $values['query'];
    $child = $values['child'];
    $html .= "<li><a href=\"test.php?ID=$query\" class=\"menulink\">$label</a></li>";
    if ( !empty( $child ) ) {
      $html .= "<li>".recursive_list_menu( $child )."</li>";
    }
  }
  $html .= "</ul>";
  return $html;
}

print $menu;
//print_r( $mapped );

?>

I hate tables, so you're on your own there, but maybe this will give you an example of one way to do this stuff.

Good luck

...

That help more then you know thanks so much. just made relize i was over thinking horribly.

[langsor]

Glad to have helped.

BTW, it took me some time to wrap my brain around that little $mapped routine ;-)

[synking]

hey all the sudden when i add this to pull data from a database it gives me a bunch of errors

Notice: Use of undefined constant label - assumed 'label' in /home/jking/public_html/test/new.php on line 25

Notice: Uninitialized string offset: 1 in /home/jking/public_html/test/new.php on line 25

Notice: Use of undefined constant query - assumed 'query' in /home/jking/public_html/test/new.php on line 25

Notice: Use of undefined constant child - assumed 'child' in /home/jking/public_html/test/new.php on line 25

Notice: Uninitialized string offset: 2 in /home/jking/public_html/test/new.php on line 26

Notice: Use of undefined constant label - assumed 'label' in /home/jking/public_html/test/new.php on line 25

Notice: Use of undefined constant query - assumed 'query' in /home/jking/public_html/test/new.php on line 25

Notice: Use of undefined constant child - assumed 'child' in /home/jking/public_html/test/new.php on line 25

[langsor]

hey all the sudden when i add this to pull data from a database it gives me a bunch of errors

Are you talking about the code I wrote above? If not you should probably start a new thread on this subject.

If you are talking about my code then maybe you could show us thenew.php file in question.

Thanks

[synking]

i am talking about the code you posted which worked fine the way it was but i attempted to have it connect to mysql database and i got the errors here is what i have changed

$result = $connector->query('SELECT * FROM cmssection');
if(!$result) {
      echo ('<p class="error">Error from query: ' .$connector->getSqlError(). '</p>');

} else {

while ($rows = mysql_fetch_row($result)) {
$i = 0;
while( $row = $rows[$i] ) {
  $mapped[$row[0]] = array( label=>$row[1], query=>$row[0], child=>array() );
  $source[$row[2]] = $row[0];
  $i ++;
}

[langsor]

Sorry synking, didn't recognize your alias and thought you were someone new posting a question to this thread.

I'm assuming you've closed your while block with curly brakcets, but had a copy-paste error here

You can get rid of the iterator lines and my original while loop...

$i = 0;
$i ++;
while( $row = $rows[$i] ) {

... and change your$rows to $row ...

Now try it ...

$result = $connector->query('SELECT * FROM cmssection');
if(!$result) {
  echo ('<p class="error">Error from query: ' .$connector->getSqlError(). '</p>');
} else {
  while ( $row = mysql_fetch_row($result) ) {
    $mapped[$row[0]] = array( label=>$row[1], query=>$row[0], child=>array() );
    $source[$row[2]] = $row[0];
  }
}

:-)

[synking]

sweet i should have been able to see that thanks guy. but i noticed something i should be able to correct that it prints the ul each time it has to print li as well.

[langsor]

No problem, we all get into the plug-and-play mindset ...

Enjoy

[langsor]

it prints the ul each time it has to print li as well.

I believe the html standard (if you want your code to validate: http://validator.w3.org/ ) is that a nested must be inside a container ...

So this is not valid (If I remember correctly) ...

<ul>
  <li>some text</li>
  <li>
    <li>some nested text</li>
  </li>
</ul>

But this is ...

<ul>
  <li>some text</li>
  <li>
    <ul>
      <li>some nested text</li>
    </ul>
  </li>
</ul>

But it displays as nested lists either way.

[codebirth]

Sorry to revive an old thread but since the code posted by langsor helped me a lot I thought I should contribute.

The code works fine except if a child has brothers. The changes below will work for any amount of childs and brothers.

$i = 0;
while( $row = $rows[$i] ) {
	$mapped[$row[0]] = array( label => $row[1], query => $row[0], child => array() );
	$source[$row[2]][] = $row[0];
	$i ++;
}

krsort( $source );

foreach ( $source as $pid => $aid ) {
	foreach ($aid as $id)
	{
		if ( $pid && array_key_exists( $pid, $mapped ) ) {
			$values = $mapped[$id];
			unset( $mapped[$id] );
			$mapped[$pid]['child'][] = $values;
		}
	}
}

Note the changes in lines 4, 10 and 11.

Hope this helps as it helped to me.

[langsor]

Hi Codebirth,

It's nice to see that these threads are still helping people. I haven't been active in a long time but I do enjoy coding and helping people with these types of problems -- I've been helped my share too.

Thanks for the refinements, this kind of work is always improvable.

[updatepanel]

$rows[] = array(194, 'Elddis Autoquest 115', 204 );
$rows[] = array(175, 'Home', 0 );
$rows[] = array(196, 'Elddis Autoquest 120', 204 );
$rows[] = array(206, 'New Motorhomes', 0 );
$rows[] = array(135, 'Used Motorhomes', 0 );
$rows[] = array(195, 'Elddis Autoquest 130', 204 );
$rows[] = array(137, 'Caravans', 0 );
$rows[] = array(125, 'Vehicle Search', 0 );
$rows[] = array(168, 'Warranty', 0 );
$rows[] = array(127, 'Location', 0 );
$rows[] = array(198, 'Contact Us', 0 );
$rows[] = array(205, 'Elddis Aspire Models', 0 );
$rows[] = array(167, 'About Us', 198 );
$rows[] = array(204, 'Elddis Autoquest Models', 0 );
$rows[] = array(209, 'tester', 167 );

I have the above results but for some reason it breaks the menu list and association. Can anybody assist is solving the problem?