Jump to content

unordered list php-sql


Isset1988

Recommended Posts

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?

Link to comment
Share on other sites

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

post-3105-0-14753700-1368789276_thumb.png

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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'
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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. :/

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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;
}
?>

Link to comment
Share on other sites

so the min parent_id should be 10  :confused:

 

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  ::)

Link to comment
Share on other sites

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);
            }
            
        }
    }
Link to comment
Share on other sites

 

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! :)

Link to comment
Share on other sites

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

 

 

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 :D

 

Many Thanks!!!!

Link to comment
Share on other sites

sample table would be :

 

|FIELD     |TYPE                |NULL |KEY |DEFAULT |EXTRA

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

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

| dest     | varchar(255)       | NO  |    | NULL   |

| dest     | varchar(255)       | NO  |    | NULL   |

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

| title    | varchar(255)       | YES |    | NULL   |

| order    | tinyInt(2)unsigned | NO  |    | NULL   |

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

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

| contID   | varchar(255)       | NO  |    | NULL   |

 

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;
?>
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.