Add new layer

Posted on 2014-08-18
Last Modified: 2014-09-12
How do I add another layer of nesting to a MySQL DB?

For example, right now we have
    > Android
    > iPhone
but need
    > Android
       > Crackberry
    > iPhone
      > 5c/5s

How do I do that?
Question by:Bob Stone
    LVL 58

    Expert Comment

    Your DB setup would help but something like this

    ID	Name		Parent
    1	Android		0
    2	Samsung		1
    3	Parts		2
    4	Crackberry	1

    Open in new window

    LVL 12

    Author Comment

    by:Bob Stone
    What's the SQL syntax and php code to query that and make it line up properly?
    LVL 58

    Accepted Solution

    This is what I use on one site

    The SQL
    SELECT id, name, parent FROM table ORDER BY parent, name

    Open in new window

    This is the code that sets up the arrays
    $refs = array();
    $list = array();

    Open in new window

    This is the code that loops the recordset
    while($data = $stmt->fetch(PDO::FETCH_ASSOC)) {
    	$this_ref = &$refs[ $data['id'] ];
    	$this_ref['parent'] = $data['parent'];
    	$this_ref['name'] = $data['name'];
    	if ($data['parent'] == 0) {
    		$list[ $data['id'] ] = &$this_ref;
    	} else {
    		$refs[ $data['parent'] ]['children'][ $data['id'] ] = &$this_ref;

    Open in new window

    This is the function that builds up a UL of the categories with nested children
    function toUL($arr,$level){
    	global $links;
    	if($level!=0){$html .= '<ul>';}
    	foreach ($arr as $v){
    		$html .= '<li>
    		<a href="'.$links.$v['url'].'">' . $v['name'].'</a>';
    		if (array_key_exists('children', $v)){
    			$html .= toUL($v['children'],1);
    		$html .= '</li>';
    	if($level!=0){$html .= '</ul>';}
    	return $html;

    Open in new window

    This is kinda plug 'n' play code, just needs to be adapted to your table column names. And of course you will add to add your styling

    I made an edit to my sample table set up above - this code relies on the top category parent value being 0 (zero)

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now