Another Parent Child Conundrum!
11 posts by 2 authors in: Forums > CMS Builder
Last Post: June 6, 2012 (RSS)
By Perchpole - June 5, 2012 - edited: June 5, 2012
The code is for a thumbnail gallery. It is designed to show all of thumbs assigned to the category being viewed and any of its sub-categories.
<?php
$galleryBranchNums = $selectedGalleryNum;
foreach ($galleryCatRecords as $galCats) {
if ($galCats['parentNum'] == $selectedGalleryNum) {
$galleryBranchNums .= ',' . (int) $galCats['num'];
}
}
// load gallery category
list($galleryTypeRecords, $galleryTypeMetaData) = getRecords(array(
'tableName' => 'record',
'where' => " galCategory IN ($galleryBranchNums) ",
'orderBy' => "date DESC",
'perPage' => '12',
?>As I say, it worked fine - until I added another tier to the category tree.
It's easy to test if a category is a child of the selected category - using parentNum - but how would I test to see if it is a grandchild?
NB: I have considered using a mysql_select function - but this would return too much information. I just need to check the record numbers.
:o/
Perch
Re: [Perchpole] Another Parent Child Conundrum!
By Dave - June 5, 2012
Category sections have a field called 'lineage' which is a colon padded and separated list of parents. So if you have 15 which was a child of 3 which was a child of 1 you'd have a lineage of :1:3:15:
So if you wanted to get all the records that were descendants of a certain category (including that category itself) you could do something like this:
$parentNum = 123; // use whatever variable your parent num is in.
$where = " lineage LIKE '%:" .mysql_escape($parentNum). ":%' ";
Hope that helps!
interactivetools.com
Re: [Dave] Another Parent Child Conundrum!
By Perchpole - June 5, 2012
Thanks for this - but how best to use it?
Im trying to filter a getRecords() call - so I cannot use lineage.
Perch
Re: [Perchpole] Another Parent Child Conundrum!
By Dave - June 5, 2012
If you already have $selectedGalleryNum what about getRecords() with something like this:
'where' => " lineage LIKE '%:$selectedGalleryNum:%' ",
interactivetools.com
Re: [Dave] Another Parent Child Conundrum!
By Perchpole - June 5, 2012
That's a getCategories() call?
:0o
Perch
Re: [Perchpole] Another Parent Child Conundrum!
By Perchpole - June 5, 2012
For my issue I want to return all of the children and sub-children starting from the selected gallery Category - not just those in the specific branch.
Lineage would also return the parent category.
:0/
Perch
Re: [Perchpole] Another Parent Child Conundrum!
By Dave - June 5, 2012
interactivetools.com
Re: [Perchpole] Another Parent Child Conundrum!
By Perchpole - June 5, 2012
I've attached a screen shot of the tree to help visualise the issue.
If a visitor is browsing the "Events" section, they should see all files loaded into that section and all of the sub-sections.
If a visitor is browsing "Jubilee 2012" they should see all files loaded into that section and all of the sub-sections - but not the files uploaded into "Events".
Finally, if a visitor is in the Tea Party section, they should see just the files uploaded into that section and no more.
Make sense?
:0/
Perch
Re: [Perchpole] Another Parent Child Conundrum!
By Dave - June 6, 2012
I'm assuming you have one table for categories, and another for records in those categories, is that right?
Here's some code that gets you a list of all the category nums from the selectedCategory and it's branch (descendants), then generates that list of numbers in a format you can use to load records from another table that are assigned to any categories in the branch.
<?php
$selectedNum = intval($selectedCategory['num']);
$recordsOnBranch = mysql_select('category', "lineage LIKE '%:$selectedNum:%'");
$branchNums = array_pluck($recordsOnBranch, 'num');
$branchNumsAsCSV = mysql_getValuesAsCSV($branchNums);
print "Selected category num: " .$selectedCategory['num']. "<br/>\n";
print "All nums in branch: $branchNumsAsCSV<br/>\n";
print "Sample MySQL where: IN ($branchNumsAsCSV)";
?>Can you use that in a where to do a getRecords() call to load all the articles.
Will that work?
interactivetools.com
Re: [Dave] Another Parent Child Conundrum!
By Perchpole - June 6, 2012
It works. Thanks.
I added an extra bit of code to turn the array - generated by your code - into a string which I could then use as a where argument.
<?php
$selectedNum = intval($selectedGalleryNum);
$recordsOnBranch = mysql_select('gallery_category', "lineage LIKE '%:$selectedNum:%'");
$branchNums = array_pluck($recordsOnBranch, 'num');
$branchNums = implode(",", $branchNums);
list($galleryTypeRecords, $galleryTypeMetaData) = getRecords(array(
'tableName' => 'record',
'where' => " galCategory IN ($branchNums) ",
'orderBy' => "dragSortOrder, date DESC",
'perPage' => '12',
));
?>I'm sure there's probably a shorter/quicker way of doing this - but it works and I can see "how" it works.
Thanks again,
:0)
Perch