Add new layer

Posted on 2014-08-18
Medium Priority
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
  • 2
LVL 58

Expert Comment

ID: 40268399
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


Author Comment

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

Accepted Solution

Gary earned 2000 total points
ID: 40269131
This is what I use on one site

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month16 days, 14 hours left to enroll

862 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