Solved

Needing help building an array in a specific format

Posted on 2014-01-12
10
291 Views
Last Modified: 2014-01-17
I've been trying to create a multi-level ordered list in PHP for some time now. I've tried and have been been searching and everything I've seen has a different data layout than what I have to work with. Most examples pull the data from a database and build the array based on separate columns and, in come cases, columns with depth or parental relationships information. My data is in a path to an SSRS report and can vary from 2 to 3 (or more in the future) levels so I'm not sure how to build the array to work with the function in this question as the structure of the data I have is different form all the examples.

The full example I'm working is attached as array1.php

array1.php

I'm pulling data from an SSRS report server. The only relevant part of it is the path to the report I have to link.  The report name is always the last element in string.  There is an additional column called 'Name' but this is simply a duplicate of the last element in the 'Path' data, so it's somewhat redundant anyway.

Data from SSRS report table - column name = "Path"
/Production/Analyst/POS Sales Excel Data Export
/Production/Analyst/STATS Membership Tracking - RAW
/Production/Information Technologies/IT Dashboard DUMMY
/Production/Information Technologies/IT Department
/Production/Membership/MDash
/Production/Membership/Membership Dashboard
/Production/Membership/Membership Reports/Membership Grid
/Production/Membership/Membership Reports/Membership Grid by SU
/Production/Membership/Membership Reports/Membership Pie Charts
/Production/Membership/Membership Reports/Membership Sum Drill Down
/Production/Membership/Membership Reports/POS Sales By Date and Location
/Production/Membership/Stats/STATS Council Participation
/Production/Membership/Stats/STATS Council SU Summary Adults
/Production/Membership/Stats/STATS Council Summary Adults
/Production/Membership/Stats/STATS Council Troop Summary Adults
/Production/Membership/Stats/STATS Lifetime Stats
/Production/Membership/Stats/STATS Membership Tracking
/Production/Online Training (LMS)/LMS User ID Lookup
/Production/Online Training (LMS)/Manager Training 2014
/Production/Shop/POS Reports/POS Sales By Date and Location
/Production/Shop/Shop Dashboard DUMMY
/Production/SLT Dashboard/OLD Dashboard Membership_Overview

Open in new window

That data would need need to go in a format like that below to work with the function I have. The ID is irrelevant to my purposes since the link to the report is the same as the report name so I would have to change the function to suit. That said, I need to take the path above and turn it into an array like that below.

Array format required:
$nested = Array
(
1 => Array ('parent' => 0, 'title' => 'Analyst'),
2 => Array ('parent' => 0, 'title' => 'Membership'),
3 => Array ('parent' => 0, 'title' => 'SLT Dashboard'),
4 => Array ('parent' => '1', 'title' => 'POS Sales Excel Data Export'),
5 => Array ('parent' => '1', 'title' => 'STATS Membership Tracking - RAW'),
6 => Array ('parent' => '2', 'title' => 'Membership Dashboard'),
7 => Array ('parent' => '6', 'title' => 'Membership Grid'),
8 => Array ('parent' => '6', 'title' => 'Membership Grid by Service Unit'),
);

Open in new window

So, how do convert the data I'm working with into an array that will output a format like the snippet above?  I've been looking at a lot of sites trying to understand more about arrays - I'm starting to see the light but still need some help in the right direction.  Any help would be greatly appreciated.
0
Comment
Question by:saabStory
  • 6
  • 4
10 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39775027
I can't line up the data from the SSRS report table with the Array posted above.  I'm trying to get a better handle on what you want to achieve here.  In this snippet I've taken the rows apart a little bit to try to show parent/child directories (for want of a better term) in groups.  Everything is a child of Production and the children include Analyst, Membership, etc.  Membership may have child directories that include Membership Reports and Stats.

Does this start to make sense of what sort of data structure you're after?

/Production/Analyst/POS Sales Excel Data Export
/Production/Analyst/STATS Membership Tracking - RAW

/Production/Information Technologies/IT Dashboard DUMMY
/Production/Information Technologies/IT Department

/Production/Membership/MDash
/Production/Membership/Membership Dashboard

/Production/Membership/Membership Reports/Membership Grid
/Production/Membership/Membership Reports/Membership Grid by SU
/Production/Membership/Membership Reports/Membership Pie Charts
/Production/Membership/Membership Reports/Membership Sum Drill Down
/Production/Membership/Membership Reports/POS Sales By Date and Location

/Production/Membership/Stats/STATS Council Participation
/Production/Membership/Stats/STATS Council SU Summary Adults
/Production/Membership/Stats/STATS Council Summary Adults
/Production/Membership/Stats/STATS Council Troop Summary Adults
/Production/Membership/Stats/STATS Lifetime Stats
/Production/Membership/Stats/STATS Membership Tracking

/Production/Online Training (LMS)/LMS User ID Lookup
/Production/Online Training (LMS)/Manager Training 2014

/Production/Shop/POS Reports/POS Sales By Date and Location

/Production/Shop/Shop Dashboard DUMMY

/Production/SLT Dashboard/OLD Dashboard Membership_Overview

Open in new window

0
 

Author Comment

by:saabStory
ID: 39775797
Right - it looks like you have it right.  In fact, since production is common to all, it can probably be left off.  This will ultimately be used for a menu and it requires an unordered list to work properly.

If it helps, this is  a test from the function in the attachment using part of the data above.

<ul>
	<li><a href="#">Analyst</a> 
		<ul>
            <li><a href="#">POS Sales Excel Data Export</a></li>
			<li><a href="#">STATS Membership Tracking - RAW</a></li>
		</ul>
	</li>
	<li><a href="#">Membership</a>
    	<ul>
			<li><a href="#">Membership Dashboard</a>
            	<ul>
					<li><a href="#">Membership Race and Ethnicity Grid</a></li>
					<li><a href="#">Membership Race and Ethnicity Grid by SU</a></li>
				</ul>
			</li>
		</ul>
	</li>
	<li><a href="#">SLT Dashboard</a></li>
</ul>

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39777345
I've been looking at this a little more, and I still can't see how you're getting these results.  For example, the data from the SSRS table has Membership Dashboard, and there is nothing under that category.  Yet in the HTML example here we have Membership Race and Ethnicity Grid and Membership Race and Ethnicity Grid by SU.  This matters to my thinking because I am wondering if the ends of the tree are unique and the branches are unique.  If they're not, I will need to use a different code set to traverse the tree.

Do you have a complete test data set for this?
0
 

Author Comment

by:saabStory
ID: 39778424
The example above is pretty much like the path field in the database.  It's not my table, but our dba/report writer.  There's a lot of other stuff in the tables to track transactions (i.e. inserts, updates, etc) but this is the the schema of the table:

COL. NAME      DATA TYPE               ALLOW NULLS
==============================================
ItemID		uniqueidentifier	Unchecked
Path		nvarchar(425)		Unchecked
Name		nvarchar(425)		Unchecked
ParentID	uniqueidentifier	Checked
Type		int			Unchecked
[Content]	image			Checked
Intermediate	uniqueidentifier	Checked
SnapshotDataID	uniqueidentifier	Checked
LinkSourceID	uniqueidentifier	Checked
Property	ntext			Checked
Description	nvarchar(512)		Checked
Hidden		bit			Checked
CreatedByID	uniqueidentifier	Unchecked
CreationDate	datetime		Unchecked
ModifiedByID	uniqueidentifier	Unchecked
ModifiedDate	datetime		Unchecked
MimeType	nvarchar(260)		Checked
SnapshotLimit	int			Checked
Parameter	ntext			Checked
PolicyID	uniqueidentifier	Unchecked
PolicyRoot	bit			Unchecked
ExecutionFlag	int			Unchecked
ExecutionTime	datetime		Checked
SubType		nvarchar(128)		Checked
ComponentID	uniqueidentifier	Checked

Open in new window


For my purposes, the relevant data would be in the attachment.  The ItemID is the PK of the table and is referenced in the ParentID column.  Path is the full path to the report including the name of the report and Name is just the name of the report.  From the Type column, I'm was only using those rows with Type=2, as those are the only ones that actually show in the navigation.

I hope this helps - I really appreciate your efforts on this.
dashboard-TestData.xlsx
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39782555
Please confirm something for me...  Here is the code and the rendered tree (image).  Am I correct in understanding that Page 2-3-3 is out of place under Page 3-1, and instead should be under Page 2-3?

$nested = Array
(
 1 => Array ('id' =>  1, 'parent' =>  0, 'title' => 'Page 1'),
 2 => Array ('id' =>  2, 'parent' =>  0, 'title' => 'Page 2'),
 3 => Array ('id' =>  3, 'parent' =>  0, 'title' => 'Page 3'),
 4 => Array ('id' =>  4, 'parent' =>  0, 'title' => 'Page 4' ),
 5 => Array ('id' =>  5, 'parent' =>  0, 'title' => 'Page 5'),
 6 => Array ('id' =>  6, 'parent' =>  1, 'title' => 'Page 1-1'),
 7 => Array ('id' =>  7, 'parent' =>  1, 'title' => 'Page 1-2'),
 8 => Array ('id' =>  8, 'parent' =>  1, 'title' => 'Page 1-3'),
 9 => Array ('id' =>  9, 'parent' =>  2, 'title' => 'Page 2-1'),
10 => Array ('id' => 10, 'parent' =>  2, 'title' => 'Page 2-2'),
11 => Array ('id' => 11, 'parent' =>  2, 'title' => 'Page 2-3'),
12 => Array ('id' => 12, 'parent' =>  3, 'title' => 'Page 3-1'),
13 => Array ('id' => 13, 'parent' =>  3, 'title' => 'Page 3-2'),
14 => Array ('id' => 14, 'parent' =>  4, 'title' => 'Page 4-1'),
15 => Array ('id' => 15, 'parent' =>  6, 'title' => 'Page 1-1-1'),
16 => Array ('id' => 16, 'parent' =>  6, 'title' => 'Page 1-1-2'),
17 => Array ('id' => 17, 'parent' =>  6, 'title' => 'Page 1-1-3'),
18 => Array ('id' => 18, 'parent' =>  7, 'title' => 'Page 1-2-1'),
19 => Array ('id' => 19, 'parent' =>  7, 'title' => 'Page 1-2-2'),
20 => Array ('id' => 20, 'parent' =>  7, 'title' => 'Page 1-2-3'),
21 => Array ('id' => 21, 'parent' =>  9, 'title' => 'Page 2-1-1'),
22 => Array ('id' => 22, 'parent' =>  9, 'title' => 'Page 2-1-2'),
23 => Array ('id' => 23, 'parent' => 12, 'title' => 'Page 2-3-3')
)
;

Open in new window

Browser output from nav tree
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39784298
Please see http://www.laprbass.com/RAY_temp_saabstory.php

<?php // RAY_temp_saabstory.php
error_reporting(E_ALL);


// SEE http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28336520.html


// TEST DATA FROM THE POST AT EE, MODIFIED TO ADD SOME EDGE CASES
$dat = <<<EOD
/Production/Analyst/POS Sales Excel Data Export
/Production/Analyst/STATS Membership Tracking - RAW
/Production/Analyst/MDash

/Production/Information Technologies/IT Dashboard DUMMY
/Production/Information Technologies/IT Department

/Production/Membership/MDash
/Production/Membership/Membership Dashboard

/Production/Membership/Membership Reports/Membership Grid
/Production/Membership/Membership Reports/Membership Grid by SU
/Production/Membership/Membership Reports/Membership Pie Charts
/Production/Membership/Membership Reports/Membership Sum Drill Down
/Production/Membership/Membership Reports/POS Sales By Date and Location

/Production/Membership/Stats/STATS Council Participation
/Production/Membership/Stats/STATS Council SU Summary Adults
/Production/Membership/Stats/STATS Council Summary Adults
/Production/Membership/Stats/STATS Council Troop Summary Adults
/Production/Membership/Stats/STATS Lifetime Stats
/Production/Membership/Stats/STATS Membership Tracking

/Production/Membership/Stats/Foo/Extra_1
/Production/Membership/Stats/Foo/Extra_2

/Production/Online Training (LMS)/LMS User ID Lookup
/Production/Online Training (LMS)/Manager Training 2014

/Production/Shop/POS Reports/POS Sales By Date and Location

/Production/Shop/Shop Dashboard DUMMY

/Production/SLT Dashboard/OLD Dashboard Membership_Overview
EOD;


Class PathFinder
{
    public function __construct($lines, $dlm='/')
    {
        // MAKE AN ARRAY OF THE LINES
		$arr = explode(PHP_EOL, $lines);

		// REMOVE BLANK LINES AND LEADING SLASHES
		foreach ($arr as $key => $val)
		{
		    $val = trim($val);
		    if (empty($val))
		    {
		        unset($arr[$key]);
		    }
		    else
		    {
		        $arr[$key] = preg_replace("#^$dlm#", NULL, $val);
		    }
		}

		// MAKE ARRAYS FROM EACH OF THE LINES
		$max = 0;
		$cnt = 1;
		foreach ($arr as $key => $val)
		{
		    $sub = explode($dlm, $val);
		    if (count($sub) > $max) $max = count($sub);

		    // RENUMBER SUB-ARRAY STARTING FROM 1
		    $new = array();
		    foreach ($sub as $ptr => $txt)
		    {
		        $new[$ptr+1] = $txt;
		    }
		    $relations[$cnt] = $new;
		    $cnt++;
        }

        // COMBINE ALL OF THE ARRAYS TO NUMBER THE DATA ELEMENTS
        $big = array();
        $cnt = 0;
        $num = 1;
        while ($cnt <= $max)
        {
            foreach ($relations as $sub)
            {
                if (!empty($sub[$cnt])) $big[] = $sub[$cnt] . "___$cnt";
            }
            $cnt++;
        }

        // DE-DUPLICATE THE BIG ARRAY, KEEPING THE LEVEL NUMBERS
        $arr = array();
        $old = FALSE;
        foreach ($big as $str)
        {
            if ($old != $str)
            {
                $arr[] = $str;
                $old = $str;
            }
        }

        // BUILD THE ARRAY OF PATH ELEMENTS
        foreach ($arr as $key => $str)
        {
            $pel = explode('___', $str);
            $this->path_elements[] = new PathElement($key+1, $pel[0], $pel[1]);
        }

        // INCORPORATE THE PARENT RELATIONSHIPS
        foreach ($relations as $sub)
        {
            $top = current(array_slice($sub,0,1));
            $this->setZero($top);

            foreach ($sub as $parent_level => $parent_name)
            {
                $child_level = $parent_level+1;
                if (!array_key_exists($child_level, $sub)) break;

                $child_name = $sub[$child_level];
                $this->setParent($parent_name, $parent_level, $child_name, $child_level);
            }
        }
        // ACTIVATE THIS TO CHECK THE RELATIONSHIPS
        // print_r($this->path_elements);
    }


    // USE THIS TO SET THE ANCHOR PAGES (FIRST IN THE NAV LIST)
    public function setZero($child_name)
    {
        foreach ($this->path_elements as $obj)
        {
            if ($child_name == $obj->getName())
            {
                // FOR THE FIRST LEVEL ONLY
                $obj->setParent(0);
                break;
            }
        }
    }


    // USE THIS TO INJECT THE PARENT INTO THE PATH ELEMENT OBJECT (TOP-TO-BOTTOM, LEFT-TO-RIGHT)
    public function setParent($parent_name, $parent_level, $child_name, $child_level)
    {
        $parentKount = FALSE;

        // IDENTIFY THE PARENT BY NAME AND LEVEL
        foreach ($this->path_elements as $obj)
        {
            if ($parent_name == $obj->getName())
            {
                if ($parent_level == $obj->getLevel())
                {
                    $parentKount = $obj->getKount();
                    break;
                }
            }
        }
        if ($parentKount === FALSE) trigger_error("PARENT NOT FOUND $parent_name($parent_level) -> $child_name($child_level)", E_USER_ERROR);

        // IDENTIFY THE FIRST EMPTY CHILD BY NAME AND LEVEL
        foreach ($this->path_elements as $obj)
        {
            if ($child_name == $obj->getName())
            {
                if ($child_level == $obj->getLevel())
                {
                    if ($obj->getparent() === NULL)
                    {
                        $obj->setParent($parentKount);
                        break;
                    }
                }
            }
        }
    }


    // USE THIS TO GET THE NAVIGATION TREE ARRAY
    public function getNested()
    {
        $nested = array();
        foreach ($this->path_elements as $obj)
        {
            $arr = array
            ( 'id'     => $obj->kount
            , 'parent' => $obj->parent
            , 'title'  => $obj->name
            )
            ;
            $nested[$obj->kount] = $arr;
        }
        return $nested;
    }
}


Class PathElement
{
    public function __construct($kount, $name, $level)
    {
        $this->kount  = $kount;
        $this->name   = $name;
        $this->level  = $level;
        $this->parent = NULL;
    }
    public function getKount()
    {
        return $this->kount;
    }
    public function getName()
    {
        return $this->name;
    }
    public function getLevel()
    {
        return $this->level;
    }
    public function getParent()
    {
        return $this->parent;
    }
    public function setParent($x)
    {
        $this->parent = $x;
    }

}


// USE THE CLASS
$p = new PathFinder($dat);
$nested = $p->getNested();


function recursive($parent, $array)
{
    $has_children = false;
    foreach($array as $key => $value)
    {
        if ($value['parent'] == $parent)
        {
            if ($has_children === false && $parent)
            {
                $has_children = true;
                echo '<ul>' ."\n";
            }
        echo '<li>' . "\n";
        echo '<a href="/page.php?id=' . $value['id'] . '">' . $value['title'] . '</a>' . " \n";
        echo "\n";
        recursive($key, $array);
        echo "</li>\n";
        }
    }
    if ($has_children === true && $parent) echo "</ul>\n";
}
?>
<!DOCTYPE HTML>
<html>
    <head>
       <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
       <title>RAY_temp_saabstory.php</title>
    </head>
    <body>
        <ul><?php echo recursive(0, $nested); ?></ul>
    </body>
</html>

Open in new window

I have this feeling that the code should be simpler and perhaps a university student with some free time could build that.  But for now this makes sense to me. HTH, ~Ray
0
 

Author Comment

by:saabStory
ID: 39789019
As my kids like to say - HOLY FREAKING AWESOME!!  That looks absolutely perfect - and I can see that I have a lot to study on over the weekend.  I can't tell you how much we appreciate this - will make for a very happy director and vp!
0
 

Author Closing Comment

by:saabStory
ID: 39789026
Excellent is not nearly enough in this instance - given all that he is obviously doing throughout this board, to spend this much time is just way, way above and beyond.  I appreciate this more than I can say.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39789053
Thanks for the points and thanks for using EE.  I may have been overthinking the problem.  If I see a simpler solution, I'll post it back here.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39789164
No, now that I looked at it again, I think this is a good enough solution.  Best regards, ~Ray
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

708 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

15 Experts available now in Live!

Get 1:1 Help Now