kenzabukuro on Twittter

Cleaning up and formatting your database query results in CodeIgniter

This is actually an old post that was on my old blog. But people have been searching for it a lot and now they can't find it, so I'm reposting it. For the record, I don't think anyone should be doing new sites in CodeIgniter 2.x, but I know lots of people do (there are definitely worse options out there).

Let's take a very simple model:

    public function get_robots($type){
        $this->db->select('id, name, type, hometown, website');
        $this->db->from('robots');
        $this->db->where('type', $type);
        $this->db->order_by('name');
        $query = $this->db->get();
    ...

At this point we have our query data stored in the $query array. Now we can clean up the fields that need enforced formatting. Maybe the input for the "hometown" is irregular and the input for the "website" sometimes has http:// and sometimes doesn't. No problem. Make a new array, clean up the values you want, assign these values to the new array, and return that new array to the controller:

    ...
        $rows = array();
        foreach($query->result_array() as $row)
        {
            $row['hometown'] = trim(strip_tags($row['hometown']));
            $row['website'] = (substr($row['website'],0,7)=='http://' ? $row['website'] : 'http://'.$row['website']);
            $rows[] = $row;
        }

        return $rows;
    }

And there you have it. The examples here are simple, and in production I would build the cleanup processes as functions in a utilities library, but that's the basics. The full method looks like this:

    public function get_robots($type){

        ## Gets all robots of the specified type
        ## Returns an array of formatted results

        $this->db->select('id, name, type, hometown, website');
        $this->db->from('robots');
        $this->db->where('type', $type);
        $this->db->order_by('name');
        $query = $this->content_db->get();

        $rows = array();
        foreach($query->result_array() as $row){
            $row['hometown'] = trim(strip_tags($row['hometown']));
            $row['website'] = (substr($row['website'],0,7)=='http://' ? $row['website'] : 'http://www.sitename.com'.$row['website']);
            $rows[] = $row;
          }
        return $rows;
    }