How to create a three dimensional array from sql query PHP

Question

I have 2 tables in a database, 1 of them are linked with a foreign key to the first one. Each row on table 1 is linked to multiple rows in table 2. I am trying to make a query that looks at a WHERE from table 2 and returns multiple rows from table 2 which are sorted into the rows they linked with in table 1 and then put this all into one big multi dimensional array, so it should work something like this:

$array[0][column_name][0] this would use row 1 from table 1 and give me a the first result in the column called column_name $array[1][column_name][0] this would use row 2 from table 1 and give me a the first result in the column called column_name $array[1][column_name][3] this would use row 2 from table 1 and give me a the 4th result in the column called column_name etc

How can I query this and store it in a 3 dimensional array using PHP.

I have tried to word this in as clear manner as possible, if you are unsure what I am asking, please comment and I will update my question to make it clearer.

Answer 1

Assume that we have two tables, Company and Employee:

  Company
------------------
ID   Company_Name
 1    Walmart
 2    Amazon.com
 3    Apple

       Employee
---------------------------------       
ID Company_Id Employee_Name
 1         1   Sam Walton
 2         1   Rob Walton
 3         1   Jim Walton
 4         1   Alice Walton
 5         2   Jeff Bezos
 6         2   Brian T. Olsavsky
 7         3   Steve Jobs
 8         3   Tim Cook 

The easiest way to envision a multi-dimensional (nested) array is to mimic the looping required to get it: outer loop is the company, inner loop is the employees:

// ignoring database access, this is just pseudo code

$outer = [];
// select id, company_name from company
foreach $companyResult as $companyRow {

    // select * from employee where company_id = ?  {$companyRow['id']}
    $inner= [];
    foreach $employee_result as $employeeRow {
        $inner[] = $employeeRow;  // ie, ['id'=>'1','Company_Id'=>'1','Employee_Name'=>'Sam Walton']
    }

    $outer[] = $inner;
}

print_r($outer);

// yields ====>

Array
(
    [0] => Array
        (
            [0] => Array
                (
                    [id] => 1
                    [Company_Id] => 1
                    [Employee_Name] => Sam Walton
                )

            [1] => Array
                (
                    [id] => 2
                    [Company_Id] => 1
                    [Employee_Name] => Rob Walton
                )

            [2] => Array
                (
                    [id] => 3
                    [Company_Id] => 1
                    [Employee_Name] => Jim Walton
                )

            [3] => Array
                (
                    [id] => 4
                    [Company_Id] => 1
                    [Employee_Name] => Alice Walton
                )
        )

    [1] => Array
        (
            [0] => Array
                (
                    [id] => 5
                    [Company_Id] => 2
                    [Employee_Name] => Jeff Bezos
                )

            [1] => Array
                (
                    [id] => 6
                    [Company_Id] => 2
                    [Employee_Name] => Brian T. Olsavsky
                )

        )

    [2] => Array
        (
            [0] => Array
                (
                    [id] => 7
                    [Company_Id] => 3
                    [Employee_Name] => Steve Jobs
                )

            [1] => Array
                (
                    [id] => 8
                    [Company_Id] => 3
                    [Employee_Name] => Tim Cook
                )

        )

)

---

It is also possible to do if you use associative arrays. Consider the flat file that this query produces:

select company.id company_id, company.name company_name, 
       emp.id employee_id, emp.employee_name
from company 
inner join employee on company.id = employee.company_id

-----

    company_id company_name employee_id employee_name
            1       Walmart           1        Sam Walton
            1       Walmart           2        Rob Walton
            1       Walmart           3        Jim Walton
            1       Walmart           4      Alice Walton
            2    Amazon.com           5        Jeff Bezos
            2    Amazon.com           6 Brian T. Olsavsky
            3         Apple           7        Steve Jobs
            3         Apple           8          Tim Cook

Just use the primary IDs as the keys for your arrays:

$employeeList = [];
foreach($result as $row) {

  $cid = $row['company_name'];
  $eid = $row['employee_name'];
  // avoid uninitialized variable
  // $employeeList[$row['company_name']] = $employeeList[$row['company_name']] ?? [];

  // easier to read version of above
  $employeeList[$cid] = $employeeList[$cid] ?? [];

  // assign it...
  $employeeList[$cid][$eid] = $row;
}

Or, if you simply want each company row to hold an array of employee names,

  $employeeList[$cid][] = $row['employee_name'];

The way that I've shown you is useful if you know the company_id and want to find the associated rows:

foreach($employeeList[2] as $amazon_guys) { ... }

But it's not at all useful if you're trying to group by employee, or some other field in the employee table. You'd have to organize the order of your indexes by your desired search order.

In the end, it's almost always better to simply do another query and let the database give you the specific results you want.