Build multidimentional array from select

[Adamhumbug]

Hi All,

Arrays kill me - struggle to understand how to build them.

Could anyone please help explain or point me to some good clear reading on how to build a multidemntional array with a select statement.

here is the statement for reference

SELECT client.company_name, job.name as jobName, version, currency, job.internal_ref, kit_delivery, kit_return, quote_status_id 
        from quote
        inner join client on quote.client_id = client.id
        inner join job on quote.job_id = job.id

and here is the output

 

I appreciate this is one record so not great for the explanation but i am trying to make an array 2 levels deep.

1st level would be the client name "test co" and the second level would be the jobName "test" everything else would be the next level.

I know this is not difficult but i just cannot get my head around it.

 

I am getting data out of the current arrays fine with the following

foreach ($quotes as $quote => $items) {
        $out .= $quote;
        foreach ($items as $item) {
            $out .= $item['jobName'];
        }
    }

 

Edited August 24, 2023 by Adamhumbug

[Barand]

try

$res = $pdo->query("SELECT client.company_name
                         , job.name as jobName
                         , version
                         , currency
                         , job.internal_ref
                         , kit_delivery
                         , kit_return
                         , quote_status_id 
                   from quote
                   inner join client on quote.client_id = client.id
                   inner join job on quote.job_id = job.id
            ");
$data = $res->fetchAll(PDO::FETCH_GROUP);            
$result = [ 'Test_Co' => $data ];

echo '<pre>' . print_r($result, 1) . '</pre>';

 

[Adamhumbug]

2 minutes ago, Barand said:

try

$res = $pdo->query("SELECT client.company_name
                         , job.name as jobName
                         , version
                         , currency
                         , job.internal_ref
                         , kit_delivery
                         , kit_return
                         , quote_status_id 
                   from quote
                   inner join client on quote.client_id = client.id
                   inner join job on quote.job_id = job.id
            ");
$data = $res->fetchAll(PDO::FETCH_GROUP);            
$result = [ 'Test_Co' => $data ];

echo '<pre>' . print_r($result, 1) . '</pre>';

 

That gives the following

[Barand]

I'll try and find a similar table of my own to test with and try again

[Adamhumbug]

1 minute ago, Barand said:

I'll try and find a similar table of my own to test with and try again

 

I have added some more info to be selected just for ease.

 

Thank you Barry - always appreciated

[Barand]

Thanks, but pictures are real bastard to process.

a var_export($array) with a few records would be much more useful.

[Barand]

You don't want to help - too much to ask?

OK - Here's how it looks with my data...

+-----------+-----------+------------+-----------+-------------------+------------+
| member_id | user_name | first_name | last_name | email             | phone      |
+-----------+-----------+------------+-----------+-------------------+------------+
| 1         | cheggs    | Scott      | Chegg     | cheggs@ggmail.com | 1345678902 |
| 2         | norderl   | Laura      | Norder    | lauran@ggmail.com | 2345678901 |
| 3         | canarit   | Tom        | DiCanari  | tomdc@ggmail.com  | 6543219878 |
| 4         | peted     | Peter      | Dowt      | pete.d@ggmail.com | 9876543210 |
| 5         | tonins    | Sarah      | Tonin     | saraht@ggmail.com | 7896321455 |
+-----------+-----------+------------+-----------+-------------------+------------+

code

$res = $pdo->query("SELECT user_name
                         , last_name
                         , first_name
                         , email
                         , phone
                    FROM member
            ");
$data = $res->fetchAll(PDO::FETCH_GROUP);            
$result = [];
foreach ($data as $u)  {
    $result[$u['user_name']][$u['last_name']] = array_slice($u,2);
}
echo '<pre>' . print_r($result, 1) . '</pre>';           

results

Array
(
    [cheggs] => Array
        (
            [Chegg] => Array
                (
                    [first_name] => Scott
                    [email] => cheggs@ggmail.com
                    [phone] => 1345678902
                )

        )

    [norderl] => Array
        (
            [Norder] => Array
                (
                    [first_name] => Laura
                    [email] => lauran@ggmail.com
                    [phone] => 2345678901
                )

        )

    [canarit] => Array
        (
            [DiCanari] => Array
                (
                    [first_name] => Tom
                    [email] => tomdc@ggmail.com
                    [phone] => 6543219878
                )

        )

    [peted] => Array
        (
            [Dowt] => Array
                (
                    [first_name] => Peter
                    [email] => pete.d@ggmail.com
                    [phone] => 9876543210
                )

        )

    [tonins] => Array
        (
            [Tonin] => Array
                (
                    [first_name] => Sarah
                    [email] => saraht@ggmail.com
                    [phone] => 7896321455
                )

        )

)

 

[Adamhumbug]

1 hour ago, Barand said:

Thanks, but pictures are real bastard to process.

a var_export($array) with a few records would be much more useful.

This is what i get for var export array - i hope i have used it correctly.

 

array (
  'Test_Co' => 
  array (
    'Big Event Co' => 
    array (
      0 => 
      array (
        'jobName' => 'TEST TC',
        'version' => 0,
        'currency' => '1',
        'internal_ref' => '00000',
        'kit_delivery' => '2023-08-01',
        'kit_return' => '2023-08-03',
        'quote_status_id' => 1,
      ),
    ),
    'Test Co' => 
    array (
      0 => 
      array (
        'jobName' => 'Test',
        'version' => 0,
        'currency' => '1',
        'internal_ref' => '123',
        'kit_delivery' => '2023-08-01',
        'kit_return' => '2023-08-02',
        'quote_status_id' => 1,
      ),
      1 => 
      array (
        'jobName' => 'Second Job',
        'version' => 0,
        'currency' => '2',
        'internal_ref' => 'ref',
        'kit_delivery' => '2023-08-16',
        'kit_return' => '2023-08-25',
        'quote_status_id' => 1,
      ),
    ),
  ),
)

 

[Adamhumbug]

36 minutes ago, Barand said:

You don't want to help - too much to ask?

OK - Here's how it looks with my data...

+-----------+-----------+------------+-----------+-------------------+------------+
| member_id | user_name | first_name | last_name | email             | phone      |
+-----------+-----------+------------+-----------+-------------------+------------+
| 1         | cheggs    | Scott      | Chegg     | cheggs@ggmail.com | 1345678902 |
| 2         | norderl   | Laura      | Norder    | lauran@ggmail.com | 2345678901 |
| 3         | canarit   | Tom        | DiCanari  | tomdc@ggmail.com  | 6543219878 |
| 4         | peted     | Peter      | Dowt      | pete.d@ggmail.com | 9876543210 |
| 5         | tonins    | Sarah      | Tonin     | saraht@ggmail.com | 7896321455 |
+-----------+-----------+------------+-----------+-------------------+------------+

code

$res = $pdo->query("SELECT user_name
                         , last_name
                         , first_name
                         , email
                         , phone
                    FROM member
            ");
$data = $res->fetchAll(PDO::FETCH_GROUP);            
$result = [];
foreach ($data as $u)  {
    $result[$u['user_name']][$u['last_name']] = array_slice($u,2);
}
echo '<pre>' . print_r($result, 1) . '</pre>';           

results

Array
(
    [cheggs] => Array
        (
            [Chegg] => Array
                (
                    [first_name] => Scott
                    [email] => cheggs@ggmail.com
                    [phone] => 1345678902
                )

        )

    [norderl] => Array
        (
            [Norder] => Array
                (
                    [first_name] => Laura
                    [email] => lauran@ggmail.com
                    [phone] => 2345678901
                )

        )

    [canarit] => Array
        (
            [DiCanari] => Array
                (
                    [first_name] => Tom
                    [email] => tomdc@ggmail.com
                    [phone] => 6543219878
                )

        )

    [peted] => Array
        (
            [Dowt] => Array
                (
                    [first_name] => Peter
                    [email] => pete.d@ggmail.com
                    [phone] => 9876543210
                )

        )

    [tonins] => Array
        (
            [Tonin] => Array
                (
                    [first_name] => Sarah
                    [email] => saraht@ggmail.com
                    [phone] => 7896321455
                )

        )

)

 

Just seen this - sorry it took a while to reply.

I have tried:

$res = $pdo->query("SELECT client.company_name
                         , job.name as jobName
                         , version
                         , currency
                         , job.internal_ref
                         , kit_delivery
                         , kit_return
                         , quote_status_id 
                   from quote
                   inner join client on quote.client_id = client.id
                   inner join job on quote.job_id = job.id
            ");
    $data = $res->fetchAll(PDO::FETCH_GROUP);
    $result = [];
    foreach($data as $u){
        $result[$u['jobName']][$u['internal_ref']] = array_slice($u, 2);
    }

    echo '<pre>' . print_r($result, 1) . '</pre>';

but i am getting "invalid array key" on jobName and internal_ref

[Barand]

Oops!

FETCH_GROUP should be FETCH_ASSOC.

You also need to change the order of your selected columns so that the first two are the ones you are using as the keys, then the array_slice takes the rest..

[ginerjm]

That is what happens when you split cases in PHP.  Stick to all lowercase and this won't happen.  'internal_ref' is a mystery since there is no reference to it that I can compare to.

 

[Adamhumbug]

Thanks again all - will keep this handy for the next array that blows my mind!

[ginerjm]

IMHO - if you have your data in a well-organized proper RDBMS you s/b able to use it without creating some difficult-to-manage multi-dimensional array.  Get a better handle on sql and perhaps next time you can see a solution that frees from your current style.

[Adamhumbug]

I am always looking to get better and i get most of my tips from here - i will bare that in mind.  I do have another question on the answer that has been given for this question.

I have the following to generate my HTML - based on the info provided in the answer.

if in the second for each loop i want to access one of the $items which i dont iterate through until the third for each - how would i go about doing that.

 foreach ($results as $client => $jobs){
        
        foreach($jobs as $j => $items){
            
                foreach($items as $item){    
                }       
        }
  }

Basically, one of the $items has a status and i want to change the colour of the table row that is created depending on this - but the table rows are created in the second for each.

[Barand]

You do have the $items array available to that second foreach(). Just access the value you need.

[ginerjm]

Barand is correct but perhaps you should have included that other value in the earlier query so you don't have to go look for it.

[Adamhumbug]

Ill have a play around with it and see where i get to.  Thanks all.