Changing db results in model before passing

[eluser]timotheus[/eluser]
Hello, I've got an issue I could some assistance with. This is probably more a general php question.

I'm using a model for my database functionality.

Code:

class Dbmojo extends Model {


    function Dbmojo()
    {
         // Call the Model constructor
        parent::Model();
    }

    function get_headlines()
    {
        $sql = "query here";
        $sql = $this->db->query($sql);
        foreach($sql->result_array() as $row)
        {
             if(strlen($row['title'])==50)
             {
             echo "is 50!";
             $row['title']=$row['title']."...";
             }
        }
        return $sql->result_array();
    }

}

I'm getting an attribute named "title", and if it is EXACTLY 50 characters long, I want to add a "..." to the end of it before passing along. This foreach is running properly, and the "is 50!" troubleshooting echo is showing up the exact number of times it should, but it's not modifying the query results before passing it along.

Can anyone point out what I'm doing wrong? Thanks in advance.

[eluser]bretticus[/eluser]
Calling $sql->result_array() only pulls one record at a time (hence, the reason you are using a foreach loop to check titles, etc.) Your example will only return the first record. Another thing to note, When you iterate over $sql, $row is not passed by reference, so working on $row will never alter the result row. If you had some large array of arrays, you could pass $row by reference (with an ampersand), but that's not an option in CodeIgniter, so, this has to be done programmatically (well, it is. You just write the code instead of using CI code.)

Here's what I do (not always but sometimes I guess,) I set an empty array:

Code:

$results = array();

I then iterate over the query using result_array():

Code:

$sql = "query here";
        $sql = $this->db->query($sql);
        foreach($sql->result_array() as $row)
        {
             $results[] = $row;
        }

After that loop, you will have a $results array with all your data. Now you can iterate over that to modify data.

Code:

foreach($results as &$row)
        {
             if(strlen($row['title'])==50)
             {
             echo "is 50!";
             $row['title']=$row['title']."...";
             }
        }

return $results;

Don't forget the ampersand because that's how you pass by reference and that's how your $results array can be modified inside the foreach loop.

You might want to change your code a little though. It's probably very unlikely your titles will be exactly 50 characters long to make this code worhtwhile. May I recommend:

Code:

foreach($results as &$row)
        {
             if(strlen($row['title'])>=50)
             {
             echo "is 50!";
             $row['title']=substr($row['title'],0,50)."...";
             }
        }

return $results;

Just using substr() to force 50 chars for the result.

[eluser]timotheus[/eluser]
@Research Assistant, Thanks for bringing up the ampersand, that was what I kept forgetting. Passing by reference.. so important but I rarely need to do it. Also, durr, I forgot that of course I can return any array I want.

Your solution works, but I don't like the doing double run through the results. This is what I did, and it works. The trick was setting the result_array to a variable, before going into the foreach loop.

Code:

class Dbmojo extends Model {

    function Dbmojo()
    {
         // Call the Model constructor
        parent::Model();
    }

    function get_headlines()
    {
        $sql = "query here";
        $sql = $this->db->query($sql);
        $results = $sql->result_array(); // pass result to var $results
        foreach($results as &$row) // run through $results
        {
            if(strlen($row['title'])==50) $row['title']=$row['title']."...";
        }
        return $results;
    }
}

And in my query I actually do a SUBSTRING(title,1,50) AS title to pull in the title. So I need it to essentially:

1. If the title was less than 50 chars, do nothing.
2. If the title was greater than 50 chars, that means it was chopped off in the query, and I want the "..." to appear.

@Research Assistant, again, thank you for taking the time to reply. It is what pointed me in the right direction!

[eluser]bretticus[/eluser]
It's @bretticus actually ;-)

You're welcome. Using SUBSTRING in the database (assuming you were using MySQL) was a suggestion I was thinking about offering, but was trying to keep it simple. Sorry for not giving you the benefit of the doubt.

I'd check your array result from result_array(). In my experience, you only get one row array back.

[eluser]timotheus[/eluser]
@bretticus Wow, that's embarrassing. :/ Sorry about that.

I've checked this and it is working. Maybe you're thinking of how row_array() works?

From the documentation..

result_array() - This function returns the query result as a pure array, or an empty array when no result is produced. Typically you'll use this in a foreach loop..

row() - This function returns a single result row. If your query has more than one row, it returns only the first row. The result is returned as an object.

row_array() - Identical to the above row() function, except it returns an array.

[eluser]bretticus[/eluser]
Nope. I'm thinking of result_array(). Notice the words in bold

result_array() - This function returns the query result as a pure array, or an empty array when no result is produced. Typically you’ll use this in a foreach loop..

If you are getting back the entire result set as an array than I guess I need to play with that function more.

When working with datasets as arrays in PHP, you typically (more often than not and then some) have an array of arrays. So it makes perfect sense that the documentation states it returns the query result as a pure array. I have long been under the impression that it meant one row at a time. Hence, the documentation that states:

...Typically you’ll use this in a foreach loop.

So yes, make sure you have an array of arrays and not the array for the first record only.

When you that say it works, than I must suppose you have already checked. Interesting...maybe I'm mistaken (wouldn't be the first time.)

[eluser]timotheus[/eluser]
Zesty discussion, I like it!

Here is the result_array() example code from the documentation..

Code:

$query = $this->db->query("YOUR QUERY");

foreach ($query->result_array() as $row)
{
   echo $row['title'];
   echo $row['name'];
   echo $row['body'];
}

The foreach loop is running through a new copy of the query results. When it's done looping through though, that new array disappears. It was only used for the loop.

If you set a variable to hold what result_array() returns, then go through that..

Code:

$query = $this->db->query("YOUR QUERY");

$result = $query->result_array();
foreach ($result as &$row)
{
   $row['title']=$row['title']."edited!";
   $row['name']=$row['name']."edited!";
   $row['body']=$row['body']."edited!";
}

Then $result will have your query results, that were edited.

This works, but maybe my reasoning for why this works is wrong. I appreciate talking through this with you. I want to be sure I fully understand this.

[eluser]BorisK[/eluser]
Consider doing this logic outside of the Model class. Moving it to a controller, view or helper function would make it a better place.

[eluser]timotheus[/eluser]
[quote author="BorisK" date="1285190809"]Consider doing this logic outside of the Model class. Moving it to a controller, view or helper function would make it a better place.[/quote]

Thanks for the input. I am still somewhat new to the MVC framework ideology..

I was under the impression that it's best to keep logic out of views, and keeping controllers as lean as possible. I suppose a helper function could have been appropriate, but I really don't see me having to do this small tweak on anything else; I wouldn't reuse it.

[eluser]bretticus[/eluser]
Actually, @timotheus, you are absolutely correct. Models are exactly the right place to form your application data. Your use is totally recommended. Controllers should be lean (I typically have problems sticking to that rule.) Ideally, your controllers know what the end user wants when a URL is accessed, controllers then know how to get application data (models) and where to send that data to handle the presentation of it to the end-user (views.) Nothing more.