• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to strtolower all query result keys

#1
[eluser]skunkbad[/eluser]
I've been working on a project with many models, and because I am not in complete control of the database, many table fields have uppercase characters. I have been using strtolower on the keys, but would like to implement a solution that extends DB_result.php. I'm tired, so I'm just looking for a little help if anyone has the time. I'd like to ensure that I don't break anything by extending the class, because everything else needs to work the way it should. Any help is appreciated.

#2
[eluser]d1a8lo24[/eluser]
Just use the lower() in MySQL when you're pulling in the data. see the following link to string function on MySQL function.

http://dev.mysql.com/doc/refman/5.0/en/s...tion_lower

#3
[eluser]skunkbad[/eluser]
[quote author="d1a8lo24" date="1310847204"]Just use the lower() in MySQL when you're pulling in the data. see the following link to string function on MySQL function.

http://dev.mysql.com/doc/refman/5.0/en/s...tion_lower[/quote]

That won't work because I don't need to lowercase the field values, I need to lowercase the field names (keys).

For instance:

Code:
$row = array(
   'Field-Name' => 'Value'
);

What I need is for `Field-Name` to be 'field-name', and value should remain Value. The project I'm working on has about 40 tables, and all of the field names are Upper-Cased, which is less convenient for me.

#4
[eluser]BradEstey[/eluser]
Have you tried array_change_key_case()?

And if you have to do it from a SQL query... you can always define your own aliases in the query:

Code:
SELECT
    Field-Name as "field-name"
FROM table

#5
[eluser]skunkbad[/eluser]
[quote author="Bradillac" date="1310857666"]Have you tried array_change_key_case()?

And if you have to do it from a SQL query... you can always define your own aliases in the query:

Code:
SELECT
    Field-Name as "field-name"
FROM table
[/quote]

Yes, array_change_key_case() or strtolower() will both change the case, and it's not that I don't know how to change the case, it's that I want to extend the DB class(es) to make this happen automatically for every query ... forever.

#6
[eluser]BradEstey[/eluser]
I haven't had any luck extending the Database Driver.. but if you want to give it a shot, you could try reading this:
http://codeigniter.com/wiki/Extending_Database_Drivers

Otherwise, the quick & dirty fix is to open system/database/DB_result.php, find the "result_array()" function and change it to:

Code:
function result_array($lowercase = FALSE)
    {        
        
        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);
        while ($row = $this->_fetch_assoc())
        {
            $this->result_array[] = $row;
        }
        
        if ($lowercase) {
            foreach ($this->result_array as $key => $value) {
                if (is_array($value)) {
                    $this->result_array[$key] = array_change_key_case($value);
                }
            }
        }
        
        return $this->result_array;
    }

And then find the function row_array() and change it to:

Code:
function row_array($n = 0, $lowercase = FALSE)
    {
        $result = array_change_key_case($this->result_array());
        if ($lowercase) { $result = array_change_key_case($result); }

        if (count($result) == 0)
        {
            return $result;
        }

        if ($n != $this->current_row AND isset($result[$n]))
        {
            $this->current_row = $n;
        }

        return $result[$this->current_row];
    }


So now if you call: $query->result_array(TRUE); then it will return a multi-row database query with all of the keys set to lowercase. If you call $query->row_array(0, TRUE); then it will return the first row of a query with the keys lowercased.

The only disadvantage of this is that since you're editing the system files, every time you update Codeigniter you'll have to put these hacks back in.

#7
[eluser]jmadsen[/eluser]
I played around with this a bit, and came to same conclusion as Bradillac...

one idea i was playing with was to use the custom_result_object() and pass in your own class - but that didn't seem to get me any further than a different object to foreach over and reset everything. Just mention it in case it gives you any ideas

#8
[eluser]skunkbad[/eluser]
Because I'm accountable for my actions, I can't knowingly modify system files, knowing that somebody else may work on the site later and not be able to figure out what happened when they upgrade CI. I've successfully extended the DB classes before, so I'll look into it tonight and post if I come up with a solution. I've got to drive about 1300 miles in the next 2 days, so I need to get to bed early, but I'm hoping to get this all figured out. Thanks for your help.

#9
[eluser]skunkbad[/eluser]
I figured out how to extend DB_Result. Can't post all three files, so I'll show you them in three posts.

First MY_Loader:
Code:
<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class MY_Loader extends CI_Loader {

    function database($params = '', $return = FALSE, $active_record = NULL)
    {
        // Grab the super object
        $CI =& get_instance();

        // Do we even need to load the database class?
        if (class_exists('CI_DB') AND $return == FALSE AND $active_record == NULL AND isset($CI->db) AND is_object($CI->db))
        {
            return FALSE;
        }

        require_once(BASEPATH.'database/DB'.EXT);

        // Load the DB class
        $db =& DB($params, $active_record);

        $my_driver = config_item('subclass_prefix').'DB_'.$db->dbdriver.'_driver';
        $my_driver_file = APPPATH.'core/'.$my_driver.EXT;

        if (file_exists($my_driver_file))
        {
            require_once($my_driver_file);
            $db = new $my_driver(get_object_vars($db));
        }

        if ($return === TRUE)
        {
            return $db;
        }

        // Initialize the db variable.  Needed to prevent
        // reference errors with some configurations
        $CI->db = '';

        // Load the DB class
        $CI->db = $db;
    }
}

#10
[eluser]skunkbad[/eluser]
Second, MY_DB_mysqli_driver.php

Code:
<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class MY_DB_mysqli_driver extends CI_DB_mysqli_driver {

    function load_rdriver()
    {
        $driver = 'CI_DB_'.$this->dbdriver.'_result';

        if ( ! class_exists($driver))
        {
            include_once(BASEPATH.'database/DB_result'.EXT);
            include_once(BASEPATH.'database/drivers/'.$this->dbdriver.'/'.$this->dbdriver.'_result'.EXT);
        }

        $CI =& get_instance();

        $my_driver = config_item('subclass_prefix').'CI_DB_'.$this->dbdriver.'_result';

        $my_driver_file = APPPATH.'core/'.$my_driver.EXT;

        if (file_exists($my_driver_file))
        {
            require_once($my_driver_file);
            return $my_driver;
        }
        else
        {
            return $driver;
        }
    }
}


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.