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.
CodePudding user response:
Dont convert things to JSON until you have completed building the array or object
$stmt = $connection->prepare($sql);
$stmt->execute();
while ($result = $stmt->fetch(PDO::FETCH_ASSOC)) {
$rows[] = $result;
}
echo json_encode($rows);
You could simplify that to
$stmt = $connection->prepare($sql);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC) {
echo json_encode($rows);
PART 2
First sort the resultset in spec_num order
$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
ORDER BY spec_num";
Then the code changes. Build the dtp array bit by bit, I made this able to cope with more than one spec_num being selected
$stmt = $connection->prepare($sql);
$stmt->execute();
$last_spec_num = NULL;
$dtp = [];
$tmp = [];
while ($row= $stmt->fetch(PDO::FETCH_ASSOC)) {
if ( $last_spec_num !== NULL ) {
// first time through to create a $tmp
$tmp = ['spec_num' => $row['spec_num'],
'spec_fld' => $row['spec_fld'],
'auth_ids' => $row['auth_id']
];
$last_spec_num = $row['spec_num'];
}
if ( $last_spec_num != $row['spec_num'] ) {
// got new spec_num
$dtp[] = $tmp;
$tmp = []; // init tmp
// load this row into the new $tmp
$tmp = ['spec_num' => $row['spec_num'],
'spec_fld' => $row['spec_fld'],
'auth_ids' => $row['auth_id']
];
// set the new las spec num
$last_spec_num = $row['spec_num'];
} else {
// same spec_num
$tmp['auth_ids'][] = $row['auth_id'];
}
}
// echo json_encode($dtp); here if thats what you want to do
CodePudding user response:
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.