Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Make mysql_fetch_assoc automatically detect return data types?

When using mysql_fetch_assoc in PHP, how can I make it return the correct data types? Right now it appears to convert everything to strings, I'd prefer if it left the Ints as Ints, and somehow designated the Date/Time as either Object or somehow different than strings.

The reason for this is that I am using PHP as a backend to a Flex application, and Flex has some features such as automatically detecting return types, which don't work that well if everything comes in as a string.

like image 853
davr Avatar asked Jun 01 '09 19:06

davr


People also ask

What will mysql_fetch_assoc () return?

Description ¶ Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead. mysql_fetch_assoc() is equivalent to calling mysql_fetch_array() with MYSQL_ASSOC for the optional second parameter. It only returns an associative array.

What is difference between mysql_fetch_array and mysql_fetch_assoc?

Difference: mysql_fetch_assoc() will always assing a non-secuencial key (like "color" and not a number). mysql_fetch_array() will assing a number key if there are not "word" key (0 and not "color" if there are not "color") but, you can choose to assing of key with a parameter...

Which function takes an associative array of field names and values and returns the entire query string?

The fetch_assoc() / mysqli_fetch_assoc() function fetches a result row as an associative array. Note: Fieldnames returned from this function are case-sensitive.

How does mysql_fetch_assoc work?

The mysql_fetch_assoc() function returns a row from a recordset as an associative array. This function gets a row from the mysql_query() function and returns an array on success, or FALSE on failure or when there are no more rows.


2 Answers

I think a good strategy here is to programatically determine the datatype of each column in a table, and cast the returned results accordingly. This will allow you to interact with your database in a more consistent and simple manner while still giving you the control you need to have your variables storing the correct datatype.

One possible solution: You could use mysql_fetch_field() to get an object that holds meta-data about the table column and then cast your string back to the desired type.

//run query and get field information about the row in the table
$meta = mysql_fetch_field($result, $i);

//get the field type of the current column
$fieldType = $meta->type

A full example can be found here: http://us2.php.net/manual/en/function.mysql-fetch-field.php

Since PHP is loosely typed, you should have a relatively easy time with this.

If you are using OO (object-oriented) techniques, you could create a class with this functionality in the setter() methods so you don't have to have duplicate code.

like image 191
Robert Greiner Avatar answered Sep 22 '22 11:09

Robert Greiner


Just contributing a small improvement to mastermind202's answer to handle more data types. Thanks mastermind for doing the heavy lifting!

function cast_query_results($rs) {
    $fields = mysqli_fetch_fields($rs);
    $data = array();
    $types = array();
    foreach($fields as $field) {
        switch($field->type) {
            case MYSQLI_TYPE_NULL:
                $types[$field->name] = 'null';
                break;
            case MYSQLI_TYPE_BIT:
                $types[$field->name] = 'boolean';
                break;
            case MYSQLI_TYPE_TINY:
            case MYSQLI_TYPE_SHORT:
            case MYSQLI_TYPE_LONG:
            case MYSQLI_TYPE_INT24:
            case MYSQLI_TYPE_LONGLONG:
                $types[$field->name] = 'int';
                break;
            case MYSQLI_TYPE_FLOAT:
            case MYSQLI_TYPE_DOUBLE:
                $types[$field->name] = 'float';
                break;
            default:
                $types[$field->name] = 'string';
                break;
        }
    }
    while($row=mysqli_fetch_assoc($rs)) array_push($data,$row);
    for($i=0;$i<count($data);$i++) {
        foreach($types as $name => $type) {
            settype($data[$i][$name], $type);
        }
    }
    return $data;
}   

Example usage:

$db = mysqli_connect(...);
$rs = mysqli_query($db, "SELECT ...");
$results = cast_query_results($rs);

Returns an associative array of rows with properly typed fields

like image 41
Craig Smedley Avatar answered Sep 19 '22 11:09

Craig Smedley