Formatting database results

[eluser]Madmartigan1[/eluser]
What's the best way to format database results before they are sent to the view file?

I'd rather not be writing php functions like strtolower() trim() and htmlspecialchars() into the view file - it makes a big mess. I can't seem to figure out how to do this for multiple database results using the active record class. It seems like it could be done in the model, but should be done in the controller.

I'd like to be able to use the parser class, which requires result_array() for database query results. It would be great if my boss, who knows nothing about php, could edit my view files without screwing them up.

This seems to work ok...

Code:

$query=$this->db->get('my_table');
$result=$query->result_array();
foreach($result as $row)
{
    $row['name']       = uc_words($row['name']);//example
    $row['comment']    = htmlentities($row['comment']);//example
    $data['my_data'][] = $row;
}
$this->parser->parse("my_view",$data);

Am I doing it right? :-S

Any help would be greatly appreciated, thanks!

[eluser]bigtony[/eluser]
The whole point of MVC is to separate out view logic from data logic. Therefore the view is the very place that you should be putting strtolower(), trim(), and htmlspecialchars(), etc., since these are all operations concerned with how stuff is output.

You can tidy it up a bit by using the functions in the [url="http://ellislab.com/codeigniter/user-guide/helpers/form_helper.html"]Form Helper[/url]

[eluser]Madmartigan1[/eluser]
That would make the parser class almost useless for database results!

The code I have used as an example DOES work, I was just wondering if there was the most efficient way to do the same thing.

I have to say bigtony, I disagree with you. Imagine what a mess a view file would be that had to preform many different string functions on several fields per result. I also don't see where the form helper has a role in this, but thanks anyway.

[eluser]InsiteFX[/eluser]
Look up sprintf and vprintf on PHP.NET

Enjoy
InsiteFX

[eluser]bigtony[/eluser]
[quote author="Madmartigan1" date="1253309327"]I have to say bigtony, I disagree with you. Imagine what a mess a view file would be that had to preform many different string functions on several fields per result.[/quote]
All you've done is move the "mess" out of the view and into somwwhere else.

Your code:

Code:

foreach($result as $row)
{
    $row['name']       = uc_words($row['name']);//example
    $row['comment']    = htmlentities($row['comment']);//example
    $data['my_data'][] = $row;
}

In the view instead:

Code:

<?php foreach($result->result() as $row): ?>
    <tr>
        <td>&lt;?php echo uc_words($row{'name']); ?&gt;</td>
        <td>&lt;?php echo htmlentities($row['comment']); ?&gt;</td>
    </tr>
&lt;?php endif; ?&gt;

Doesn't look any messier to me...

[eluser]n0xie[/eluser]
There are several advantages to using bigtony's solution:

1. You only loop over the recordset once instead of twice
2. The data doesn't change which makes it easier to reuse (since uc_words is just representation logic it shouldn't be in the model or controller imho)

Then again you're free to do what works for you ;-)

[eluser]Madmartigan1[/eluser]
[quote author="n0xie" date="1253375654"]

1. You only loop over the recordset once instead of twice
2. The data doesn't change which makes it easier to reuse (since uc_words is just representation logic it shouldn't be in the model or controller imho)[/quote]

Yes, looping through everything twice and reassigning the array to a new variable does suck, but the idea behind my original post was getting the data formatted before it is sent to the view, in order to use the template parser class. This is particular to my situation perhaps: my boss, who doesn't know anything about php, needs to be able to edit my view files. The cleaner they are, the better. I'm not suggesting that this is best practice.

I’d like to be able to use the parser class, which requires result_array() for database query results. It would be great if my boss, who knows nothing about php, could edit my view files without screwing them up.

Your second point is right on the money. Sometimes I need to use a htmlentities() formatted value and the raw data at the same time, maybe in a form. A workaround for this with the method I used above (and remember, I'm using the template parser class) is to assign a new key/value pair to the array.

Thanks guys.

[eluser]brianw1975[/eluser]
What I did when I ended up in this same situation with having to format db results yet not wanting to clutter up my views or controller -- I made a "Transformer" library with various functions to "transform" the data and return it and then send it to the view.

Yes, you still have the "clutter", but at least its all centralized and reusable.

In code you provided above, I would have a function like so:

Code:

class Transformer{

function Transformer(){
//enter log entry for library/helper being loaded -- forget the function args
}
/**
*
* called via Transformer::articleComments($result);
*
*/
function articleComments($data){
foreach($data as $row)
{
    $row['name']       = uc_words($row['name']);//example
    $row['comment']    = htmlentities($row['comment']);//example
    $return['my_data'][] = $row;
}

return $return;
}

or you could leave your foreach in place and simply modify the code to pass a single result to be transformed

Code:

foreach($result as $row)
{
    $data['my_data'][] = Transformer::articleComments($row);
}

but mostly these suggestions are just "smarter" refactoring of your original code...

[eluser]Madmartigan1[/eluser]
brianw1975, that's not a bad idea. Most of the formatting I usually do is of only a few different types, but it's typically somewhat extensive. Example: For some reason, people like to submit forms with the caps lock key on... so for proper names I almost always trim, strtolower, and a ucwords variant before they go to the view file. (Just an example, I know I can format that before the db insert)

I'm just using a helper file currently, but maybe it should be moved to it's own class. Thanks for the code examples and your time.

[eluser]Jondolar[/eluser]
You may want to put the data into the database in the same way that you want to display it. That way you won't have to uc_words() or htmlentities() every time you display it. Might not work for you depending on what else you do with the data.

Also, I too like to format the data prior to sending it to the view for the same reason as you. Others that modify the view file (html code) don't want to mess with any php functions at all.