Jump to content

Query hierarchy in reverse


Go to solution Solved by Barand,

Recommended Posts

One the one hand, the code can't look familiar because you are not using the method I'm using.

 

On the otherhand, nothing will drop into #21 because your real problem isn't about building arrays.

What you need to do, at the most basic level, is recursively climb the tree from the source node untill you run into node 144  or until there are no mode parent nodes.

There are no arrays involved at all, forget arrays, they are not relevant here.

Just write a function that can fetch a record, check if it has a parent. if no parent stop. if it has a parent then  check if that parent is the node you need. if yes; stop, be happy. If not fetch the parentnode, check if it's parent is the 144 node, etc etc..

 

That's what my code does, in a class structure, but that's realy how you should be programming anyway, if you want to keep your code easy to maintain.

  • Solution

I had something like this in mind

$db = new mysqli(HOST, USERNAME, PASSWORD, DATABASE);

$fullSectionsArray = array(1,2,3,4,5,6,7,8,9,10);

$parentId = 144;
$descendants = array();
findDescendants($parentId, $descendants, $db);

$descendedFrom144 = array_intersect($descendants, $fullSectionsArray);
$notDescended = array_diff($fullSectionsArray, $descendants);

function findDescendants($parentId, &$descendants, $db)
{
    $res = $db->query("SELECT category_id FROM category WHERE parent = $parentId");
    while ($row = $res->fetch_assoc()) {
        $descendants[] = $row['category_id'];
        findDescendants($row['category_id'], $descendants, $db);
    }
}


Thanks very much for everyone's input. I think I've got what I need with this: -
 

<?php
$targetsection=144;

$fullSectionsArray = array(1,1505,1507,1509,1510,1511);
$editedSectionsArray = array();
$invertedSectionsArray = array();
$pathsArray = array();

foreach ($fullSectionsArray as &$theSection) {
	$thispath = get_my_path($theSection);
	$pathsArray[$theSection] = $thispath;
}

foreach( $pathsArray as $mKey => &$mVal ) {
	if( in_array($targetsection, $mVal ) ) {
		$editedSectionsArray[] = $mKey;
	} else {
		$invertedSectionsArray[] = $mKey;
	}
}

// $node is the name of the node we want the path of 
function get_my_path($node) {
	global $tableSections;

    // look up the parent of this node
	$query = "SELECT parent FROM $tableSections WHERE sectionID = " . $node;
	$result = mysql_query($query);
	$record = mysql_fetch_assoc($result);

    // save the path in this array 
    $myPath = array(); 

    // only continue if this $node isn't the root node (that's the node with no parent)
    if ($record['parent'] != '' && $record['parent'] != '0' && $record['parent'] != '1') { 
        // the last part of the path to $node, is the name 
        // of the parent of $node 
        $myPath[] = $record['parent'];
 
        // we should add the path to the parent of this node to the path 
        $myPath = array_merge(get_my_path($record['parent']), $myPath);
    } 
 
    // return the result
	return $myPath;
}
?>

vinny42 - while I don't doubt your code works and is perhaps the better approach, I start with an array in, and need two arrays out the other end (the template system of the 3rd party application in use expects arrays) and, although I said earlier I don't care about the intermediate path nodes, at some point I might like to list the full paths of all filtered descendants rather than just the immediate parent sections and so the get_my_path function is handy for this.
 
Barand - yours looks a lot more like what I was expecting. I haven't tested it but don't see why it wouldn't work. I may well end up switching to this if I decide I really don't care about full paths!
 
Again, thanks to all for their input :)

Edited by John_A
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.