Matching database content types to PHP types

Question

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
    }
}

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
– Rarst, Commented Jan 13, 2012 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
– Geert, Commented Jan 13, 2012 at 15:09

Community · Accepted Answer · 2023-11-17 20:38:18Z

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 https://www.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!

Stack Exchange Network

Matching database content types to PHP types

1 Answer 1

Your Answer

Linked

Hot Network Questions

Matching database content types to PHP types

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions