Original data types in Active Record query result?

[eluser]Samuel Wan[/eluser]
I'm new to Code Igniter and PHP, and ran into the problem of Active Record returning all record properties as string types. After some research, I think I've figured out that PHP database queries always return results as strings, regardless of the table column's original data type. This seems like a terrible inconvenience.

Are there any best practices and/or libraries that enable a query to retrieve records with properties in their original data types?

Thanks in advance for any tips!

[eluser]jedd[/eluser]
Hi Samuel,

Ya know, I'd never even noticed this before - but just did some tests with AR as well as the 'normal' CI query calls, and you're absolutely right.

What inconvenience are you experiencing with this? Is it just breaking on strict code structures such as: === ?

I guess in your models you could write some smarts that cast the data as it comes out of the DB, but that's pretty messy no matter which way you did it (dynamic table analysis or hard-coded field types).

[eluser]Samuel Wan[/eluser]
At first, I implemented a content revisioning feature, and didn't have any problems incrementing a revision_number column of type int, probably because PHP casts the Active Record's string value to int for arithmetic operations.

E.g.

Code:

$new_revision_number = $row->revision_number + 1; // no problem

However,the big scenario where this breaks down is JSON-encoded responses. Most of my controller actions are AJAX web service methods via that return JSON-encoded records in response to HTTP-POST requests. Security issues aside, my original plan was to write simple web service controllers like so:

Code:

class SomeController() {
...
function getLastRevisionNumber($id) {
   ...load model, run query...
   echo json_encode($query->row());
}
...
}

Instead of the expected result:

Code:

[1,2,3,4,5]

I get this:

Code:

["1","2","3","4","5"];

Unfortunately, I might need to move away from Code Igniter (or PHP entirely) because this scenario goes from very simple to very painful if I need to write hand-crafted transformations back to the original database column type for every requested record.

Are there any ORMs compatible with Code Igniter that might return original types? I'm new to PHP but I don't mind wrapping other frameworks into Libraries as I've already learned how to do that for PEAR.

[eluser]jedd[/eluser]
Ah, yes, I can see how that'd be painful.

I just tried mysqli - I think it's a less favoured, possibly soon-to-be-deprecated MySQL library (?) - but it also returns my ID columns as type string, so no joy there.

I suspect you may have to cast these, in your model, as you pull the data in - perhaps an 'is_numeric()' style call against every field that you care about, on the way in, and if true then cast to int - may be workable. No idea on the performance hit you'll suffer, though I wouldn't expect it'd be huge. But there's also the added gratuitous complexity in your models.

[eluser]Samuel Wan[/eluser]
Thanks for the tip on an is_numeric check. I dug into the ActiveRecord metadata functions and followed the code to the system/database/drivers/mysql/mysql_result.php file. Starting at line 84, there's a function called field_data() which loads column metadata into an stdClass object using the mysql_fetch_field function. Code Igniter does not use an available meta-data attribute called $field->numeric, which returns a 1 if numeric and 0 if not.

http://us.php.net/mysql_fetch_field

This isn't something I have time to do today, but for anyone else who comes across this problem, you can probably enable json_encode to output numbers from active records with the following steps:

1) Modify the mysql/mysql_resultphp file to include the $field->numeric metadata attribute.
2) Add an extra step to active record queries that uses Code Igniter's $this->db->field_data() to test for any fields with numeric==1
3) If numeric==1, cast that field's value to a number.

Code:

function field_data()
    {
        $retval = array();
        while ($field = mysql_fetch_field($this->result_id))
        {    
            $F                = new stdClass();
            $F->name         = $field->name;
            $F->type         = $field->type;
            $F->default        = $field->def;
            $F->max_length    = $field->max_length;
            $F->primary_key = $field->primary_key;
                        $F->numeric = $field->numeric; // <!-- ADD THIS LINE
            
            $retval[] = $F;
        }
        
        return $retval;
    }

Heavy reliance on JSON in my scenario might make the extra effort worthwhile, but most people might never run into this.

[eluser]jedd[/eluser]
Hey, excellent work - and looks like a great solution. If you go down this path, might be worth filing a feature-enhancement / big report in CI for this?

Two notes worth making - ctype_digit() is slightly faster than is_numeric() - BUT only works against strings. This is okay so long as the DB is only pulling strings in, but ctype_digit(27), for example, returns false while ctype_digit('27') returns true. I know you're not using this here, but in case others stumble across this thread and approach the problem differently.

Second point - MySQL has some numeric fields that surpass PHP5's integer handling capability (well, raw PHP - there are some arbitrary-length math plugins you can use to expand this). Specifically BIGINT is not handled in PHP5 (but will, I think, be covered by the new data type of INT64 (?) in PHP6). This is especially painful if you're going to be pulling in ID's from columns created with BIGINT (this includes SERIAL).

[eluser]Samuel Wan[/eluser]
Hi Jedd,

Thanks, I did not know those details about PHP string and int type conversions. Sounds like I might be digging into a deeper issue than I'd hoped to solve. For the purposes of making progress with my project, I ended up writing a helper that gives you the option of specifying fields and types to cast on any key-value array returned from a db query.

It's a blunt way to solve the problem, but "good enough" for me. The function comments are based on PHP's settype() documentation at http://us2.php.net/manual/en/function.settype.php

Code:

<?php
/**
  * Casts specified fields of an active record array to specified types.
  * Possibles values of type  are:
  * "boolean" (or, since PHP 4.2.0, "bool")
  * "integer" (or, since PHP 4.2.0, "int")
  * "float" (only possible since PHP 4.2.0, for older versions use the deprecated variant "double")
  * "string"
  * "array"
  * "object"
  * "null" (since PHP 4.2.0)
  * @param array $record Array from an active record
  * @param array $fieldTypes Array where key=property name, value=type to cast.
  * @return array Returns modified active record array with cast properties
*/
function cast_fieldtypes($record, $fieldTypes) {
    foreach($record as $fieldName => $value) {
        if(isset($fieldTypes[$fieldName])) {
            $type = $fieldTypes[$fieldName];
            $value = $record[$fieldName];
            switch ($type) {
                case 'boolean':
                case 'bool':
                    $value = (bool) $value;
                    break;
                
                case 'integer':
                case 'int':
                    $value = (int) $value;
                    break;

                case 'float':
                    $value = (float) $value;
                    break;
                    
                case 'string':
                    $value = (string) $value;
                    break;

                default:
                    break;
            }
            $record[$fieldName] = $value;
        }
    }
    return $record;
}
?>

Here's an example of how to use it:

Code:

// Declare the types for some of the fields (not necessarily all of them)
$fieldTypes = array(
    'weight'=>'int',
    'revision'=>'int',
    'is_visible'=>'bool',
);
$record = $this->db->get_where('content', array('id'=>1));
$casted_record = cast_fieldtypes($record, $fieldTypes);
echo json_encode($casted_record);