PHP - Nested Unordered Lists - Anybody?

Hello PhpFreaks... been a while since I stopped by. I need to reach out for some assistance. The task I am trying to complete seems so simple, but I can't get my brain around it and have searched through these forums and the almighty google and have not found what I need. So here is what I need some directions on.

I am trying to create a multi-level un-ordered list using PHP & MySQL. It is going to be a database driven menu with an unknown number of parent items with 0 or more child items. Surely I am not the first one to need to solve this problem, but cannot find the simplest solution that gets me what I want.

Ok.. so I got a table setup like this with some test data in it....

Code: [Select]
CREATE TABLE IF NOT EXISTS `navigation` (
  `linkId` int(11) NOT NULL AUTO_INCREMENT,
  `displayName` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL,
  `parentId` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`linkId`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;


INSERT INTO `navigation` (`linkId`, `displayName`, `url`, `parentId`) VALUES
(1, 'Home', '/', 0),
(2, 'City Hall', '/city_hall/', 0),
(3, 'City Council', '/city_council', 0),
(4, 'Upcoming Meetings', '/city_council/upcoming_meetings.php', 3),
(5, 'Past Minutes', '/city_council/past_minutes', 3),
(6, 'Council Members', '/city_council/council_members.php', 3),
(7, 'Forms & Downloads', '/downloads', 0),
(8, 'Parks & Recreation', '/parks', 0),
(9, 'Park Name', '/parks/park.php', 8),
(10, 'Another Link', '/parks/link2.php', 8);

I have a query and loop that grabs the entire menu, and places it in 2 arrays to separate the top-level parent items and the items that have a parent id != 0.


$items = $db->getData("SELECT linkId, displayName, url, parentId FROM navigation ORDER BY parentId, linkId ASC");
while ($row = mysql_fetch_object($items['result'])) {
    if ($row->parentId == 0) {
        $parent_menu[$row->linkId]['label'] = $row->displayName;
        $parent_menu[$row->linkId]['link'] = $row->url;
    } else {
        $sub_menu[$row->linkId]['parent'] = $row->parentId;
        $sub_menu[$row->linkId]['label'] = $row->displayName;
        $sub_menu[$row->linkId]['link'] = $row->url;
    }
}

That gives me an array that looks like this.

Code: [Select]
Array(
    [1] => Array (
            [label] => Home
            [link] => /
        )

    [2] => Array (
            [label] => City Hall
            [link] => /city_hall/
        )

    [3] => Array(
            [label] => City Council
            [link] => /city_council
        )

    [7] => Array(
            [label] => Forms & Downloads
            [link] => /downloads
        )

    [8] => Array(
            [label] => Parks & Recreation
            [link] => /parks
        )

)

Array
(
    [4] => Array(
            [parent] => 3
            [label] => Upcoming Meetings
            [link] => /city_council/upcoming_meetings.php
        )

    [5] => Array(
            [parent] => 3
            [label] => Past Minutes
            [link] => /city_council/past_minutes
        )

    [6] => Array(
            [parent] => 3
            [label] => Council Members
            [link] => /city_council/council_members.php
        )

    [9] => Array(
            [parent] => 8
            [label] => Park Name
            [link] => /parks/park.php
        )

    [10] => Array(
            [parent] => 8
            [label] => Another Link
            [link] => /parks/link2.php
        )
)

Taking that array and doing a couple loops, gets me close (sorta.. I realize that there needs to be recursion for this to work).


echo '<ul>'."\r\n";
foreach($parent_menu as $pid => $pvalue){
echo '  <li><a href="'.$pvalue['link'].'">'.$pvalue['label'].'</a>'."\r\n";

foreach($sub_menu as $sid => $svalue){
if($svalue['parent'] == $pid){
echo '    <li>'.$svalue['label'].'</li>'."\r\n";
}
}

echo '</li>'."\r\n";
}
echo '</ul>'."\r\n";


That gets me a list that is not quite correct markup. The nested <ul> items are missing because I could not determine the best way to do this.

The goal for the data presented here is to be marked up like this.

Code: [Select]
<ul>
  <li><a href="/">Home</a></li>
  <li><a href="/city_hall">City Hall</a></li>
  <li><a href="/city_council">City Council</a>
    <ul>
      <li><a href="/city_council/upcoming_meetings.php">Upcoming Meetings</a></li>
      <li><a href="/city_council/past_minutes">Past Minutes</a></li>
      <li><a href="/city_council/council_members.php">Council Members</a></li>
    </ul>
  </li>
  <li><a href="/downloads">Forms &amp; Downloads</a></li>
  <li><a href="/parks">Parks &amp; Recreation</a>
    <ul>
      <li><a href="/parks/park.php">Park 1</a></li>
      <li><a href="/parks/link2.php">Another Link</a></li>
    </ul>
  </li>
</ul>

Thats where I am at here. Any ideas or tips would be helpful and appreciated. So anyone solved this problem before? Sorry for the long post, but I wanted to give all information and code that I have.

Thanks in advance,

Nathan

I want to produce a pdf document using mysql and TCPDF.

I can do this OK in some circumstances but I now have a problem. I have two tables - documents and parties - they are linked by a common field docid.

I have for table documents, the fields:
docid, doctitle, idcode

and for the parties table:
partyid, docid, party (the field party is the name of the party)

For any document there could be any number of parties and for a single idcode there could be any number of documents.

For example, for idcode = 12345 there might be 2 documents called doctitle 1 and doctitle 2. For doctitle 1 there are 3 parties to it party1, party 2 and party 3. For doctitle 2 there are two parties - party4 and party 5.

For any idcode (which is passed through by the url) I want the pdf to give a list such as:

Doctitle 1:
party name 1
party name 2
party name 3

Doctitle 2:
party name 4
party name 5

Part of my code is:

mysql_select_db($database_process, $process);
$result2 = mysql_query("SELECT parties.docid, GROUP_CONCAT(party SEPARATOR '<br>') AS partylist, documents.doctitle
FROM parties INNER JOIN documents ON parties.docid=documents.docid
                    WHERE parties.idcode = '$appidpassed'
               GROUP BY parties.docid
                    LIMIT 1
                    ") or die(mysql_error());   

while($row = mysql_fetch_array($result2))
  {
    $doctitle = $row['doctitle'];
   $parties = $row['partylist'];
   
   
}   

and then further down the page:

$doctitle<br>
$parties

This only gives one document and the parties associated with it. I know the solution is probably easy but I can't see it.

Does anyone have any ideas?

Hi everyone,

I've read through the FAQs for Dynamic Dropdown Menus on this site, as well as others, and I can't figure out why my code won't work. I have two dropdown boxes that need to be populated with data from a mysql table; one menu for route types, and one for route numbers. When I choose the route from the 'Route' menu, the 'Number' menu automatically populates with all of the possible numbers, rather than only those that correspond with the route type. I can tell that the problem has something to do with the value of 'Route' not being recognized, but I don't know why. I'm a beginner when it comes to PHP, so any suggestions or help would be much appreciated!  Thanks!

The code is as follows:

Code: [Select]

<html>
<body>
<basefont face='calibri' color='#7E2217'>
<?php 
// set variables 
$mileTable = $_GET['mileTable']; 
$routeType = isset($_POST['Roadtype'])? $_POST['Roadtype']: 0; 
include 'opendbMile.php'; 
include 'error.php'; 
    // Connect to the MySQL DBMS 
   if (!($connection = @ mysql_connect($hostName, 
                                                                           $username, 
                                                                           $password))) 
          die("Could not connect"); 
   if (!mysql_select_db($databaseName, $connection)) 
         showerror(  ); 
 
    // Start a query... 
    $query = "SELECT ID, 
                                         Roadtype 
                          FROM Alabama 
                          GROUP BY Roadtype"; 
    // execute the SQL statement 
    $result = mysql_query($query, $connection) or die(mysql_error()); 
echo '<form name="mileform" method="post" action="MileQuery.php">'; 
echo '<p>Route: 
<select name="routeType" id="routeType" onchange="this.form.submit();"> 
<option value="0"'.($routeType == 0? ' SELECTED': '').'>Route</option>'; 
while($row = mysql_fetch_array($result)){ 
    echo ' 
            <option value="'.$row[0].'"'.(($routeType == $row[0])? ' SELECTED': '').'>'.$row[1].'</option>'; 
} 
    echo ' 
    </select> 
</p>'; 
// create the SQL statement 
$query2 = "SELECT ID, 
          Roadnumber 
                   FROM Alabama 
                   GROUP BY Roadnumber"; 
         if($mnucategory != 0){    // Filter road numbers 
    $query2 .= " WHERE Roadtype='".$routeType."'"; 
    } 
// execute the SQL statement 
$result2 = mysql_query($query2, $connection) or die(mysql_error()); 
echo '<p>Number: 
    <select name="routeNumber" id="routeNumber" onchange="this.form.submit();"> 
            <option value="0"'.($routeNumber == 0? ' SELECTED': '').'>Number</option>'; 
while($row2 = mysql_fetch_array($result2)){ 
    echo ' 
            <option value="', $row2[0].'"'.(($routeNumber == $row2[0])? ' SELECTED': '').'>'. $row2[1], '</option>'; 
} 
    echo ' 
    </select> 
</p>'; 
 // Close the DBMS connection 
   mysql_close($connection); 
?>
</form>
</body>
</html>

Hi

I am having problems trying to view some data and wondered if anyone could help.

I have a table called 'parties' which contains fields - partyid, docid and party. There is another table called 'documents' which has fields - docid and doctitle.

I have a number of documents and for each document there are a number of parties - ie people linked to that document.

What I would like to do is get a view on screen (and also printed out) that has the title of the document followed by a list of the parties associated with that document.

For example, if in the documents table there was:

|docid   |doctitle           |
|1           |first document   |
|2           |second document|

and in the parties table there was:
|partyid   |docid   |party   |
|1      |1        |Fred    |
|2      |1           |Jim     |
|3      |1           |Jane   |
|4      |2        |Peter   |
|5      |2        |Fred           |

I could get a view that looked like:

First document:
Fred
Jim
Jane

Second document:
Peter
Fred

I suspect it may involve arrays but I have never used them so any guidance would be very welcome.

Thanks