Click here to Skip to main content
15,039,149 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'];
   

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