Storing JOIN Values in PHP and showing them as a table-CodePudding

I have following SQL query:

SELECT tbltasks.fldTaskNr
    ,tbltasks.fldRITMNr
    ,tbltasks.fldCHGNr
    ,tblci.fldCI
    ,tblgxp.fldGxP
    ,tblrequester.fldRequester
    ,tblstatus.fldStatus
    ,tbltasks.fldDescription
    ,tblresponsible.fldResponsible
    ,tbllocation.fldLocation
FROM tbltasks
LEFT JOIN tblCI ON tblci.pkCI = tbltasks.fkCI
LEFT JOIN tblgxp ON tblgxp.pkGxP = tbltasks.fkGxP
LEFT JOIN tblrequester ON tblrequester.pkRequester = tbltasks.fkRequester
LEFT JOIN tblstatus ON tblstatus.pkStatus = tbltasks.fkStatus
LEFT JOIN tblresponsible ON tblresponsible.pkResponsible = tbltasks.fkResponsible
LEFT JOIN tbllocation ON tbllocation.pkLocation = tbltasks.fkLocation

OUTPUT:

Now I want to store the values of this query and output them as a table, should look like this:

I got the following table as HTML, the output should show in this table and repeat itself as there will be several entries in the database:

                    <th>Task Nr.</th>
                    <th>RITM Nr.</th>
                    <th>CHG Nr.</th>
                    <th>CI</th>
                    <th>GxP</th>
                    <th>Task Requester</th>
                    <th>Task Status</th>
                    <th>Description</th>
                    <th>Responsible</th>
                    <th style="width: 201px;">Location</th>
                </tr>

How can I do that? Any help is appreciated!

CodePudding user response：

It's easy but process is little bit long. First make sure to give your query column an name value as follow:

SELECT tbltasks.fldTaskNr AS "TASK_NR"
,tbltasks.fldRITMNr AS "RITMNR"
,tbltasks.fldCHGNr AS "CHGNR"
,tblci.fldCI AS "FLDCI"
,tblgxp.fldGxP AS "GXP"
,tblrequester.fldRequester AS "REQUESTER"
,tblstatus.fldStatus AS "FLD_STATUS"
,tbltasks.fldDescription AS "FLD_DESCRIPTION"
,tblresponsible.fldResponsible AS "RESPONSIBLE"
,tbllocation.fldLocation AS "LOCATION" FROM tbltasks .. (rest of your joints)

Then use this code to convert your query into a array of object

$data = array();
while ($row = mysqli_fetch_assoc($result)) {
    $data[] = $row; 
} 
return $data;

Once this step is done then we can move on to your HTML Table part

<tr>
<th>Task Nr.</th>
<th>RITM Nr.</th>
<th>CHG Nr.</th>
<th>CI</th>
<th>GxP</th>
<th>Task Requester</th>
<th>Task Status</th>
<th>Description</th>
<th>Responsible</th>
<th style="width: 201px;">Location</th>
</tr>

<?php
for($i = 0 ;$i < count($data); $i  ){
echo '<tr>';
echo '<td>'.$data['TASK_NR'].'</td>';
echo '<td>'.$data['RITMNR'].'</td>';
echo '<td>'.$data['CHGNR'].'</td>';
echo '<td>'.$data['FLDCI'].'</td>';
echo '<td>'.$data['GXP'].'</td>';
echo '<td>'.$data['REQUESTER'].'</td>';
echo '<td>'.$data['FLD_STATUS'].'</td>';
echo '<td>'.$data['FLD_DESSCRIPTION'].'</td>';
echo '<td>'.$data['RESPONSIBLE'].'</td>';
echo '<td style="width: 201px;">'.$data['LOCATION'].'</td>';
echo '</tr>';
}
?>

Hope this will solve your problem, and make sure your file extension is ".php"

Vote my answer if it found useful. Thanks.

CodePudding user response：

You can print the data you pulled into html with foearch.

foreach ($data as $key => $val) {
    <tr>
       <td>
          <?php echo $val ?>
       </td>
    </tr>

}

You can get the result you want by applying the above code to your own table and data.