Dynamic menu creation using php/mysql

Hello Experts,

We are developing an php/mysql application where in we are planning a menu as seen in the following website( http://www.xtrememac.com/ ).The menu options should come from the database(mysql), so basically its dynamically generated menu.. can anybody point me to any good tutorial(creating dynamically generated menu[2,3 level menus] using php/mysql]).Googled a lot but could'nt find a good howto/tutorial for this job Sad

plz help me out.

Regards,
Swiftguy.
swiftguyAsked:
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.

maUruCommented:
you are going to need a parent-child tree to pull this off:

they basically go along the lines of:

ID, pID, Name

where ID is the ID of the element, pID is the parent ID (or 0 if it is a top level element) and then finally the name and the link

its actually tougher to write a program to /make and change/ this relationship tree in mysql rather than pulling it from the database afterwards and displaying (which is just a matter of simple css)

there are 3 things you must do:

1. make a parent tree database
2. make a page to update/add/delete nodes
3. make a page to retrieve the results
4. apply the css

here are the codes for each:

1.just make a table in mysql:
ID (primary key) integer
pID integer
name varchar

2.
<?

function delchilds($pid) {
      $q = "SELECT * FROM menu WHERE pID = $pid";
      $r = mysql_query($q);
      if (mysql_num_rows($r) == 0) {
            mysql_query("DELETE FROM menu WHERE ID = $pid");
      }
      else {
            while ($row = mysql_fetch_assoc($r)) {
                  delchilds($row['ID']);
            }
            mysql_query("DELETE FROM menu WHERE ID = $pid");
      }
      return;
}

function ischild($id, $newpid) {
      if ($newpid == 0) return false;
      $q = "SELECT * FROM menu WHERE ID = $newpid";
      $r = mysql_query($q);
      $cpid = mysql_result($r, 0, 'pID');
      if ($cpid == $id || $id == $newpid)
            return true;
      return ischild($id, $cpid);
}


if ($action == 'update') {
      if (ischild($id, $pid)) {
            echo 'cannot put this as a sub category of itsself!';
      }
      else {
            $q = "UPDATE menu SET pID = $pid, Name = '$name' WHERE ID = $id";
            mysql_query($q);
      }
}
else if ($action == 'new') {
      $q = "INSERT INTO menu SET pID = $pid, Name = '$name'";
      mysql_query($q);
      $id = mysql_insert_id();
}
else if ($action == 'delete') {
      if ($confirm == 'yes') {
            delchilds($id);
      }
      else {
            echo 'must confirm';
      }
}
$q = "SELECT * FROM menu WHERE ID = $id";
$r = mysql_query($q);
while ($row = mysql_fetch_assoc($r)) {
      $name = $row['Name'];
      $pid = $row['pID'];
}
?>
<br>
ID = <?=$id?><br>
<form action="<?=$PHP_SELF?>" method="POST" >
<table border=1>
      <tr>
            <td>Name:<input type="text" name="name" value="<?=$name?>"></td>
            <td>Parent:
                  <select name="pid">
                        <option value="0">ROOT</option>
<?
$recordset = mysql_query("SELECT ID, pID, Name FROM menu ORDER BY Name ASC"); // query

while($row = mysql_fetch_assoc($recordset)) {
      $all[$row['ID']] = $row; // add everything on one big array
}

$top = array_filter($all, 'topcat'); // extract the top parents (with pID = 0)
$depth = 0; // depth of our traversing, this is for indentation

foreach($top as $cat) { // for every top parent
      selbuildchild($cat); // build its children
}
?>
                  </select>
            </td>
      </tr>
</table>
<input type="hidden" name="id" value="<?=$id?>">
<input type="submit" name="action" value="update">
<input type="submit" name="action" value="new">
<input type="submit" name="action" value="delete">
<input type="checkbox" name="confirm" value="yes">
</form>

<?
$fullindents = false; // change this to true if you want full indentation
$recordset = mysql_query("SELECT ID, pID, Name FROM menu ORDER BY Name ASC"); // query

$all = array();
while($row = mysql_fetch_assoc($recordset)) {
      $all[$row['ID']] = $row; // add everything on one big array
}

$top = array_filter($all, 'topcat'); // extract the top parents (with pID = 0)
$depth = 0; // depth of our traversing, this is for indentation

echo "<ul>\n";
foreach($top as $cat) { // for every top parent
      buildchild($cat); // build its children
}
echo "</ul>\n";

function buildchild($cat) { // the recursive builder
      global $depth, $fullindents;
      $depth++;
      $link = '<a href="' . $PHP_SELF . '?id=' . $cat['ID'] . '">';
      if ($fullindents) {
            echo t() . "<li>\n" . t(1) . $link . $cat['Name'] . '</a>';
      }
      else {
            echo t() . "<li>" . $link . $cat['Name'] .'</a>';
      }

      if ($children = haschildren($cat['ID'])) { // check if the current element has children and build them
            $ul = true;
            $depth++;
            echo "\n" . t() . "<ul>\n";
            foreach($children as $child) {
                  buildchild($child);
            }
            echo t() . "</ul>";
            $depth--;
      }
      if ($fullindents) {
            echo "\n" . t() . "</li>\n";
      }
      else {
            if ($ul) echo "\n" . t();
            $ul = false;
            echo "</li>\n";
      }
      $depth--;
}

function selbuildchild($cat) { // the recursive builder
      global $depth, $pid;
      $depth++;

      $sel = ($pid == $cat['ID']) ? ' selected' : '';
      echo "\t\t\t\t<option " . $sel . " value=\"" . $cat['ID'] . "\">" . t(0, '--') . '>' . $cat['Name'] . "</option>\n";

      if ($children = haschildren($cat['ID'])) { // check if the current element has children and build them
            $ul = true;
            $depth++;
            foreach($children as $child) {
                  selbuildchild($child);
            }
            $depth--;
      }
      $depth--;
}


function haschildren($id) { // checks if an element has children
      global $all;
      foreach($all as $cat) {
            if ($cat['pID'] == $id) {
                  $result[] = $cat;
                  unset($all[$cat['ID']]);      // unset this value so that we dont
                                                            // search it again when looking through
                                                            // the array next time, this gives a
                                                            // ~20% increase in speed to the script
            }
      }
      if (count($result) > 0) {
            return $result; // return the children if found
      }
      return false; // otherwise return false
}

function t($x=null,$s="  ") { // indentation function, you can change spaces to \t (tabs) if you like
      global $depth;
      return str_repeat($s, $depth+$x);
}
function topcat($row) { // used for the array_filter to weed out categories with pID != 0
      return $row['pID'] == 0;
}


?>

3. the lower part of (2.) contains the code to display the unordered list

4. apply the css, here is one:
http://phoenity.com/newtedge/horizontal_nav/

google for others.

you're gonna have to modify my code a bit to allow for extra things like links/icons etc

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
x2dml02Commented:
You could also try coolmenus

http://www.dhtmlcentral.com/projects/coolmenus/

In the download there is an example for using php. From what I can gather it is no longer being developed but it still works with all browsers I have tried it in and is very customable.

I made a small php script to autamatticly generate it from the records in the database (dynamic).

Worth looking at.

Kevin.
swiftguyAuthor Commented:
Sorry for this late reply(i was out of town).

Thx Mauru and x2dml02 for responding to my query.

Im trying Maurus' solution,Mauru thx again for taking pains in providing with the code, i will get back to u once i run your code.

swiftguyAuthor Commented:
Maurus' step by step helped in creating my own custom code to retrieve menu items from DB using php.

x2dml02 assisted in solving the prob, his tip of using coolmenus did help me in zeroing on the perfect menu.BTW 'coolmenu' is not providing php integration example in its documentation,had to add my own custom php code snippet to get the things going!!!
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.