Slimshadddyyy Slimshadddyyy - 1 month ago 7
PHP Question

How do I write a PHP loop to display data from a database query in Table format?

Array Output

print_r($hours_effort_result); //prints below output

Array
(
[0] => Array
(
)

[1] => Array
(
[0] => Array
(
[RESOURCE_ID] => 1
[EMPLOYEE_NAME] => User 1
[SITE_STATUS] => Onsite
[ACTUAL_HOURS] => 120
[MONTH_YEAR] => JUL-2015
)

[1] => Array
(
[RESOURCE_ID] => 2
[EMPLOYEE_NAME] => User 2
[SITE_STATUS] => Onsite
[ACTUAL_HOURS] => 140
[MONTH_YEAR] => JUL-2015
)

)

[2] => Array
(
[0] => Array
(
[RESOURCE_ID] => 3
[EMPLOYEE_NAME] => User 1
[SITE_STATUS] => Offshore
[ACTUAL_HOURS] => 170
[MONTH_YEAR] => AUG-2015
)

[1] => Array
(
[RESOURCE_ID] => 4
[EMPLOYEE_NAME] => User 2
[SITE_STATUS] => Offshore
[ACTUAL_HOURS] => 180
[MONTH_YEAR] => AUG-2015
)

)

[3] => Array
(
)

)

foreach ($hours_effort_result as $hours_effort_data){

echo '<pre>';print_r($hours_effort_data);//below output
}

Array
(
)

Array
(
[0] => Array
(
[RESOURCE_ID] => 1
[EMPLOYEE_NAME] => User 1
[SITE_STATUS] => Onsite
[ACTUAL_HOURS] => 120
[MONTH_YEAR] => JUL-2015
)

[1] => Array
(
[RESOURCE_ID] => 2
[EMPLOYEE_NAME] => User 2
[SITE_STATUS] => Onsite
[ACTUAL_HOURS] => 140
[MONTH_YEAR] => JUL-2015
)

)

Array
(
[0] => Array
(
[RESOURCE_ID] => 3
[EMPLOYEE_NAME] => User 1
[SITE_STATUS] => Offshore
[ACTUAL_HOURS] => 170
[MONTH_YEAR] => AUG-2015
)

[1] => Array
(
[RESOURCE_ID] => 4
[EMPLOYEE_NAME] => User 2
[SITE_STATUS] => Offshore
[ACTUAL_HOURS] => 180
[MONTH_YEAR] => AUG-2015
)
)

Array
(
)


Display Months and Site

print_R($project_months); //outputs below data

Array
(
[0] => JUN-2015
[1] => JUL-2015
[2] => AUG-2015
[3] => SEP-2015
)


echo '<tr>';

foreach($project_months as $month_year){

echo '<td>Site</strong></td>';
echo '<td>'.$month_year.'</td>';
}
echo '</tr>';


Desired Output

Name of Engineer Site Jun-15 Site Jul-15 Site Aug-15 Site Sep-15
User 1 Onsite 120 Offshore 170
User 2 Onsite 140 Offshore 180


As one can see, Month
Jun
and
Sep
do not contain any data and so the array is empty while other months contains data which are printed in tabular format. The only data to be printed are
Engineer Name, Site and Hours


How do I loop the array in order to achieve above output?

Answer

This is assuming you have this variable defined:

$project_months = Array('JUN-2015', 'JUL-2015', 'AUG-2015', 'SEP-2015' );

First, create an array of users:

<?php 

$user_array = Array();

foreach ( $project_months as $month ) 
{
    foreach ($hours_effort_result as $key => $data)
    {
        if ( ! empty ($data) )
        {
            foreach ($data as $key => $val )
            {
                if ( $val['MONTH_YEAR'] == $month ) 
                {
                  $user_array[$val['EMPLOYEE_NAME']][$month]['SITE_STATUS'] = $val['SITE_STATUS'];
                  $user_array[$val['EMPLOYEE_NAME']][$month]['ACTUAL_HOURS'] = $val['ACTUAL_HOURS'];
                }
            }
        }
    }
}
?>

Second, in the table, loop through the $user_array and add n/a to those months without data.

<table>
<tr>
    <th>Name</th>
    <th>Site</th>
    <th>Jun-15</th>
    <th>Site</th>
    <th>Jul-15</th>
    <th>Site</th>
    <th>Aug-15</th>
    <th>Site</th>
    <th>Sep-15</th>
</tr>
<?php foreach ($user_array as $user_name => $user_data): ?>
    <tr>
        <td><?php print ($user_name); ?></td>
        <?php foreach ($project_months as $month): ?>
            <?php if ( array_key_exists( $month, $user_data ) ): ?>
                <td><?php print ($user_data[$month]['SITE_STATUS']); ?></td>
                <td><?php print ($user_data[$month]['ACTUAL_HOURS']); ?></td>
            <?php else: ?>
                <td>n/a</td>
                <td>n/a</td>
            <?php endif; ?>
        <?php endforeach; ?>
    </tr>
<?php endforeach; ?>
</table>