Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
// Db configs.
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()) {
            
            // print_r(json_encode(['entities'=> $row],JSON_PRETTY_PRINT));

             $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
Posted
Updated 26-May-21 4:31am

1 solution

You're probably seeing the details overwrite because you're declaring your $obj variable outside of the while loop. So what happens is that the $obj doesn't get created per row, it just exists as one object.

Try moving the $obj = new stdClass; above the $obj->MembershipNo like so:

PHP
$obj = new stdClass;
$obj->MembershipNo = $row['MembershipNo'];
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900