Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

The $wpdb query methods return all query results as strings. For example, an 'ID' column would return a string value like '42' instead of an integer 42.

Is there a way to configure the WP database class to make the database content types match to PHP types? Or do I need to continue to do this manually like below?

$results = $wpdb->get_results('SELECT * FROM my_table');

foreach ($results as $result)
{
    // All integer values
    foreach (array('id', 'user_id', 'comment_count') as $field)
    {
        $result->$field = (int) $result->$field;
    }
    // All float values
    foreach (array('price') as $field)
    {
        $result->$field = (float) $result->$field;
    }
    // All boolean values
    foreach (array('is_open', 'is_confirmed') as $field)
    {
        $result->$field = (bool) $result->$field;
    }
    // Etc.
}

Why bother, you may think? Well, for starters, clean code is fun to work with. Integer values should not be stored as strings. When you create a custom function to look up something, a product, for example, I like to be able to differentiate the field to look up based on the data type of the passed argument:

function load_product($id_or_slug)
{
    if (is_int($id_or_slug))
    {
        // Look up by ID
    }
    else
    {
        // Look up by slug
    }
}
share|improve this question
    
Why not just is_numeric()? I gave this some thought and I got few ideas how to convert to proper types, but I can't stop thinking that it would just be a waste of code and resources. –  Rarst Jan 13 '12 at 14:18
    
The string '-1.2e3' is numeric too, but that's not the point. Without the proper types you can't write code that relies on the type. In many/most cases it probably won't matter; it just feels ugly to me. –  Geert Jan 13 '12 at 15:09
add comment

1 Answer

up vote 0 down vote accepted

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!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.