Jump to content

Build multidimentional array from select


Go to solution Solved by Barand,

Recommended Posts

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

Screenshot2023-08-24at21_17_22.png.43173c1368c6f05b9c27f1fcec2b201f.png

 

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 by Adamhumbug

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>';

 

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

Screenshot2023-08-24at21_31_35.png.a83b9466fabf1f21cacb79c7ec7cb85f.png

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
                )

        )

)

 

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,
      ),
    ),
  ),
)

 

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

  • Solution

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..

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.

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.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.