MySQL / PHP INT values

[Itshim]

When querying data out of MySQL into PHP, all values from MySQL arrive in PHP as strings, even if the MySQL column type is INT. This is documented behavior, but I was wondering if someone could explain why this is the case.

The data type returned by MySQL corresponds to the column type, so why doesn't PHP simply return the values as is? I know PHP is a loosely typed language, but I do not see the reason for the conversion?

If someone knows a configuration change to stop this behavior let me know.

Thanks,
Itshim

 

[vacunita]

They don't really arrive as strings. Its just that PHP is flexible enough that it lets you manipulate them as if they were strings or any other type of variable.

But you can still treat them as integers and do operations on them normally.

$myinteger="25";

$myotherinteger="10";

You can perfectly well do:

$result=$myinteger+$myotherinteger;
echo $result; //35.

But you can also treat them as strings and:

$otheresult=$myinteger . $myotherinteger; //2510


Or even arrays:
$thirdresult=$myinteger[0] + $myotherinteger[0] // 3;

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[Itshim]

Actually, I believe they are returned as strings, because using the comparison '===' will fail when comparing the returned value to an integer.

The documentation of mysql_fetch_array (and other mysql functions) state:

Returns an array of strings that corresponds to the fetched row...

Also if you use var_dump() on a returned value it is labeled as a string. I could be doing something in the code which causes this to occur, but if that is the case I simply cannot find it.

Thanks

 

[vacunita]

Again, they are labeled as strings, but operations, can still be performed on them as long as the value is numerical

If you return numbers, you can add them, subtract them, multiply them etc.

If they are strings, you can concatenate them. Or do whatever.

For PHP variables type depends on its content, and its context.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[Itshim]

I understand that, my question is why label them as strings when they are coming into PHP as integers? If they were labeled as integers you could still do everything you listed because like you said: PHP variables type depends on its content, and its context.

I completely understand in the normal execution of PHP this will not be a factor unless you use the comparison operator 'identical', I was just looking for the logic behind labeling them as strings.

 

[vacunita]

The logic behind it I don't know. I guess they could have said an "Returns and array of values..." or something to that effect, instead of specifying them as strings, as it really has no bearing on the contents of the row.

Maybe someone else can shed some light on the reasons behind returning them as strings, or if its really just a naming convention.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[jpadie]

it's not a naming convention - having tested with other extensions (like PDO) i see the same behaviour: all values are returned as strings.

given the loosely typed nature of php, I would agree with vacunita that the behaviour represents a sensible design decision by either zend or the mysql API designers. i wonder where the behavious actually takes place? in the API or the kernel?

 

[Itshim]

I have done a little more research into this and found that it actually happens in the MySQL C API.

The documentation I found on mysql_fetch_row() (in the C API) states:

Returns a pointer to the next row of the result set, represented as an array of strings (except that NULL column values are represented as NULL pointers)...

Values for all data types, even numeric types, are returned as strings. If you want to perform a numeric calculation with a value, you must convert it yourself...

Now PHP does the conversion for you, but I am actually more shocked that it happens in the C because it is not a loosely typed language.

I found the documentation for mysql_fetch_row in the C API on the MySQL site, but it does not mention this aspect of the function.

MySQL-Paul DuBois - link to the book where the above quote was taken from.(Amazon)

I will post more information on the logic, if I can find it.

 

[vacunita]

I suppose the creators of the API decided it would be easier to have everything as strings, then to try to preserve the data types.

As DB's progress, more data types are added to them. It would in my mind be a very difficult chore to be updating the API's constantly to return the new column types. And sometimes, returning some data types might be to difficult to do. Or Maybe the Language used to process the results, has no way of handling a specific data type, so they chose to return everything as a string, and have the user, set the types manually afterwards.

I suppoose. Maybe it was just arbitrary.







----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[Itshim]

vacunita, I had not thought about new data types being added as DBs mature, and I think you make a very valid point. Since I would assume all values can be represented as strings (except for NULL of course) it would definitely make more sense to have a stable extension instead of trying to update every time a type definition is changed.

I did find that mysql_fetch_row() (in C) uses the data type MYSQL_ROW which is defined as

This is a type-safe representation of one row of data. It is currently implemented as an array of counted byte strings. (You cannot treat these as null-terminated strings if field values may contain binary data, because such values may contain null bytes internally.)

I tried to do some research on what is meant by 'a type-safe representation' and could only find this. I am unfamiliar with the C language and the more I read the more I thought that this did not contain the answers I was looking for.

Thank you for posting your thoughts and providing some possible reasoning.