Matching database content types to PHP types

There is no way to automatically make wpdb do this. This is really a limitation of PHP that WordPress doesn’t happen to address. wpdb doesn’t have any hooks or actions that would help you either, so the best thing you can do is write your own wrapper function and run your queries through that so as to avoid repeating yourself.

First, you might check out http://us2.php.net/manual/en/function.mysql-fetch-field.php to make your wrapper more dynamic.

Second, wpdb stores this information in wpdb::col_info and can be accessed through wpdb::get_col_info(). This variable gets overridden after each query, so you have to access it immediately. Furthermore, it’s indexed numerically, so you need to know which order your field is in (or loop through it, which is an unnecessary amount of work).

Just shooting from the hip here, but something like…

function get_typed_results($sql, $output = OBJECT) {
    global $wpdb;
    $rows = $wpdb->get_results($sql, ARRAY_N);
    $new_result = array();
    foreach ($rows as $row) {
        $new_row = array();
        foreach ($row as $i => $value) {
            if ( $wpdb->col_info[$i]->numeric ) {
                if ( $wpdb->col_info[$i]->type == 'float' || $wpdb->col_info[$i]->type == 'double' )
                    $new_row[$wpdb->col_info[$i]->name] = (float)$value;
                elseif ( $wpdb->col_info[$i]->type == 'bool' || $wpdb->col_info[$i]->type == 'boolean' )
                    $new_row[$wpdb->col_info[$i]->name] = (bool)$value;
                else
                    $new_row[$wpdb->col_info[$i]->name] = (int)$value;
            }
            elseif ( $wpdb->col_info[$i]->type == 'datetime' )
                $new_row[$wpdb->col_info[$i]->name] = strtotime($value);
            else
                $new_row[$wpdb->col_info[$i]->name] = $value;
        }
        if ($output == ARRAY_A) $new_result[] = $new_row;
        elseif ($output == OBJECT) $new_result[] = (object)$new_row;
        else $new_result[] = array_values($new_row);
    }
    return $new_result;
}

I don’t actually know what the types it returns are, if they’re float or double or whatever, so you’ll want to test it out and fix it as appropriate. Also, I assume booleans are considered numeric, but maybe that’s incorrect — again, test. Good luck!