teelou teelou - 28 days ago 10
PHP Question

To make data into table format before exporting to Excel

I use this function below to process the array data I retrieve from my db, then export this data for Excel.

# feed the final items to our formatting function...
$contents = get_excel_data($items);

function get_excel_data($items){

# set the variable
$output = null;

# check if the data is an items and is not empty
if (is_array($items) && !empty($items))
{
# start the row at 0
$row = 0;

# loop the items
# "foreach(array_values($data) as $item)" is too complicated. "foreach($data as $item)" will suffice.
# foreach(array_values($items) as $item)
foreach($items as $item)
{
if (is_array($item) && !empty($item))
{
if ($row == 0)
{
# write the column headers
$output = implode("\t",array_keys($item));
$output .= "\n";
}
# create a line of values for this row...
$output .= implode("\t",array_values($item));
$output .= "\n";

# increment the row so we don't create headers all over again
$row++;
}
}
}

# return the result
return $output;
}


It processes the output like this below,

cat_id cat_name cat_order cat_hide cat_important cat_created cat_updated
1 Primary image 1 0 1 0000-00-00 00:00:00 2011-01-17 17:26:51
2 Secondary image 2 0 1 0000-00-00 00:00:00 2011-01-17 17:27:01
3 Tertiary image 3 0 1 0000-00-00 00:00:00 2010-10-08 20:03:56
4 Quartary image 4 0 1 0000-00-00 00:00:00 2010-10-08 20:03:56


but ideally I want to wrap the output in a table like this,

<table border="1">
<tr>
<th>cat_id</th>
<th>cat_name</th>
<th>cat_order</th>
<th>cat_hide</th>
<th>cat_important</th>
<th>cat_created</th>
<th>cat_updated</th>
</tr>

<tr>
<td>1</td>
<td>Primary image</td>
<td>1</td>
<td>0</td>
<td>1</td>
<td>0000-00-00 00:00:00</td>
<td>2011-01-17 17:26:51</td>
</tr>

<td>2</td>
<td>Secondary image</td>
<td>2</td>
<td>0</td>
<td>1</td>
<td>0000-00-00 00:00:00</td>
<td>2011-01-17 17:26:51</td>
</tr>
...
</table>


Any ideas what I should add into the original code to output the table above?

I tried with my version of modification below but it fails!

function get_excel_data($items){

# set the variable
$output = null;

# check if the data is an items and is not empty
if (is_array($items) && !empty($items))
{
# start the row at 0
$row = 0;

# loop the items
# "foreach(array_values($data) as $item)" is too complicated. "foreach($data as $item)" will suffice.
# foreach(array_values($items) as $item)
foreach($items as $key => $value )
{

if ($row == 0)
{
/*
# write the column headers
$output = implode("\t",array_keys($item));
$output .= "\n";
*/

$output = '<table border="1"><tr>';
$output .= '<th>'.$key.'</th>';
$output .= '</tr>';
}
/*
# create a line of values for this row...
$output .= implode("\t",array_values($item));
$output .= "\n";
*/

$output .= '<tr>';
$output .= '<td>'.$value.'</td>';

$output .= '</tr>';


if ($row == 0)
{
$output .= '</table>';
}

# increment the row so we don't create headers all over again
$row++;

}
}

# return the result
return $output;
}


Thanks.

Answer

Here is an untested variant on your function that is likely to work.

# feed the final items to our formatting function...
$contents = get_excel_data($items);

function get_excel_data($items){

    # set the variable
    $output = null;

    # check if the data is an items and is not empty
    if (is_array($items)  && !empty($items))
    {
        # start the row at 0
        $row = 0;

        $output = '<table border="1">'

        # loop the items
        # "foreach(array_values($data) as $item)" is too complicated. "foreach($data as $item)" will suffice.
        # foreach(array_values($items) as $item)
        foreach($items as $item)
        {
            if (is_array($item) && !empty($item))
            {
                if ($row == 0)
                {
                    $output .= '<tr>';
                    foreach(array_keys($item) as $header)
                    {
                        $output .= '<th>'.$header.'</th>';
                    }
                    $output .= '</tr>';
                }

                $output .= '<tr>';
                foreach(array_values($item) as $cell)
                {
                    $output .= '<td>'.$cell.'</td>';
                }
                $output .= '</tr>';

                # increment the row so we only create headers once
                $row++;
            }
        }
        $output .= '</table>';
    }

    # return the result
    return $output;
}