CodeIgniter MySQL Database MetaData

Occasionally it is necessary to obtain information about the table in a db schema to perform dynamic operations on the data. For example: if trying to save to a numeric db field, automatically validate to make sure the data being received is numeric. I ran across 2 items that I wanted to be able to get the information on for a column, but they were not available in the CI Meta Field Data.

The 2 pieces of information that I really needed to know were:

1. Is this an auto incremented field?

2. Does this field allow NULL?

I extended the CI_Model with MY_Model and added this method:

    protected function getTableMetaData($table) {


            //Replaced the default CI field_data so that we could determine if the field is a auto_increment

             /*
                Example object in the MetaData

                   stdClass::__set_state(array(
                 'Field' => 'coupon_no',
                 'Type' => 'bigint(20)',
                 'Null' => 'NO',
                 'Key' => 'PRI',
                 'Default' => NULL,
                 'Extra' => 'auto_increment',
              ))*/

    $fields = $this -> db -> query('DESCRIBE ' . $table) -> result();
            $md = array();
            foreach($fields as $field) {
                preg_match('/([a-zA-Z]+)((d+))?/', $field->Type, $matches);

                $type = (array_key_exists(1, $matches)) ? $matches[1] : NULL;
                $length = (array_key_exists(2, $matches)) ? preg_replace('/[^d]/', '', $matches[2]) : NULL;

                $F              = new stdClass();
                $F->name        = $field->Field;
                $F->type        = $type;
                $F->default     = $field->Default;
                $F->max_length  = $length;
                $F->primary_key = ( $field->Key == 'PRI' ? 1 : 0 );
                $F->auto_increment = strripos($field -> Extra, 'auto_increment') !== FALSE ? 1 : 0; 
                $F->allow_null = $field->Null === 'YES' ? TRUE : FALSE;

                $md[] = $F;
            }




        return $md;

    }

The 2 new properties on the field data are auto_increment which is a boolean and allow_null which is also a boolean. This is MySQL specific, so it would have to be tweaked for other DB’s.