The only problem I have with using things like [man]is_numeric/man is that you're basically taking data and saying "Gee, this looks like an integer... let's call it an integer!" when in fact you may be wrong. Why is casting '42' to the integer 42 'correct' ? What if my PHPBuilder username was the string '42' ? Usernames aren't normally integers, so why would it be correct to cast my username to an integer?
Take this table for example:
CREATE TABLE myTest (
`integer` INT,
`varchar` VARCHAR(2),
`float` FLOAT,
`decimal` DECIMAL(4,2)
);
INSERT INTO myTest VALUES (12, '34', 5.6, 7.8);
As you've noted, a simple fetch_assoc() will yield you with:
array(4) {
["integer"]=>
string(2) "12"
["varchar"]=>
string(2) "34"
["float"]=>
string(3) "5.6"
["decimal"]=>
string(4) "7.80"
}
However, using this code:
function cast_mysqli_row($result) {
$fields = mysqli_fetch_fields($result);
$row = $result->fetch_assoc();
$field_num = 0;
foreach($row as &$col)
switch($fields[$field_num++]->type) {
case MYSQLI_TYPE_DECIMAL:
case MYSQLI_TYPE_NEWDECIMAL:
case MYSQLI_TYPE_FLOAT:
case MYSQLI_TYPE_DOUBLE:
case MYSQLI_TYPE_LONGLONG:
case MYSQLI_TYPE_INT24:
$col = (float)$col;
break;
case MYSQLI_TYPE_BIT:
case MYSQLI_TYPE_TINY:
case MYSQLI_TYPE_SHORT:
case MYSQLI_TYPE_LONG:
case MYSQLI_TYPE_DOUBLE:
$col = (int)$col;
break;
case MYSQLI_TYPE_NULL:
$col = NULL;
break;
case MYSQLI_TYPE_TIMESTAMP:
case MYSQLI_TYPE_DATE:
case MYSQLI_TYPE_TIME:
case MYSQLI_TYPE_DATETIME:
case MYSQLI_TYPE_YEAR:
case MYSQLI_TYPE_NEWDATE:
$col = (string)$col; // could be fancy and use a DateTime object?
break;
case MYSQLI_TYPE_INTERVAL:
continue; // ??? - could maybe use a DateInterval object?
break;
case MYSQLI_TYPE_ENUM:
case MYSQLI_TYPE_SET:
continue; // ???
break;
case MYSQLI_TYPE_TINY_BLOB:
case MYSQLI_TYPE_MEDIUM_BLOB:
case MYSQLI_TYPE_LONG_BLOB:
case MYSQLI_TYPE_BLOB:
continue; // leave binary data as-is
break;
case MYSQLI_TYPE_VAR_STRING:
case MYSQLI_TYPE_STRING:
$col = (string)$col;
break;
case MYSQLI_TYPE_GEOMETRY:
continue; // ???
break;
}
return $row;
}
$result = $sql->query('SELECT * FROM myTest');
$row = cast_mysqli_row($result);
var_dump($row);
yields these results:
array(4) {
["integer"]=>
int(12)
["varchar"]=>
string(2) "34"
["float"]=>
float(5.6)
["decimal"]=>
float(7.8)
}
Note that there are a couple of cases where you're going to shoot yourself in the foot with the above method because MySQL can handle larger values than PHP (take an UNSIGNED BIGINT, for example). In such cases, you could perhaps modify the above code to first check to see if the (int) or (float) version matches the original data if casted back into a string (so you go from string, to int/float, back to string); if it doesn't, then you know PHP changed the data when casting to a numeric type ("changed" == "wrapped around due to overflow") and could then opt to discard your int/float version and keep the original string representation returned by the fetch_assoc().
EDIT: Also note the possibilities of being fancy about your casting and using things such as PHP's DateTime class (manual page: [man]class.datetime[/man]) for the various date-related column types.
EDIT2: Another note about the lossy process of type casting; since PHP doesn't have an equivalent "DECIMAL" type, note that you could very well be re-introducing small errors in arithmetic due to the nature of floating-point numbers (e.g. the very reason you'd want to use a DECIMAL type in SQL to begin with).
