My CI Hacks Pt 1

[eluser]vijinho[/eluser]
I've been using CI to develop a new site and working around certain issues I encountered I present some of the hacks I have made.


a) I wanted to get the prepared and compiled SQL statement from my active record commands without actually running it and I wrote this method to do so.

File: system/database/db_active_rec.php

Code:

/**
     * Get The Compiled SQL Statement
     *
     * Compiles the select statement based on the other functions called
     * and runs the query
     *
     * @access  public
     * @param   string  the limit clause
     * @param   string  the offset clause
     * @return  object
     */
    function get_compiled_sql($table = '', $limit = null, $offset = null)
    {
        if ($table != '')
        {
            $this->from($table);
        }

        if ( ! is_null($limit))
        {
            $this->limit($limit, $offset);
        }

        $sql = $this->_compile_select();

        $this->_reset_select();
        return $sql;
    }

b) I needed to get a count of the number of rows only for the current active record query WITHOUT pulling back every single row of the db into an array and then counting that array.

File: system/database/db_active_rec.php

Code:

function get_nrows($table = '', $limit = null, $offset = null)
    {
        if ($table != '')
        {
                $this->from($table);
        }

        if ( ! is_null($limit))
        {
                $this->limit($limit, $offset);
        }

        $sql = $this->_compile_select();
        $this->_reset_select();

        $sql = 'SELECT COUNT(*) AS rows FROM (' . $sql . ') results';

        // return the count value
        $query = $this->query($sql);
        $result = $query->result_array();
        if (is_array($result)) {
            $result = $result[0]['rows'];
        }
        $this->_reset_select();
        return $result;
    }

c) I needed to perform a mysql "SOUNDS LIKE" (SOUNDEX) search so I modified the active record class to have a new clause (copying the like parts and adding new methods and code where necessary) such that we I got something like this. Probably should have been in the mysqli driver than there.

File: system/database/db_active_rec.php

Code:

$this->ar_soundslike[] = $prefix." $k SOUNDS LIKE '{$v}'";

d)
I wanted a bit more flexibility from result_array() so that

a) When getting the result array, I found I was having to loop over the results a 2nd time in order to set the key for the array results, e.g. for id fields which was quite wasteful, so I added an option to specify which field of the database resultset to use as the array key instead

b) I had to process very large tables, some of over 100,000 records so I wanted to be able to loop over the resultset with a db pointer rather than use up all the memory, so I added a 2nd option, $pointeronly which returns just the DB pointer, which allows my controller to then loop over the large result set using

while ($result = $rp->_fetch_assoc())

Where $rp is the returned result pointer

File: system/database/DB_result.php

Code:

/**
     * Query result.  "array" version.
     *
     * @access    public
     * @return    array
     */
    function result_array($indexfield = null, $pointeronly = null)
    {
        if (count($this->result_array) > 0)
        {
            return $this->result_array;
        }

        // In the event that query caching is on the result_id variable
        // will return FALSE since there isn't a valid SQL resource so
        // we'll simply return an empty array.
        if ($this->result_id === FALSE OR $this->num_rows() == 0)
        {
            return array();
        }

        $this->_data_seek(0);
        if (empty($pointeronly)) {
            while ($row = $this->_fetch_assoc())
            {
                if (!empty($indexfield)) {
                    $this->result_array[$row[$indexfield]] = $row;
                } else {
                    $this->result_array[] = $row;
                }
            }
            return $this->result_array;
        } else {
            return $this;
        }
    }

this is continued in next post (exceeded 6000 chars despite the form saying 300 or so remaining for the entire original post!)