I'm trying to create an array or an object that has data coming from the database and have the form like the following JSON structuer
{
"entities": [
{
"FacilityCode": "02835",
"ClaimYear": 2021,
"FolioDiseases": [
{
"DiseaseCode": "G50.1",
"Status": "Provisional",
},
{
"DiseaseCode": "G50.1",
"Status": "Final",
}
],
"FolioItems": [
{
"ItemCode": "10002",
"OtherDetails": null,
}
],
"CreatedBy": "Joseph E. Tawete",
"DateCreated": "2021-04-30",
]
}
What I have tried:
Here is what i have trying
<?php
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'new_nhif');
define('USERNAME', 'root');
define('PASSWORD', '');
error_reporting(E_ALL);
ini_set('display_errors', 1);
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$connection = new mysqli(HOST, USERNAME, PASSWORD, DATABASE, PORT);
$sql = sprintf(
'SELECT
nh.MembershipNo,
nh.FullName,
nh.id as nhid,
lb.labrequest,
fd.diagnosis,
fd.DiseaseCode,
fd.CreatedBy as fdcrb,
dz.name
FROM nhif_data AS nh
LEFT JOIN laboratory AS lb ON lb.re_id = nh.id
LEFT JOIN foliodisease AS fd ON fd.re_id = nh.id
LEFT JOIN dawa_zilizotoka AS dz ON dz.re_id = nh.id
WHERE lb.re_id = nh.id
AND fd.re_id = nh.id
AND dz.re_id = nh.id
GROUP BY nh.MembershipNo
'
);
$obj = new stdClass;
$result = $connection->query($sql);
$vipimo = array();
$dawa = array();
$all = array();
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
$obj->MembershipNo = $row['MembershipNo'];
$obj->FullName = $row['FullName'];
$id = $row['nhid'];
$sql2 = "SELECT * FROM foliodisease WHERE re_id ='$id'";
$result1 = $connection->query($sql2);
if ($result1->num_rows > 0) {
while($row2 = $result1->fetch_assoc()) {
$vipimo['diagnosis']= $row2['diagnosis'];
$vipimo['DiseaseCode']= $row2['DiseaseCode'];
$obj->FolioDiseases[] = $vipimo;
}
}
$sql3 = "SELECT * FROM dawa_zilizotoka WHERE re_id = $id";
$result3 = $connection->query($sql3);
if ($result3->num_rows > 0) {
while($row3 = $result3->fetch_assoc()) {
$dawa['name']= $row3['name'];
$obj->FolioItems[] = $dawa;
}
}
$all[] = $obj;
}
print_r(json_encode(['entities'=> $all], JSON_PRETTY_PRINT));
}
?>
And I get all data repeatedly instead of getting only two patients with their records but the records of patient one repeat to patient two