unordered list php-sql

Isset1988 · May 17, 2013

Hello my friends,

I want some help with this below:

I i have this example page

My database:

-------------------------------------
| id  | parent_id  |     title      |
-------------------------------------
|  99 |     -      |    container   |
|  1  |     99     |    category1   |
|  2  |     99     |    category2   |
|  3  |     2      |  subcategory1  |
|  4  |     3      |sub-subcategory1|
|  5  |     3      |sub-subcategory2|
|  6  |     2      |  subcategory2  |
|  7  |     99     |    category3   |
-------------------------------------

I want to echo the above with queries. (Imagine that maybe i will have more subcategories or more sub-subcategories ore categories)

<ul>
<li>category1</li>
<li>category2
<ul>
<li>subcategory1
<ul>
<li>sub-subcategory1</li>
<li>sub-subcategory2</li>
</ul>
</li>
<li>subcategory2</li>
</ul>
</li>
<li>category3</li>
</ul>

Any smart idea?

Edited May 17, 2013 by Isset1988

PravinS · May 17, 2013

Refer this url: http://www.phpclasses.org/package/5927-PHP-Display-an-hierarchic-menu-stored-in-a-MySQL-table.html

Isset1988 · May 17, 2013

Thank you for your answer

But this works fine only with tables. Can i replace the tables with li ul?

Barand · May 17, 2013

you could adapt this

my data

+-------------+----------------------+--------+--------+
| category_id | name                 | parent | number |
+-------------+----------------------+--------+--------+
|           1 | ELECTRONICS          |      0 |      0 |
|           2 | TELEVISIONS          |      1 |      2 |
|           3 | TUBE                 |      2 |      8 |
|           4 | LCD                  |      2 |      4 |
|           5 | PLASMA               |      2 |      3 |
|           6 | PORTABLE ELECTRONICS |      1 |      6 |
|           7 | MP3 PLAYERS          |      6 |      2 |
|           8 | FLASH                |      7 |      5 |
|           9 | CD PLAYERS           |      6 |      4 |
|          10 | 2 WAY RADIOS         |      6 |      9 |
+-------------+----------------------+--------+--------+

my code

$sql = "SELECT category_id, name, parent
        FROM category";
$res = mysql_query($sql);
while (list($id, $name, $parent) = mysql_fetch_row($res)) {
    $data[$parent][] = array('id'=>$id, 'name'=>$name);
}
    

// call the recursive function
displayHierarchy($data, 0);

// function to print a category then its child categories 
function displayHierarchy(&$arr, $parent, $indent=0)
{
    $ind = $indent * 50;
    if (isset($arr[$parent]))
    echo "<ul>\n";
    foreach($arr[$parent] as $rec)
    {
        echo "<li>{$rec['name']}</li>";
        echo "<ul>\n";
        if (isset($arr[$rec['id']]))
        displayHierarchy($arr, $rec['id'], $indent+1);
        echo "</ul>\n";
    }
    echo "</ul>\n";
}

my results attached

Isset1988 · May 17, 2013

Thank you for your quick reply.

Everything Works perfect!

Many thanks!

(i'm not so good on functions and i want some time to understood your thoughts, but works perfect )

Edited May 17, 2013 by Isset1988

Isset1988 · May 19, 2013

Barand, i'm back to you with a new question.

I some other columns on my table, like levels. Every category has a level, maybe the level is the same.
I tried this

$sql = "SELECT category_id, name, parent
FROM category WHERE level=1";
$res = mysql_query($sql);
while (list($id, $name, $parent) = mysql_fetch_row($res)) {
$data[$parent][] = array('id'=>$id, 'name'=>$name);
}

Everything works perfect when there is only one entrie with level =1.
When i have 2 fields with the same level, i get a blank page :/

Any idea?

Barand · May 19, 2013

displayHierarchy($data, 0);

The code starts by finding the category with parent=0 (top of the hierarchy) so that needs to be in the records selected.

Isset1988 · May 19, 2013

Can i remove this and display the unordered list of that i have inside the select?

Thank you again for your help!

Barand · May 19, 2013

You just need to create a similar recursive function designed to meet your revised requirements

Isset1988 · May 19, 2013

Ok. thank you for the advice, but is not too simple for me. :/

I will wait if anyone has any idea,

I have this database:

id    parent_id	level	title	        language
1	0	0	ROOT	        *
2	1	1	Uncategorised	*
3	1	1	Uncategorised	*
4	1	1	Uncategorised	*
5	1	1	Uncategorised	*
6	1	1	Uncategorised	*
7	1	1	Uncategorised	*
8	1	1	English	        en-GB
9	1	1	Greek	        el-GR
10	8	2	Category 1	en-GB
11	9	2	Category 1	el-GR
12	10	3	SubCategory 1	en-GB
13	10	3	SubCategory 2	en-GB
14	13	4	SubCategory 2.1	en-GB
15	13	4	SubCategory 2.2	en-GB
16	10	3	SubCategory 3	en-GB
17	11	3	SubCategory 1	el-GR
18	11	3	SubCategory 2	el-GR
19	18	4	SubCategory 2.1	el-GR
20	18	4	SubCategory 2.2	el-GR
21	11	3	SubCategory 3	el-GR

I want to print into ul li all the categories and subcategories with the above select:

SELECT id, title, parent_id FROM conf_categories WHERE level>=3 and language='en-GB'

Edited May 19, 2013 by Isset1988

Barand · May 19, 2013

Having seen the data

$sql = "SELECT id, title, parent_id FROM isset1988 WHERE level>=3 and language='en-GB'";
$res = $db->query($sql);
while (list($id, $name, $parent) = $res->fetch_row()) {
    $data[$parent][] = array('id'=>$id, 'name'=>$name);
}
    
// call the recursive function
displayHierarchy($data, 10);                // start at parent id 10 instead of 0

Barand · May 19, 2013

If you identify the minimum parent value in the selection then you don't have to hardcode the 10

$sql = "SELECT id, title, parent_id FROM isset1988 WHERE level>=3 and language='en-GB'";
$res = $db->query($sql);

$minParent = 999;
while (list($id, $name, $parent) = $res->fetch_row()) {
    $data[$parent][] = array('id'=>$id, 'name'=>$name);
    $minParent = min($minParent,$parent);
}
    
// call the recursive function
displayHierarchy($data, $minParent);

Note it will fail if there is not a chain of parent->child in the selection

Isset1988 · May 20, 2013

Thank you again for your time, to solve my problem

When i use your first answer:

<?
$sql = "SELECT id, title, parent_id FROM table WHERE language='en-GB' and level>=3";
$res = mysql_query($sql, GetMyConnection());
while (list($id, $title, $parent_id) = mysql_fetch_row($res)) {
    $data[$parent_id][] = array('id'=>$id, 'title'=>$title);
}
    
// call the recursive function
displayHierarchy($data, 10);

//function to print a category then its child categories 
	function displayHierarchy(&$arr, $parent_id, $indent=0)
	{
		$ind = $indent * 50;
		if (isset($arr[$parent_id]))
		echo "<ul>\n";
		foreach($arr[$parent_id] as $rec)
		{
			echo "<li>{$rec['title']}</li>";
			echo "<ul>\n";
			if (isset($arr[$rec['id']]))
			displayHierarchy($arr, $rec['id'], $indent+1);
			echo "</ul>\n";
		}
		echo "</ul>\n";
	}
?>

I get this fault result:

<ul>
<li>SubCategory 1</li><ul>
</ul>
<li>SubCategory 2</li><ul>
<ul>
<li>SubCategory 2.1</li><ul>
</ul>
<li>SubCategory 2.2</li><ul>
</ul>
</ul>
</ul>
<li>SubCategory 3</li><ul>
</ul>
</ul>

When i use the second:

$sql = "SELECT id, title, parent_id FROM table WHERE language='en-GB' and level>=3";
$res = mysql_query($sql, GetMyConnection());

$minParent = 999;
while (list($id, $title, $parent_id) = $res->fetch_row()) {
    $data[$parent_id][] = array('id'=>$id, 'title'=>$title);
    $minParent = min($minParent,$parent_id);
}
    
// call the recursive function
displayHierarchy($data, $minParent);

I get an server error. :/

Edited May 20, 2013 by Isset1988

Barand · May 20, 2013

your query should return this from your data above

+----+-----------------+-----------+
| id | title           | parent_id |
+----+-----------------+-----------+
| 12 | SubCategory 1   |        10 |
| 13 | SubCategory 2   |        10 |
| 14 | SubCategory 2.1 |        13 |
| 15 | SubCategory 2.2 |        13 |
| 16 | SubCategory 3   |        10 |
+----+-----------------+-----------+

so the min parent_id should be 10 :confused:

Muddy_Funster · May 20, 2013

Quite coincidentaly I just finished making some functions to do something rather simmilar to this for my current project, and was just jumping on here to see if someone could refine it down enough to add it to the snipit/repo section of the forum. It's a bit clumsy compared to what Barand and the others can do, but it takes any number of parent->child associations and nests them appropriately. It's actualy designed to build a series of dynamicaly generated menus based on a given contaner div and the currently logged in users roll level (or group as I have chosen to call it). It's split over a few functions and I have commented it so that each step is prettey well broken down. I'm posting this on here for you and anyone else to use for free (that's free as in free beer). The comments should make the process fairly simple to follow and the code fairly easy to adapt as you need. Have a look, and good luck.

<?php

require_once 'db_con.php';

/*
  A series of functions to create a dynamicly generated, infinately recursive menu
  using a tree structure. This runs by getting data from table with a fairly
  standard layout of
  ______________________________________________________________________________
  |FieldName | DataType | Note                                                 |
  |lID       | int()    | LinkId, PrimaryKey, Unsigned, AutoInc                |
  |pID       | int()    | parentID, Unsigned, default of 0-(zero) for top level|
  |dest      | varchar()| destination that the links points to                 |
  |txt       | varchar()| Display Text for link                                |
  |title     | varchar()| text for the title attribute of the anchor tag       |
  |order     | int()    | display order, numbered from left to right**         |
  |lnkStat   | tinyint()| used to controll display state of link***            |
  |accessGrp | int()    | used to determin what links are available by group   |
  |contID    | varchar()| text value of div the links are to be shown in****   |
  ------------------------------------------------------------------------------
  ** order is a reserved word in MySQL, you may want to change this for your own
     tables
     
  *** I use a default value of 1, with 1 = active and 0 = inactive.  This can be
      expanded uppon using a CASE in the SQL to, for example, change the destination
      of the link when set to three to a stock "under maintenance", or when -1
      point the link to a default "under construction" page etc.
      
  **** This must include the # and . symbols for id and class names respectivly
*/

function getLinks($grp){
/*
  Creates a PDO conection to the database by calling my own custom builder
  function. Replace the first line with your own PDO connction details to connect
  and retrieave the list of links for the applicable account group sent in $grp.
  Package links into a muti-dim' array in format:
  containingDiv->parentID->linkID->{linkDisplayTxt, linkDestination, linkTitle, linkDisplayOrder}   
*/
  $con = pdoCon('nsite');
  $sql = "SELECT contID, pID, lID, dest, txt, title, `order` FROM t_links WHERE lnkStat = 1 AND accessGrp IN(SELECT agID FROM t_map_lnk_access WHERE gID = :grp ) ORDER BY contID, pID, `order`, lID ASC";
  $stmt = $con->prepare($sql);
  $stmt->bindParam(':grp', $grp, PDO::PARAM_INT);
  $stmt->execute();
  $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
  foreach($result as $row){
    $links[$row['contID']][$row['pID']][$row['lID']] = json_encode(
      array(
        "txt"=>$row['txt'],
        "dest"=>$row['dest'],
        "title"=>$row['title'],
        "ord"=>$row['order']
      )
    );  
  }
  return $links;
}

function buildLinks($links, $cont){
/*
  The function takes in the $links array produced by getLinks and the $cont
  string variable that relates to the containerDiv that you want to populate the
  links into, it then calls the nonRecurseLinks() function for the relevent div.
  This is called independantly of the getLinks() function to reduce needles
  database transactions.  As long as you do not have too much in the way of links
  you can actualy add the $links array into the $_SEESION and carry it about for
  use between pages without needing to call the database again (larger datasets
  can be stored in browser storage using javascript as well, but that's off topic)
*/       
  foreach($links as $container=>$val){  
    if($container == $cont){
      $pids=array_keys($val);
      $return = nonRecurseLinks(&$val, $pids);
      return $return;
    }
  }
}
function nonRecurseLinks($val, $pids){
/*
  Creates the top level menu items by calling prepLink() and builds the recursive
  menu items by calling recurseLink() whenever an item is found to have a parentID
  that is in the list.  This does potentialy infinate recursion although I have
  only tested with a four deep nest i.e top->child1->child2->child3 and found
  that the raleationships were intact.
  $vals is the array of links that relate to the containerDiv and $pids is the
  array of parentIDs that exist for the links in $val.  If there is no matching
  linkID for a given parentID then none of the links with that parentID will be #
  displayed.  This lets you cut off a branch and not worry about any sub-branches
  showing up where they don't belong.   
*/
  $return = "";
  foreach($val as $p=>$l){ ;
    foreach($l as $id=>$data){
      if(in_array($id, $pids)){
        $return .=  prepLink($data);
        unset($val[$p]);
        $return .=  recurseLink($id, &$val, $pids);
      }
      elseif($p == 0){
          $return.=prepLink($data)."</li>";
      }
    }
  }
  return $return;
}

function recurseLink($p, $val, $pids){
/*
  This recusively builds the child links from the top down. taking in the current
  parentID for the link in use, the full remaining array of links to display and
  the parentID array for all parentIDs.  Each link is checked to see if its
  parentID is in the $pids array and if it is another level is added to the tree.
  Once a link has been resolved to it's final position it is sent out through the
  prepLink function and removed from the $val array.  This prevents spurious
  instances of the links apearing under other parent headings or on the top level.
*/
  $return = "";
  foreach($val as $pr=>$l){
    if($p == $pr){
      $return .="<ul>\n\r";
      foreach($l as $id=>$data){
      if(in_array($id, $pids)){
        $return .= prepLink($data);
        unset($val[$pr]);  
        $return .= recurseLink($id, &$val, $pids);
      }
      elseif($p != 0){
          $return.=prepLink($data);
      }
      }
      $return .= "</ul></li>";
    }
  }
  return $return;
}

function prepLink($data){
/*
  directly applies the html unordered list and list item wrappers to each link
  element and returns the sting to the buildLinks() function.
*/
  $return = "";
  $lob = json_decode($data);
  $return .="<li><a href='{$lob->dest}' title='{$lob->title}'>{$lob->txt}</a>\n\r";
  return $return;
}
?>

Isset1988 · May 20, 2013

so the min parent_id should be 10

Thank you for all the help.

The results is perfect for the first option, but i dont get the right structure on ul li. :confused:

Quite coincidentaly I just finished making some functions to do something rather simmilar to this for my current project, and was just jumping on here to see if someone could refine it down enough to add it to the snipit/repo section of the forum. It's a bit clumsy compared to what Barand and the others can do, but it takes any number of parent->child associations and nests them appropriately. It's actualy designed to build a series of dynamicaly generated menus based on a given contaner div and the currently logged in users roll level (or group as I have chosen to call it). It's split over a few functions and I have commented it so that each step is prettey well broken down. I'm posting this on here for you and anyone else to use for free (that's free as in free beer). The comments should make the process fairly simple to follow and the code fairly easy to adapt as you need. Have a look, and good luck.

Thank you Muddy_Fuster. I will try to get something from this code ::)

Muddy_Funster · May 20, 2013

You're very welcome, let me know how you get on with it and If anything's confusing you then just give a shout.

Barand · May 20, 2013

You're right, it was overdoing the ul /ul tags

Revised

$minParent=999;
while (list($id, $title, $parent_id) = mysql_fetch_row($res)) {
    $data[$parent_id][] = array('id'=>$id, 'title'=>$title);
    $minParent = min($minParent,$parent_id);
}
   
// call the recursive function
displayHierarchy($data, $minParent);

//function to print a category then its child categories 
    function displayHierarchy(&$arr, $parent_id, $indent=0)
    {
        $ind = $indent * 50;
        if (isset($arr[$parent_id]))
        echo "<ul>\n";
        foreach($arr[$parent_id] as $rec)
        {
            echo "<li>{$rec['title']}</li>\n";
            
            if (isset($arr[$rec['id']])) {
                displayHierarchy($arr, $rec['id'], $indent+1);
            }
            
        }
        echo "</ul>\n";
    }

I don't usually use ul tags but handle the indents myself (hence the indent parameter)

    function displayHierarchy(&$arr, $parent_id, $indent=0)
    {
        $ind = $indent * 25;
        if (isset($arr[$parent_id]))
        foreach($arr[$parent_id] as $rec)
        {
            echo "<div style='margin-left:{$ind}px;'>• {$rec['title']}</div>\n";
            if (isset($arr[$rec['id']])) {
                displayHierarchy($arr, $rec['id'], $indent+1);
            }
            
        }
    }

Edited May 20, 2013 by Barand

Isset1988 · May 20, 2013

You're right, it was overdoing the ul /ul tags

Yes, now i get one good result.

<ul>
<li>SubCategory 1</li>
<li>SubCategory 2</li>
<ul>
<li>SubCategory 2.1</li>
<li>SubCategory 2.2</li>
</ul>
<li>SubCategory 3</li>
</ul>

I think that the structure is more right as the code below, but it is difficult to achieve this.

.

<ul>
<li>SubCategory 1</li>
<li>SubCategory 2
  <ul>
  <li>SubCategory 2.1</li>
  <li>SubCategory 2.2</li>
  </ul>
</li>
<li>SubCategory 3</li>
</ul>

In my case i will try to get this code:

<ul>
<li><a href="#">SubCategory 1</a></li>
<li><a href="#">SubCategory 2</a>
  <ul>
  <li><a href="#">SubCategory 2.1</a></li>
  <li><a href="#">SubCategory 2.2</a></li>
  </ul>
</li>
<li><a href="#">SubCategory 3</a></li>
</ul>

Thanks again for the help!

Muddy_Funster · May 21, 2013

that's what my code produces.... :tease-03:

Isset1988 · May 23, 2013

that's what my code produces....

Wow, Muddy_Funster i think your code is what i looking for.

Your code has the functions page i need to include in my code.

Can you post me here a sample of the front page which call these functions?

And if you can send me a sample database to adupt this to the structure of my database

Many Thanks!!!!

Muddy_Funster · May 23, 2013

sample table would be :

| lID | int(3) unsigned | NO |PRI | NULL | auto_incrament

| pID | int(3) unsigned | NO | | 0 |

| txt | varchar(255) | NO | | NULL |

| linkStat | tinyInt(1)unsigned | NO | | 1 |

| accessGrp| int(10) unsigned | NO | | 1 |

contID holds the name of the div that you will populate the list into, pID is the parent ID for the link - this is the link ID of another existing lnk or 0 for the top level, lID is the link Id number order is the display oreder - either left to right or top to bottom depending on your display method, accessGrp is for roll level access and would relate to another roll level deffinition table. linkStat is used to define if the link is active and should be displayed or not - see comments in the original code. dest is the URL that the list item will point to

once you have the table populated you would call the code like this (assuming the contID is #mainMenu and you are not using an accessGrp other than the default value of 1):

<?php
$linkList = getLinks(1);
$mainMenuLinks = buildLinks($linkList, '#mainMenu');
$showMenu = <<<SHOW_MENU
<div id="mainMenu">
$mainMenuLinks
</div>
SHOW_MENU;
echo $showMenu;
?>

Sign In

unordered list php-sql

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information