PHP recursive step through mysql table.

Question

synking 0 Newbie Poster

16 Years Ago

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

mysql php

4 Contributors
14 Replies
2K Views
3 Years Discussion Span
Latest Post 12 Years Ago Latest Post by updatepanel

langsor

16 Years Ago

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

16 Years Ago

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

...

langsor

16 Years Ago

Glad to have helped.

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

langsor

16 Years Ago

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 the new.php file in question.

Thanks

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

synking 0 Newbie Poster · Answer 1 · 2008-08-12T20:10:08+00:00

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.

synking 0 Newbie Poster · Answer 2 · 2008-08-14T02:54:35+00:00

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

synking 0 Newbie Poster · Answer 3 · 2008-08-14T03:08:32+00:00

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 · Answer 4 · 2008-08-14T03:17:51+00:00

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 0 Newbie Poster · Answer 5 · 2008-08-14T03:26:24+00:00

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 · Answer 6 · 2008-08-14T03:28:09+00:00

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

Enjoy

langsor · Answer 7 · 2008-08-14T03:34:51+00:00

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 <li></li> must be inside a <ul> 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 6 Newbie Poster · Answer 8 · 2010-04-26T16:38:39+00:00

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 · Answer 9 · 2010-04-27T00:10:24+00:00

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 0 Newbie Poster · Answer 10 · 2012-04-13T13:26:28+00:00

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