Matching database content types to PHP types

0 Views

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?

Read More
$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
    }
}
Post Views: 0

Related posts

Leave a Reply

1 comment

  1. 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!