Home » Database » DB » Bug #7168

[2006-03-21 03:45 UTC] patrick at 5etdemi dot com

Description:
------------
The mysql_fetch_* functions always return strings regardless of the column type. While this won't cause any issue 99% of the time, when it comes time to automatically serialize into another format to exchange with another language that is more strict about numbers/strings, then it can become an issue on the other side.

Amfphp is an open-source project which allows communication between PHP and Flash, and Flash does make a difference between strings and numbers, and then when it receives on its side, if the source is MySQL, it can't sort data properly or add and such on number columns. Amfphp uses a mysql adaptor to catch number columns from mysql and transform the type before it reaches Flash. Pretty neat. The code is pasted below. The trouble is that if the PEAR::DB getAll function is used, then what amfphp sees is a plain array instead of a resultset, and therefore it can't retrieve the column information, and number columns are returned as strings.

The solution would be to modify DB/mysql.php, the fetchInto method so that it correctly types number columns. I would suggest to have this as a compatibility option, something like DB_PORTABILITY_NUMBERS_AS_NUMBERS. Sample code:

From amfphp:

$fieldcount = mysql_num_fields($d);
$isint = array();
for($i = 0; $i < $fieldcount; $i++) {
$this->columnNames[$i] = $this->_charsetHandler->transliterate(mysql_field_name($d, $i));

//mysql_fetch_* usually returns only strings,
//hack it into submission
$type = mysql_field_type($d, $i);
$isint[] = in_array($type, array('int', 'real', 'year'));
}

while ($line = mysql_fetch_row($d)) {
$to = count($line);
for($i = 0; $i < $to; $i++)
{
if($isint[$i]) //type as number
{
$line[$i] = $line[$i] + 0;
}
}

[2006-03-21 03:55 UTC] patrick at 5etdemi dot com

I forgot to mention: the reason why you can't send "number-looking" strings as numbers using is_numeric is that then you'll get the reverse problem, that is, some things that look like numbers but are really strings will get sent as numbers. For example, if you have a varchar(255) column called book_title with a row with the value '1984' and you send that as a number to Flash, then if you do something like book_title + ', by ' + book_author in Flash you will receive NaN (not a number). Flash unfortunately has the same operator for addition and string concatenation.