I have been trying to get a query output formatted in a specific way but I am surely doing something wrong. Could anyone that knows what I am doing wrong give me a hand? Thanks a lot.
I have the following db call:
$sql = "SELECT tbl1.spec_num As spec_num, IF(tbl1.spec_fld, 'TRUE', 'FALSE') As spec_fld, tbl2.auth_id As auth_id FROM spec_table tbl1 JOIN spec_auth tbl2 ON tbl1.id=tbl2.spec_table_id WHERE tbl1.spec_fld=1 AND tbl2.enb=1;";
If I run this query in the db (mysql) I get this:
spec_num spec_fld auth_id 123413253242135234213432112345DDDDDDDG TRUE 1234567 123413253242135234213432112345DDDDDDDG TRUE 3423435 123413253242135234213432112345DDDDDDDG TRUE 9234245
When I make a call to the DB in PHP using PDO I do the following:
$stmt = $connection->prepare($sql); $stmt->execute(); while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) { $result_json = json_encode($result); echo $result_json; }
My echo inside the loop shows this:
{"spec_num":"123413253242135234213432112345DDDDDDDG","spec_fld":"TRUE","auth_id":"3423435"} {"spec_num":"123413253242135234213432112345DDDDDDDG","spec_fld":"TRUE","auth_id":"9234245"}
But what I need now is to create a variable ($dtp) outside the while loop that looks like this:
$dtp = [ 'spec_num' => '123413253242135234213432112345DDDDDDDG', 'spec_fld' => TRUE, 'auth_ids' => [ '1234567', '3423435', '9234245', ], ];
Any ideas on the best way to do this? Thanks again in advance.
Advertisement
Answer
Just use
$arr = $stmt->fetchAll(PDO::FETCH_ASSOC);
and you get an array like
$arr = [ ["spec_num" => "123413253242135234213432112345DDDDDDDG","spec_fld" => TRUE,"auth_id" => "1234567"], ["spec_num" => "123413253242135234213432112345DDDDDDDG","spec_fld" => TRUE,"auth_id" => "3423435"], ["spec_num" => "123413253242135234213432112345DDDDDDDG","spec_fld" => TRUE,"auth_id" => "9234245"], ];
You want to create such an array:
$dtp = [ 'spec_num' => '123413253242135234213432112345DDDDDDDG', 'spec_fld' => TRUE, 'auth_ids' => [ '1234567', '3423435', '9234245', ], ];
It only makes sense to create this structure it if the SQL query ensures that spec_num and spec_fld are identical in all lines.
The solution for this special case is very simple:
$dtp = [ "spec_num" => $arr[0]["spec_num"], "spec_fld" => $arr[0]["spec_fld"], "auth_id" => [] ]; foreach($arr as $key => $row){ $dtp["auth_id"][] = $row["auth_id"]; }
If spec_num and spec_fld are not identical in all lines, you must first group by spec_num and then by spec_fld. The solution is then a 4 dimensional array.