PHP - 通过引用将子节点添加到多级关联数组

Question

I've got a record set from MySQL which returns the categories IDs and their values for the given objects. For example:

+-----------+-------------+----------------+
| object_id | category_id | category_value |
+-----------+-------------+----------------+
|         1 |           2 | VEGETARIAN     |
|         1 |           3 | MATARÓ         |
|         1 |           5 | MARCO POLO     |
|         1 |           5 | JOHN DOE       |
|         2 |           2 | VEGETARIAN     |
|         2 |           3 | MATARÓ         |
|         2 |           5 | JOHN DOE       |
+-----------+-------------+----------------+

Having this, what I'd need would be the following resulting associative array in PHP:

// They are arrays, so more than one object_id can be in the same category tree (and the object_id can be repeated across categories)

$final_array['VEGETARIAN']['MATARÓ']['MARCO POLO'] = array(1);
$final_array['VEGETARIAN']['MATARÓ']['JOHN DOE'] = array(1, 2);

The query orders the results first by object_id and, then, by category_id with a user set order. So, it would be like ORDER BY object_id ASC, FIELD(category_id, usercat1, usercat2, usercat3) (which in this case would be FIELD(category_id, 2, 3, 5).

I've got the loop which goes by every set, and I'm trying to keep a temporary variable to store the array levels, because they can change (not always will be 3 levels), so something like:

$temporary_array['VEGETARIAN'] = array();
$temporary_array = $temporary_array['VEGETARIAN'];

// And then, on the next iteration
$temporary_array['MATARÓ'] = array();
$temporary_array = $temporary_array['MATARÓ'];
// Now, $temporary_array would be equal to $temporary_array['VEGETARIAN']['MATARÓ'];

So I can create a main key based on the new category value which will be stored as a child of the parent array, and get all the hierarchical tree. Each level should include all the next ones.

I've tried to declare it globally and pass it by reference but it seems not to work.

Is there any way to achieve this in PHP? How would you make to get the desired result?

EDIT:

I've been more close to get it reversing the order of the categories, so I get first the last category (the one that has no other categories inside it) and I go upwards the array, something like this:

// Long story very short, would be like
$final_array['new_category_value'] = $old_final_array; 

And keep going up for every object. When I change the object_id, then I make:

array_merge_recursive($final_array, $actual_object_array);

However, I'm still not able to get the desired result.

Thank you for your time and comments!

Answer 1

Assuming you can access your SQL data as an associative array, and that the '1' value is the object_id (instead of say true/false 0/1 etc.), something like this:

$sth = $dbh->prepare("SELECT ......"); //Your SQL Here
$sth->execute();
$rows = $sth->fetchAll(PDO::FETCH_ASSOC);

$rows should then contain something like this:

$rows = array(
    array(
        "object_id" => 1,
        "category_id" => 2,
        "category_value" => "VEGETARIAN"
    ),
    array(
        "object_id" => 1,
        "category_id" => 3,
        "category_value" => "MATARÓ"
    ),
    array(
        "object_id" => 1,
        "category_id" => 5,
        "category_value" => "MARCO POLO"
    ),
    array(
        "object_id" => 1,
        "category_id" => 5,
        "category_value" => "JOHN DOE"
    )
); 

From this point, you can loop over the rows. What happens to an individual row is dependent on the row before it, so store and update a variable containing that so that it can be accessed in this loop

//Define your final array
$final_array = array();

//Store a reference to the last row in order to get the last category checked
$lastRow=null;

//Store reference to the last potential parent 
$lastParent=&$final_array;

//Store a reference to the last entry - either an array or the object id
$lastEntry = null;


//Loop over all returned rows
foreach($rows as $row) {

    //If first time around loop, $lastRow will be null
    //If $lastRow is not null, but matches this row's category_id, we should add to the same parent 
    if($lastRow===null || $lastRow['category_id']===$row['category_id']){

        $lastParent[$row['category_value']] = $row['object_id'];

        $lastEntry = &$lastParent[$row['category_value']];

        //Note - Do not change last parent here
    }

    //lastCategoryId does not match this row's category ID and is not null, we must change the 
    //  last entry to be an array and add this entry to it.
    else {
        $lastParent = &$lastEntry;

        $lastParent = array($row['category_value'] => $row['object_id']);

        $lastEntry = &$lastParent[$row['category_value']];
    }


    //Loop complete, update the last row
    $lastRow=$row;
}

You can now check call print_r($final_array); which returns :

Array
(
    [VEGETARIAN] => Array
        (
            [MATARÓ] => Array
                (
                    [MARCO POLO] => 1
                    [JOHN DOE] => 1
                )

        )

)