MySQL integer field is returned as string in PHP

Question

I have a table field in a MySQL database    userid INT 11    So I am calling it to my page with this query    SELECT userid FROM DB WHERE name  john     Then for handling the result I do     row  result- gt fetch assoc      id  row  userid      Now if I do   echo gettype  id     I get a string  Shouldn t this be an integer

User · Answer

Easiest Solution I found   You can force json encode to use actual numbers for values that look like numbers    json encode  data  JSON NUMERIC CHECK      since PHP 5 3 3    Or you could just cast your ID to an int    row    result- gt fetch assoc     id    int   row  userid

User · Answer

If prepared statements are used  the type will be int where appropriate  This code returns an array of rows  where each row is an associative array  Like if fetch assoc   was called for all rows  but with preserved type info   function dbQuery  sql        global  mysqli        stmt    mysqli- gt prepare  sql        stmt- gt execute         stmt- gt store result          meta    stmt- gt result metadata         params   array         row   array         while   field    meta- gt fetch field             params      amp  row  field- gt name              call user func array array  stmt   bind result     params        while   stmt- gt fetch             tmp   array          foreach   row as  key   gt   val             tmp  key     val                 ret      tmp              meta- gt free         stmt- gt close         return  ret

User · Answer

I like mastermind s technique  but the coding can be simpler   function cast query results  result   array       if   result     false        return null        data   array         fields    result- gt fetch fields        while   row    result- gt fetch assoc            foreach   fields as  field             fieldName    field- gt name           fieldValue    row  fieldName           if   is null  fieldValue               switch   field- gt type                  case 3                   row  fieldName     int  fieldValue                  break                case 4                   row  fieldName     float  fieldValue                  break                   Add other type conversions as desired                   Strings are already strings  so don t need to be touched                              array push  data   row              return  data      I also added checking for query returning false rather than a result-set  And checking for a row with a field that has a null value  And if the desired type is a string  I don t waste any time on it - its already a string     I don t bother using this in most php code  I just rely on php s automatic type conversion  But if querying a lot of data  to then perform arithmetic computations  it is sensible to cast to the optimal types up front

User · Answer

No  Regardless of the data type defined in your tables  PHP s MySQL driver always serves row values as strings   You need to cast your ID to an int    row    result- gt fetch assoc     id    int   row  userid

User · Answer

This happens when PDO  ATTR EMULATE PREPARES is set to true on the connection   Careful though  setting it to false disallows the use of parameters more than once  I believe it also affects the quality of the error messages coming back

User · Answer

I like Chad s answer  especially when the query results will be passed on to javascript in a browser  Javascript deals cleanly with numeric like entities as numbers but requires extra work to deal with numeric like entities as strings  i e  must use parseInt or parseFloat on them   Building on Chad s solution I use this and it is often exactly what I need and creates structures that can be JSON encoded for easy dealing with in javascript   while   row    result- gt fetch assoc             convert numeric looking things to numbers for javascript     foreach   row as  amp  val            if  is numeric  val                val    val   0            Adding a numeric string to 0 produces a numeric type in PHP and correctly identifies the type so floating point numbers will not be truncated into integers

User · Answer

You can do this with      mysql fetch field   mysqli result  fetch field direct or PDOStatement  getColumnMeta        depending on the extension you want to use  The first is not recommended because the mysql extension is deprecated  The third is still experimental   The comments at these hyperlinks do a good job of explaining how to set your type from a plain old string to its original type in the database   Some frameworks also abstract this  CodeIgniter provides  this- gt db- gt field data      You could also do guesswork--like looping through your resulting rows and using is numeric   on each  Something like        foreach  result as  amp  row    foreach  row as  amp  value     if is numeric  value        value    int   value                           This would turn anything that looks like a number into one   definitely not perfect

User · Answer

mysqli- gt options MYSQLI OPT INT AND FLOAT NATIVE  TRUE    Try this - worked for me

User · Answer

For mysqlnd only   mysqli options  conn  MYSQLI OPT INT AND FLOAT NATIVE  true    Otherwise     row    result- gt fetch assoc       while   field    result- gt fetch field          switch  true          case  preg match     float double decimal      field- gt type             row  field- gt name     float  row  field- gt name           break        case  preg match     bit  tiny small medium big  int      field- gt type             row  field- gt name     int  row  field- gt name           break

User · Answer

Use the mysqlnd  native driver  for php   If you re on Ubuntu   sudo apt-get install php5-mysqlnd sudo service apache2 restart   If you re on Centos   sudo yum install php-mysqlnd sudo service httpd restart   The native driver returns integer types appropriately   Edit   As  Jeroen has pointed out  this method will only work out-of-the-box for PDO  As  LarsMoelleken has pointed out  this method will work with mysqli if you also set the MYSQLI OPT INT AND FLOAT NATIVE option to true   Example    mysqli   mysqli init     mysqli- gt options MYSQLI OPT INT AND FLOAT NATIVE  TRUE

User · Answer

My solution is to pass the query result  rs and get a assoc array of the casted data as the return   function cast query results  rs         fields   mysqli fetch fields  rs        data   array         types   array        foreach  fields as  field            switch  field- gt type                case 3                   types  field- gt name     int                   break              case 4                   types  field- gt name     float                   break              default                   types  field- gt name     string                   break                      while  row mysqli fetch assoc  rs   array push  data  row       for  i 0  i lt count  data   i              foreach  types as  name   gt   type                settype  data  i   name    type                       return  data      Example usage    dbconn   mysqli connect  localhost   user   passwd   tablename     rs   mysqli query  dbconn   SELECT   FROM Matches     matches   cast query results  rs       matches is now a assoc array of rows properly casted to ints floats strings

User · Answer

In my case mysqlnd so extension had been installed  BUT i hadn t pdo mysqlnd so  So  the problem had been solved by replacing pdo mysql so with pdo mysqlnd so

User · Answer

MySQL has drivers for many other languages  converting data to string  standardizes  data and leaves it up to the user to type-cast values to int or others

User · Answer

In my project I usually use an external function that  filters  data retrieved with mysql fetch assoc   You can rename fields in your table so that is intuitive to understand which data type is stored   For example  you can add a special suffix to each numeric field  if userid is an INT 11  you can rename it userid i or if it is an UNSIGNED INT 11  you can rename userid u  At this point  you can write a simple PHP function that receive as input the associative array  retrieved with mysql fetch assoc   and apply casting to the  value  stored with those special  keys

User · Answer

When you select data from a MySQL database using PHP the datatype will always be converted to a string  You can convert it back to an integer using the following code    id    int   row  userid     Or by using the function intval      id   intval  row  userid

[php] MySQL integer field is returned as string in PHP

Examples related to php

Examples related to mysql

Examples related to types

Examples related to int

Examples related to gettype