Interesting Logic Puzzle
Posted: Thu Jul 01, 2010 2:05 am
So I think I figured this out, but without having all the data in place to test it, I was wondering if anyone could either find a more graceful way of doing it, or give some insight as to whether it would work.
Basically I have 5 levels of listings in my mysql database, with the bottom 4 each having a "parent" value corresponding to the one above it. Think of it as a hierarchy, or better, a tree. Each child can have one parent, but each parent can have infinite children.
I also have a table called user_listing_relations, which stores, among other things, which users are admins of which listings. The user_listing_relations table has a column for "level", which corresponds to one of the 5 levels of listings. I have the permissions set up so if a user is an admin of a certain listing, he/she is also the admin for all of that listing's children.
I want to have a function that returns all listing IDs of a certain level that a user is an admin of. It would take two arguments, user_id and listing_level, and basically would iterate through all listing levels to get the listings that the user is a direct admin of, and then reiterate again to get the children, grandchildren, or great-grandchildren of those listings that correspond to the level in question.
(Note that get_results returns an array of the SQL query results in stdClass format)
Here's what I have so far, any ideas/critique would be great:
Basically I have 5 levels of listings in my mysql database, with the bottom 4 each having a "parent" value corresponding to the one above it. Think of it as a hierarchy, or better, a tree. Each child can have one parent, but each parent can have infinite children.
I also have a table called user_listing_relations, which stores, among other things, which users are admins of which listings. The user_listing_relations table has a column for "level", which corresponds to one of the 5 levels of listings. I have the permissions set up so if a user is an admin of a certain listing, he/she is also the admin for all of that listing's children.
I want to have a function that returns all listing IDs of a certain level that a user is an admin of. It would take two arguments, user_id and listing_level, and basically would iterate through all listing levels to get the listings that the user is a direct admin of, and then reiterate again to get the children, grandchildren, or great-grandchildren of those listings that correspond to the level in question.
(Note that get_results returns an array of the SQL query results in stdClass format)
Here's what I have so far, any ideas/critique would be great:
Code: Select all
public static function get_admin_listings($user, $level) {
$ids = array();
$results = array();
$final = array();
for($i=1;$i<=$level;$i++) {
$query = $this->get_results("SELECT `ulr_listing` FROM `lm_user_listing_relations` WHERE `ulr_user`='$user' AND `ulr_level`='$i'");
foreach($query as $result) {
$ids[] = $result->ulr_listing;
}
for($child = $i + 1;$child <=$level;$child++) {
$results = $ids;
foreach($results as $result) {
unset($ids);
$ids = array();
$children = $this->get_results("SELECT `l".$child."_id` AS `id` FROM `lm_listing_level$child` WHERE `l".$child."_parent`='$result'");
foreach($children as $child_listing) {
$ids[] = $child_listing->id;
}
}
}
}
$final = array_unique($ids);
return $final;
}