Andurit Andurit - 5 months ago 14
SQL Question

SQL JOIN return joined data as array

I try to make some nice structure data for frontend on my server side.

Response should looks like:

[
{
"id": "57484a7e0cdb86d125ebce9c",
"wasCalled": true,
"wasGood": false,
"candidateName": "Tonia Santos",
"positions": [
{
"positionId": "48asd4asd36",
"positionName": "Pozicia1"
},
{
"positionId": "954asd5as4d",
"positionName": "Pozicia4"
}
],
"addedBy": "User1",
"registered": "2014-11-30T07:13:27 -01:00"
}
]


Database structure:


  • candidates: All info exept positions

    | id | wasCalled | wasGood | addedBy | firstName | lastName

  • candidates_positions: Each row is one user and one position

    | id | candidateId | positionId | positionName



How I try it:

class getCandidates
{
public function getFromDatabase(){
try {
$con = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
$con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$sql = "SELECT * FROM candidates as c INNER JOIN candidates_positions as cp ON c.id=cp.candidateId;";
$stmt = $con->prepare($sql);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}catch( PDOException $e ) {
return $e->getMessage();
}

}

public function createJson($data){
return json_encode($data);
}
}


What it actually create:

[{
"id": "1",
"firstName": "Tonia",
"lastName": "Santos",
"addedBy": "22",
"wasCalled": "1",
"wasGood": null,
"candidateId": "1",
"positionId": "1",
"positionName": "Pozicia4"
},
{
"id": "1",
"firstName": "Tonia",
"lastName": "Santos",
"addedBy": "22",
"wasCalled": "1",
"wasGood": null,
"candidateId": "1",
"positionId": "1",
"positionName": "Pozicia1"
}]


Is there some nice way or best practice how to create JSON similar as on TOP of this question without making multiple selects on database and building JSON?

Thanks for any advise

Answer

Since you get multiple rows for the same people, containing different positions, it might look like (typing out of my head so may be some error there, but try it out and see):

 function prepare_result($data) {
  $result = [];
  foreach ($data as $record) {
    if (!isset($result[$record['candidateId']])) {
      $result[$record['candidateId']] = array(
        'id' => (int) $record['candidateId'],
        'wasCalled' => true,
        'wasGood' => false,
        'candidateName' => $record['firstName'] . " " . $record['lastName'],
        'addedBy' => 'no idea', // you need additional join i think, since your result returns only ID
        'registered' => 'no idea either', // same as above, original query returns no date
        'positions' => array(
          array(
            'positionId' => $record['positionId'],
            'positionName' => $record['positionName']
          )
        )
      );
    } else {
      $result[$record['candidateId']]['positions'][] = array(
        'positionId' => $record['positionId'],
        'positionName' => $record['positionName']
      );
    }
  }

  sort($result);

  return json_encode($result);
}

Note the comments for registered and addedBy.