Exporting nested table from Postgres database to Excel file- Using PHP

Question

I have 4 tables in the database (studies, sites, labs and investigators). They relate the following way:

1 Study 1.1 Site 1.1.1 Labs 1.1.2 Investigators

I am trying to export the data from those tables to an excel file, following the structure above. But it only loops through and print the information for the first item at each level. How can I make it print all items?

This is the code I have:

<?php

header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");

include_once("C:/webroot/connect.php");


//loop the query data to the table in same order as the headers
$sql_data = "select agent, study, ind_no, sponsor, status from dw_regtrack.qlstudies st left join dw_regtrack.user_studies us ON st.study=us.study_name where user_name='gsamara'";
$sql_data2="select study, amtrackid, name, pi, status, irbappcr, ohrp, ib, prot, ic, global1572, 1572, ibc, obarac, dsa FROM dw_regtrack.qlstudysites2";
$sql_data3="select amtrackid, labid, name, cap, clia, lnv FROM dw_regtrack.qlstudysitelabs";
$sql_data4="select amtrackid, personid, name, cv, ml, fd, hspt, dot, pisort from dw_regtrack.qlstudysiteinvests2";

$result=pg_query($sql_data);
$result2=pg_query($sql_data2);
$result3=pg_query($sql_data3);
$result4=pg_query($sql_data4);


while ($row = pg_fetch_array($result, NULL, PGSQL_ASSOC)){
    echo implode("\t", array('Agent', 'Study', 'IND No', 'IND Sponsor', 'Current Status'))."\r\n";
    echo implode("\t", array($row['agent'], $row['study'], $row['ind_no'],$row['sponsor'], $row['status'])) . "\r\n";

    while ($row2=pg_fetch_array($result2, NULL, PGSQL_ASSOC))
    {

        if ($row['study']==$row2['study']){ 
            echo implode("\t", array('','Site', 'PI', 'Status'))."\r\n";        
            echo implode("\t", array('',$row2['name'], $row2['pi'], $row2['status']))."\r\n";
        }

        //foreach

        while ($row3=pg_fetch_array($result3, NULL, PGSQL_ASSOC)){
            if ($row2['amtrackid']==$row3['amtrackid']){
                echo implode("\t", array('','','Lab', 'CAP', 'CLIA', 'LNV'))."\r\n";
                echo implode("\t", array('','',$row3['name'], $row3['cap'], $row4['clia'],$row4['lnv']))."\r\n";
            }
        }
        while ($row4=pg_fetch_array($result4, NULL, PGSQL_ASSOC)){
            if ($row2['amtrackid']==$row4['amtrackid']){
                echo implode("\t", array('','','Investigator', 'CV', 'ML', 'FD'))."\r\n";
                echo implode("\t", array('','',$row4['name'], $row4['cv'], $row4['ml'],$row4['fd']))."\r\n";
            }
        }
    }
}


?>

But this code is exporting the data like this: [![enter image description here][1]][1]

The Site row should be the second level and then Labs and Investigators in the third level. It should loop through each agent-study and find the corresponding site and then loop through each site and find it's labs and investigators. Also, even tho it prints all the Study-agents correctly, it only loops through the first study-agent, and it does export any data for the other agents.

Can anyone help me?

Thanks.

Answer 1

Problem solved. Here it is the working code.

<?php

header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");

include_once("C:/webroot/connect.php");

//loop the query data to the table in same order as the headers
$sql_data = "SELECT agent, study, ind_no, sponsor, status 
             FROM dw_regtrack.qlstudies st 
             LEFT JOIN dw_regtrack.user_studies us ON st.study=us.study_name 
             WHERE user_name='gsamara'";

$sql_data2="SELECT study, amtrackid, name, pi, status, irbappcr, ohrp, ib, prot, ic, global1572, 1572, ibc, obarac, dsa 
            FROM dw_regtrack.qlstudysites2";

$sql_data3="SELECT amtrackid, labid, name, cap, clia, lnv 
            FROM dw_regtrack.qlstudysitelabs";

$sql_data4="SELECT amtrackid, personid, name, cv, ml, fd, hspt, dot, pisort 
            FROM dw_regtrack.qlstudysiteinvests2";

$result=pg_query($sql_data);

while ($row = pg_fetch_array($result, NULL, PGSQL_ASSOC)){
    echo implode("\t", array('Agent', 'Study', 'IND No', 'IND Sponsor', 'Current Status'))."\r\n";
    echo implode("\t", array($row['agent'], $row['study'], $row['ind_no'],$row['sponsor'], $row['status'])) . "\r\n";

    $result2=pg_query($sql_data2." WHERE study = '".$row['study']."'");
    while ($row2=pg_fetch_array($result2, NULL, PGSQL_ASSOC))
    {
        echo implode("\t", array('','Site', 'PI', 'Status'))."\r\n";
        echo implode("\t", array('',$row2['name'], $row2['pi'], $row2['status']))."\r\n";

        $result3=pg_query($sql_data3." WHERE amtrackid= '".$row2['amtrackid']."'");     
        while ($row3=pg_fetch_array($result3, NULL, PGSQL_ASSOC)){
            echo implode("\t", array('','','Lab', 'CAP', 'CLIA', 'LNV'))."\r\n";
            echo implode("\t", array('','',$row3['name'], $row3['cap'], $row4['clia'],$row4['lnv']))."\r\n";
        }

        $result4=pg_query($sql_data4." WHERE amtrackid= '".$row2['amtrackid']."'"); 
        while ($row4=pg_fetch_array($result4, NULL, PGSQL_ASSOC)){
            echo implode("\t", array('','','Investigator', 'CV', 'ML', 'FD'))."\r\n";
            echo implode("\t", array('','',$row4['name'], $row4['cv'], $row4['ml'],$row4['fd']))."\r\n";
        }
    }
}

?>