PHP Create a Multidimensional Array from an Array with Relational Data

PHP Create a Multidimensional Array from an array with relational data

<?php
header('Content-Type: application/json; charset="utf-8"');

/**
 * Helper function
 * 
 * @param array   $d   flat data, implementing a id/parent id (adjacency list) structure
 * @param mixed   $r   root id, node to return
 * @param string  $pk  parent id index
 * @param string  $k   id index
 * @param string  $c   children index
 * @return array
 */
function makeRecursive($d, $r = 0, $pk = 'parent', $k = 'id', $c = 'children') {
  $m = array();
  foreach ($d as $e) {
    isset($m[$e[$pk]]) ?: $m[$e[$pk]] = array();
    isset($m[$e[$k]]) ?: $m[$e[$k]] = array();
    $m[$e[$pk]][] = array_merge($e, array($c => &$m[$e[$k]]));
  }

  return $m[$r][0]; // remove [0] if there could be more than one root nodes
}

echo json_encode(makeRecursive(array(
  array('id' => 5273, 'parent' => 0,    'name' => 'John Doe'),  
  array('id' => 6032, 'parent' => 5273, 'name' => 'Sally Smith'),
  array('id' => 6034, 'parent' => 6032, 'name' => 'Mike Jones'),
  array('id' => 6035, 'parent' => 6034, 'name' => 'Jason Williams'),
  array('id' => 6036, 'parent' => 5273, 'name' => 'Sara Johnson'),
  array('id' => 6037, 'parent' => 5273, 'name' => 'Dave Wilson'),
  array('id' => 6038, 'parent' => 6037, 'name' => 'Amy Martin'),
)));

demo: https://3v4l.org/s2PNC

How to create multi-dimensional array from a list?

Some very simple recursion to build a tree structure:

function buildTree(array $data, $parent = null) {
    $branch = array();

    foreach ($data as $row) {
        if ($row['parent_id'] == $parent) {
            $row['children'] = buildTree($data, $row['id']);
            $branch[] = $row;
        }
    }

    return $branch;
}

$tree = buildTree($rowsFromDatabase);

Having an explicit 'children' key is usually preferable to the structure you're proposing, but feel free to modify as needed.

Converting an array from one to multi-dimensional based on parent ID values

The following code-example converts the array $array into the tree-structure you're looking for:

// key the array by id
$keyed = array();
foreach($array as &$value)
{
    $keyed[$value['id']] = &$value;
}
unset($value);
$array = $keyed;
unset($keyed);

// tree it
$tree = array();
foreach($array as &$value)
{
    if ($parent = $value['parent_id'])
        $array[$parent]['children'][] = &$value;
    else
        $tree[] = &$value;
}
unset($value);
$array = $tree;
unset($tree);

var_dump($array); # your result

This does not work, if there is an existing parent id that is 0. But could be easily changed to reflect that.

This is a related question, that has the original array already keyed, so the first half of the solution could be spared: Nested array. Third level is disappearing.

Edit:

So how does this work? This is making use of PHP variable aliasing (also known as references) and (temporary) arrays that are used to store a) aliases to the nodes ($keyed) and b) to build the new tree order ($tree).

Could you [...] explain the purpose of $array = $keyed, $array = $tree and the unsets?

As both, $keyed and $tree contain references to values in $array, I first copy over that information into $array, e.g.:

$array = $keyed;

As now $keyed is still set (and contains references to the same values as in $array), $keyed is unset:

unset($keyed);

This un-sets all references in $keyed and ensures, that all values in $array aren't referenced any longer (the value's refcount is reduced by one).

If the temporary arrays are not unset after the iteration, their references would still exist. If you use var_dump on $array, you would see that all values would have a & in front, because they are still referenced. unset($keyed) removes these references, var_dump($array) again, and you will see the &s are gone.

I hope this was understandable, references can be hard to follow sometimes if you're not fluent with them. It often helps me to think about them as variable aliases.

If you want some exercise, consider the following:

How to convert your $array from flat to tree with one foreach iteration?

Decide on your own when you would like to click the link which contains a Solution.

PHP generate a multidimensional array from mysql two tables

First, JOIN matieres to sous_matieres in your query.

$stmt = $pdo->query('SELECT 
                        m.id AS m_id, m.url AS m_url, m.title AS m_title, 
                        s.id AS s_id, s.url AS s_url, s.title AS s_title
                     FROM matieres m
                     INNER JOIN sous_matieres s ON m.url = s.parent');

Note that using INNER JOIN means that you will not get matieres without associated sous_matieres or vice versa. If there are any of those you want to see, you will need to use an outer join instead.

Then, as you fetch rows from the query result, append materials to their related parent keys like this:

while ($row = $stmt->fetchObject()) {
    $matieres[$row->m_id]['url'] = $row->m_url;
    $matieres[$row->m_id]['title'] = $row->m_title;
    $matieres[$row->m_id]['sous_matieres'][$row->s_id] = $row;
}

You should be able to loop over the resulting array to generate the output you want like this (not exactly the HTML you want, but should be enough to demonstrate the idea):

foreach ($matieres as $m_id => $matiere) {
    echo "<h2>$matiere[title]</h2>";
    foreach ($matiere['sous_matieres'] as $id => $sm) {
        echo "<div>
                  <a href='{$sm->s_url}'>{$sm->s_title}</a>
              </div>";
    }
}

SQL Joined data into multidimensional array

You can do something more generic

$records = array(
    array(
        'user.name' => 'Joshua',
        ....
    ),
);

$result = [
    'users' => []
];

foreach ( $records as $r ) {
    $new_record = [];
    foreach ( $r as $k => $v ) {
        $parsed = explode('.', $k);
        $cur    = &$new_record;
        for ( $i = 1, $n = count($parsed); $i < $n - 1; $i ++ ) {
            if ( ! isset($cur[ $parsed[ $i ] ]) ) {
                $cur[ $parsed[ $i ] ] = [ ];
            }
            $cur = &$cur[ $parsed[ $i ] ];
        }
        $cur[ $parsed[ $i ]] = $v;
    }
    $result['users'][] = $new_record;
}

print_r($result);

Here we are creating a new $new_record for each user record, and using a reference for the current nested level.

NOTE: In this case btw I put all the users in the same array. You can have the users index inside each record with a little modification:

$result = [];

foreach ( $records as $r ) {
    $new_record = [];
    foreach ( $r as $k => $v ) {
        $parsed = explode('.', $k);
        $cur    = &$new_record;
        for ( $i = 0, $n = count($parsed); $i < $n - 1; $i ++ ) {
            if ( ! isset($cur[ $parsed[ $i ] ]) ) {
                $cur[ $parsed[ $i ] ] = [ ];
            }
            $cur = &$cur[ $parsed[ $i ] ];
        }
        $cur[ $parsed[ $i ]] = $v;
    }
    $result[] = $new_record;
}

This will give you the exact structure that you've requested

How do I output a multidimensional array from multiple relational MySQL tables?

You can use a subselect in an in clause and something like this:

SELECT `event`.*, `user`.* from `user`,`user_event`, `event` 
WHERE `event`.`id` in (
   SELECT `user_event`.`event_id` from `user_event`, `user` 
   WHERE `user`.`id` = ?
)
AND `user`.`id` = `user_event`.`user_id`
AND `user_event`.`event_id` = `event`.`id`
ORDER BY `event`.`id`

Which basically reads: find all users going to the following events - the list of events can be found by taking the event_ids of the events that joe is going to.

In the subselect I don't pull in the event table because I get enough information (the event_id) from my many to many table.

There is going to be redundant data in the rows. You're going to want to post process the resulting array by iterating over the rows.

$groupedArray = array();
foreach ($result as $item)
{
   if(!isset($groupedArray[$item['event_id']])
   {
       // Create an empty array if we don't have one for this grouping yet.
       $groupedArray[$item['event_id']] = array();
   }

   // Append the current item to the subarray.    
   $groupedArray[$item['event_id']][] = $item;

}

This kind of loop is actually pretty idiomatic. You might want to write/find a general "regroup" function.

How to create two dimensional array to multi-level order list?

Here is the answer, created nested arrays from multi-dimensional array with ref

function makeRecursive($d, $r = 0, $pk = 'parent', $k = 'id', $c = 'children')
{
    $m = [];
    foreach ($d as $e) {
        isset($m[$e[$pk]]) ?: $m[$e[$pk]] = [];
        isset($m[$e[$k]]) ?: $m[$e[$k]]   = [];
        $m[$e[$pk]][]                     = array_merge($e, [$c => &$m[$e[$k]]]);
    }
    return $m[$r]; // remove [0] if there could be more than one root nodes
}
function nested2ul($data)
{
    $result = [];
    if (sizeof($data) > 0) {
        $result[] = '<ul>';
        foreach ($data as $entry) {
            $result[] = sprintf(
                '<li>%s %s</li>',
                $entry['name'],
                nested2ul($entry['children'])
            );
        }
        $result[] = '</ul>';
    }
    return implode($result);
}
$temp= makeRecursive($two_dimention);
echo nested2ul($temp);

Demo

Output

<ul>

  <li>Home </li>

  <li>Menu 1 </li>

  <li>Menu 2

    <ul>

      <li>Menu 2.1

        <ul>

          <li>Menu 2.1.1 </li>

          <li>Menu 2.1.2 </li>

        </ul>

      </li>

      <li>Menu 2.2 </li>

    </ul>

  </li>

  <li>Menu 3

    <ul>

      <li>Menu 3.1 </li>

    </ul>

  </li>

</ul>

---

Related Topics