Query hierarchy in reverse

[vinny42]

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.

[Barand]

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

[John_A]

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 September 27, 2013 by John_A