To make data into table format before exporting to Excel

Question

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 1

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;
}