current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have a database that stores accounts and subscriptions. An account can contain accounts and subscriptions and have more than three parent levels.

A row from the database query result looks something like this:

$array = (0 => array("level" => "2", "accountno" => "123124234", "accountname" => "Hansel", "parentaccountno" => "000213123", "subscription" => "5423213213", "username" => "Gretchen");

(Level 1 means that it is top level and it will have PARENTACCOUNTNO = null).

From this I am trying to create a multidimensional array which looks something like this:

accounts:
    000213123
        accounts:
            123124234
                name: Hansel
                subscriptions:
                    5423213213
                        username: Gretchen

This is the code I have so far, which works really well on the first two levels. When there are are three levels or more things become more complicated, since each account only knows about its own parent:

$hierarchy = array();

foreach ($decode as $row) {
    $accountno = $row["ACCOUNTNO"];
    $msisdn = $row["MSISDN"];
    if ($row["PARENTACCOUNTNO"] == null)
        $base_array_key = $hierarchy["accounts"];

    $base_array_key[$accountno] = array("name" => $row["ACCOUNTNAME"], "accounts" => array(), "subscriptions" => array());

    $hierarchy["accounts"][$accountno]["accounts"]["321323123213"] = "blaha";

    if ($row["MSISDN"] != null)
        $hierarchy["accounts"][$accountno]["subscriptions"][$msisdn] = array("enduser" => $row["ENDUSER"]);
}

The solution I have in mind right now is to pass the base key from each iteration to the next iteration, and then if for example the previous level was two levels above this one just use some sort of string replace to remove the last two parts of the key.

Any better ideas? I am quite sure this is a shitty way of doing what I am trying to accomplish.

share|improve this question
    
Is this a complete my code question? or you got error somewhere? –  Starx Jun 17 '12 at 11:56
    
No error, just looking for some friendly advice on something I have been stuck on for about a week trying to find the optimal solution. –  Samuel Lindblom Jun 17 '12 at 15:23

3 Answers 3

up vote 0 down vote accepted

I appreciate the answers, but I think the problem is my database query which should contain records of all the parents of an account and not just the parent, using something like SYS_CONNECT_BY_PATH in Oracle SQL.

share|improve this answer
up vote 0 down vote

Create a temporary associative array:

$all_records = array();

Populate it with the results you find in the database:

while($result = get_results_from_db()) {
    $accountno = $result[accountno];
    $all_records[$accountno] = $result;
}

Now you have an array indexed by account numbers so it's easy to find any record if you know it's account number. So you iterate through the array to associate children to parents:

$top_level_records = array();
foreach($all_records AS $record) {
    $parentaccountno = $record[parentaccountno];
    if(!empty($parentaccountno)) {
        $parent = &$all_records[$record[parentaccountno]];
        if(empty($parent['accounts']))
            $parent['accounts'] = array() 
        $parent['accounts'][] = $record;
    }
    else {
        $top_level_records[] = $record;
    }
}
share|improve this answer
up vote 0 down vote

Do all your results look like that ? So then, where's are the parent accounts ? Or am i missing something ? I would simple rewrite the query to get a more "linear" result, like:

parentaccountno | accountno | accountname | bla
-----------------------------------------------
000213123       | 123124234 | Hansel      | abc

When every result row looks like this, you can simple create your final array like that:

foreach ($decode as $row) {
    $my_array = [ $row["parentaccountno"] ][ $row["accountno"] ]["accountname"] = $row["accountname"];      
    $my_array = [ $row["parentaccountno"] ][ $row["accountno"] ]["bla"] = $row["bla"];     
}
share|improve this answer
    
Each row is an account and a subscription, with an optional reference to a parent account. That means that any row could be a parent account, or a parent to a parent account, or a parent to a parent to a parent account, etc. –  Samuel Lindblom Jun 17 '12 at 15:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.