Adamhumbug Posted August 24, 2023 Share Posted August 24, 2023 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/317218-build-multidimentional-array-from-select/ Share on other sites More sharing options...
Barand Posted August 24, 2023 Share Posted August 24, 2023 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>'; Quote Link to comment https://forums.phpfreaks.com/topic/317218-build-multidimentional-array-from-select/#findComment-1611395 Share on other sites More sharing options...
Adamhumbug Posted August 24, 2023 Author Share Posted August 24, 2023 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 Quote Link to comment https://forums.phpfreaks.com/topic/317218-build-multidimentional-array-from-select/#findComment-1611396 Share on other sites More sharing options...
Barand Posted August 24, 2023 Share Posted August 24, 2023 I'll try and find a similar table of my own to test with and try again Quote Link to comment https://forums.phpfreaks.com/topic/317218-build-multidimentional-array-from-select/#findComment-1611397 Share on other sites More sharing options...
Adamhumbug Posted August 24, 2023 Author Share Posted August 24, 2023 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 Quote Link to comment https://forums.phpfreaks.com/topic/317218-build-multidimentional-array-from-select/#findComment-1611398 Share on other sites More sharing options...
Barand Posted August 24, 2023 Share Posted August 24, 2023 Thanks, but pictures are real bastard to process. a var_export($array) with a few records would be much more useful. Quote Link to comment https://forums.phpfreaks.com/topic/317218-build-multidimentional-array-from-select/#findComment-1611399 Share on other sites More sharing options...
Barand Posted August 24, 2023 Share Posted August 24, 2023 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 ) ) ) Quote Link to comment https://forums.phpfreaks.com/topic/317218-build-multidimentional-array-from-select/#findComment-1611400 Share on other sites More sharing options...
Adamhumbug Posted August 24, 2023 Author Share Posted August 24, 2023 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, ), ), ), ) Quote Link to comment https://forums.phpfreaks.com/topic/317218-build-multidimentional-array-from-select/#findComment-1611401 Share on other sites More sharing options...
Adamhumbug Posted August 24, 2023 Author Share Posted August 24, 2023 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 Quote Link to comment https://forums.phpfreaks.com/topic/317218-build-multidimentional-array-from-select/#findComment-1611402 Share on other sites More sharing options...
Solution Barand Posted August 24, 2023 Solution Share Posted August 24, 2023 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.. Quote Link to comment https://forums.phpfreaks.com/topic/317218-build-multidimentional-array-from-select/#findComment-1611403 Share on other sites More sharing options...
ginerjm Posted August 24, 2023 Share Posted August 24, 2023 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. Quote Link to comment https://forums.phpfreaks.com/topic/317218-build-multidimentional-array-from-select/#findComment-1611404 Share on other sites More sharing options...
Adamhumbug Posted August 25, 2023 Author Share Posted August 25, 2023 Thanks again all - will keep this handy for the next array that blows my mind! Quote Link to comment https://forums.phpfreaks.com/topic/317218-build-multidimentional-array-from-select/#findComment-1611411 Share on other sites More sharing options...
ginerjm Posted August 25, 2023 Share Posted August 25, 2023 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. Quote Link to comment https://forums.phpfreaks.com/topic/317218-build-multidimentional-array-from-select/#findComment-1611415 Share on other sites More sharing options...
Adamhumbug Posted August 25, 2023 Author Share Posted August 25, 2023 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. Quote Link to comment https://forums.phpfreaks.com/topic/317218-build-multidimentional-array-from-select/#findComment-1611417 Share on other sites More sharing options...
Barand Posted August 25, 2023 Share Posted August 25, 2023 You do have the $items array available to that second foreach(). Just access the value you need. Quote Link to comment https://forums.phpfreaks.com/topic/317218-build-multidimentional-array-from-select/#findComment-1611418 Share on other sites More sharing options...
ginerjm Posted August 25, 2023 Share Posted August 25, 2023 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. Quote Link to comment https://forums.phpfreaks.com/topic/317218-build-multidimentional-array-from-select/#findComment-1611419 Share on other sites More sharing options...
Adamhumbug Posted August 25, 2023 Author Share Posted August 25, 2023 Ill have a play around with it and see where i get to. Thanks all. Quote Link to comment https://forums.phpfreaks.com/topic/317218-build-multidimentional-array-from-select/#findComment-1611421 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.