Formatting columns in a table

[eluser]skypanther[/eluser]
I'm pulling data from a database then outputting it in an HTML table. That part's fine. But what I can't figure out is how to apply formatting to the individual columns in the results.

So, the first column is a part number, then a product name, then a price, etc. I want to format the price column as money (applying the money_format() or similar function).

My controller:

Code:

// call my model's function to grab the data:
$data['results'] = $this->productsmodel->get_products();
// load the HTML Table Class
$this->load->library('table');
$this->table->clear();
$this->table->set_heading('Part #', 'Name', 'List price', 'Parts', 'Hours', 'Total');
$this->template->set('title','Products');
$this->template->load('template','products', $data);

And my view:

Code:

echo $this->table->generate($results);

Works fine but how can I format columns? I tried manually adding rows with $this->table->add_row() but I can't figure out how to extract the data returned by my model. BTW, it's just calling $query = $this->db->get('parts'); I could do the formatting with jQuery but there are a lot of rows and that would be slow I'm guessing.

Thanks,
Tim

[eluser]oldmatt[/eluser]
Well, you could do it in MySQL(if the is what DB you are using). Or you could use a template, or you could prepare the data before you pass it to the generate method.

[eluser]skypanther[/eluser]
I guess it's the "prepare the data before you pass it to the generate method" that I figured was the best way. But that's what I can't figure out. I never thought about using MySQL, but you're right -- CONCAT('$', FORMAT(mynumberfield, 2))

I'd still like to know how to do the "prep the data" technique.

[eluser]InsiteFX[/eluser]
You can add a css class to the table! You need to use the Table Classes table template.

Code:

class MY_Controller extends CI_Controller
{

    public function __construct()
    {
        parent::__construct();

        $this->load->library('table');

        $tmpl = array (
            'table_open'         => '<table border="0" cellpadding="4" cellspacing="0">',

            'thead_open'         => '<thead>',
            'thead_close'        => '</thead>',

            'heading_row_start'  => '<tr>',
            'heading_row_end'    => '</tr>',
            'heading_cell_start' => '<th>',
            'heading_cell_end'   => '</th>',

            'tbody_open'         => '<tbody>',
            'tbody_close'        => '</tbody>',

            'row_start'          => '<tr>',
            'row_end'            => '</tr>',
            'cell_start'         => '<td>',
            'cell_end'           => '</td>',

            'row_alt_start'      => '<tr class="alt">',   // See class added here!
            'row_alt_end'        => '</tr>',
            'cell_alt_start'     => '<td>',
            'cell_alt_end'       => '</td>',

            'table_close'        => '</table>'
        );

        $this->table->set_template($tmpl);
    }

}

I am using a MY_Controller but should work in a regular Controller.

For formatting you can use the php sprintf method.

InsiteFX

[eluser]skypanther[/eluser]
Thanks, adding a class as you showed would help me format rows. But I need to format the content in the third column. I don't see how that can be done with the table templates.

As for the printf function, how do I apply it to the third column of data using the table library?

[eluser]InsiteFX[/eluser]
Can you show your view code.

InsiteFX

[eluser]skypanther[/eluser]
Sure, it's really just the table->generate call. The rest is just HTML/CSS to pretty it up.

Code:

<div class="button" style="width:150px;float:right;margin-right:20px;"><a href="import" title="Import new prices">Import new prices</a></div>
<br clear="all"/>
<div class="dashstats">
<div class="dsTitle"><span id="mProducts">&nbsp;</span>Products</div>
<div class="dsBody" id="partstable"><br clear="all"/>
&lt;?php
    echo $this->table->generate($results);

?&gt;

</div>
</div> &lt;!-- // end .dashstats --&gt;
&lt;script type="text/javascript">
$(document).ready(function(){
   $("#partstable tr").mouseover(function(){$(this).addClass("over");}).mouseout(function(){$(this).removeClass("over");});
   $("#partstable tr:even").addClass("evenTR");
});
&lt;/script>

[eluser]InsiteFX[/eluser]
to format the your column you would need to loop through the returned result set either in your model or your controller.

Code:

$data['results'] = $this->productsmodel->get_products();

Then you could format the fields you need formatted.

InsiteFX

[eluser]skypanther[/eluser]
When I print_r($results) in my view, this is what I get:

Code:

CI_DB_mysql_result Object ( [conn_id] => Resource id #27 [result_id] => Resource id #42
[result_array] => Array ( ) [result_object] => Array ( ) [custom_result_object] =>
Array ( ) [current_row] => 0 [num_rows] => 145 [row_data] => )

What do I loop over within that to access the data? Or are you saying I need to dispense with the automated techniques offered with the Table library and build my own? I'm certainly familiar with PHP enough to query a DB, loop through a result set, and output an HTML table. What I'm trying to figure out is what & how CI can do the heavy lifting for me. The automated table-building library is a great timesaver. I just figured there was some callback or other means built-in for me to manipulate rows and columns prior to output. It seems not, so I'll go the roll-my-own route.

I don't mean that to be snotty. And I really appreciate your help! If there's not a built-in way to do what I want, I'm fine with doing it more manually. I'm just trying to learn the CI way (since I obviously have not used it very much).

Thanks,
Tim

[eluser]Julian[/eluser]
Hello, good post, please visit my post and see how you can become a millionaire in the forex. [URL="http://millionaireinforex.com"]Become a Millionaire in Forex[/URL]