PHP: How to cast properties of mysqli_result->fetch_object() based on MySQL type?

Question

I am trying to cast the properties of mysqli_result->fetch_object() based on types defined in MySQL.

For example, if my id column is bigint(20) in MySQL, I would like mysqli_result->fetch_object()->id to be an integer, not a string.

According to the docs, this method returns an object of string properties. Is it possible to get the data types?

"to be an integer, not a string" --- php doesn't support that big *integers* — zerkms, Nov 18 '13 at 23:00
This sounds like much more trouble than it's worth - especially seeing as PHP is a weakly typed language anyway so it doesn't really matter. What if a `bigint` is [larger than PHP's `int` range?](http://stackoverflow.com/questions/670662/whats-the-maximum-size-for-an-int-in-php) The same goes for floats... and what about weird mySQL stuff like spatial data? — Pekka, Nov 18 '13 at 23:00
and it should've been our first question: why do you need that? — zerkms, Nov 18 '13 at 23:01
I'm writing a PHP script that maps data from MySQL to MongoDB, so casting the correct data types is important. — Shaun Scovil, Nov 18 '13 at 23:27
@Shaun OK, but then one shouldn't rely on PHP's data types (whose range is *much* more limited than mySQL's), but get the real datatype info as is shown in the accepted answer. I assume that's what you're going to do though — Pekka, Nov 19 '13 at 00:35

score 8 · Accepted Answer · edited May 23 '17 at 11:50

You can do this to get detailed meta-data info on your result set: $mysqli->fetch_field_direct().

example from the page:

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = "SELECT Name, SurfaceArea from Country ORDER BY Name LIMIT 5";

if ($result = $mysqli->query($query)) {

    /* Get field information for column 'SurfaceArea' */
    $finfo = $result->fetch_field_direct(1);

    printf("Name:     %s\n", $finfo->name);
    printf("Table:    %s\n", $finfo->table);
    printf("max. Len: %d\n", $finfo->max_length);
    printf("Flags:    %d\n", $finfo->flags);
    printf("Type:     %d\n", $finfo->type);

    $result->close();
}

/* close connection */
$mysqli->close();
?>

And these are the enum values for the type:

enum_field_types {
   MYSQL_TYPE_DECIMAL,
   MYSQL_TYPE_TINY,
   MYSQL_TYPE_SHORT,
   MYSQL_TYPE_LONG,
   MYSQL_TYPE_FLOAT,
   MYSQL_TYPE_DOUBLE,
   MYSQL_TYPE_NULL,
   MYSQL_TYPE_TIMESTAMP,
   MYSQL_TYPE_LONGLONG,
   MYSQL_TYPE_INT24,
   MYSQL_TYPE_DATE, 
   MYSQL_TYPE_TIME,
   MYSQL_TYPE_DATETIME, 
   MYSQL_TYPE_YEAR,
   MYSQL_TYPE_NEWDATE, 
   MYSQL_TYPE_VARCHAR,
   MYSQL_TYPE_BIT,
   MYSQL_TYPE_NEWDECIMAL=246,
   MYSQL_TYPE_ENUM=247,
   MYSQL_TYPE_SET=248,
   MYSQL_TYPE_TINY_BLOB=249,
   MYSQL_TYPE_MEDIUM_BLOB=250,
   MYSQL_TYPE_LONG_BLOB=251,
   MYSQL_TYPE_BLOB=252,
   MYSQL_TYPE_VAR_STRING=253,
   MYSQL_TYPE_STRING=254,
   MYSQL_TYPE_GEOMETRY=255
};

This is a really detailed answer I wrote about this topic: https://stackoverflow.com/a/19761805/623952 - it shows you how to merge this with the results of a query. The OP for that question was Spanish (I think?) so the row set was called $proceso (instead of the normal "$row") and their query included a limit 1, so take that into consideration.

Here is a snippet... but you should go see the whole answer because I included a lot of extra details and information.

<?php
$mysqli = mysqli_connect("localhost", "root", "", "test");

// this came from http://php.net/manual/en/mysqli-result.fetch-field-direct.php 
$mysql_data_type_hash = array(
    1=>'tinyint',
    2=>'smallint',
    3=>'int',
    4=>'float',
    5=>'double',
    7=>'timestamp',
    8=>'bigint',
    9=>'mediumint',
    10=>'date',
    11=>'time',
    12=>'datetime',
    13=>'year',
    16=>'bit',
    //252 is currently mapped to all text and blob types (MySQL 5.0.51a)
    253=>'varchar',
    254=>'char',
    246=>'decimal'
);

// run the query... 
$result = $mysqli->query("select * from user limit 1"); 

// get one row of data from the query results 
$proceso = mysqli_fetch_assoc($result);

print "<table>
        <tr>
           <th>\$key</th>
           <th>\$value</th>
           <th>\$datatype</th>
           <th>\$dt_str</th>
        </tr>  ";

// to count columns for fetch_field_direct()
$count = 0; 

// foreach column in that row...
foreach ($proceso as $key => $value) 
{
  $datatype = $result->fetch_field_direct($count)->type;  
  $dt_str   = $mysql_data_type_hash[$datatype];
  $value    = (empty($value)) ? 'null' : $value;  

  print "<tr>
           <td>$key</td>
           <td>$value</td>
           <td class='right'>$datatype</td>
           <td>$dt_str</td>
         </tr>  ";  
  $count++; 
} 

print "</table>"; 

mysqli_close($mysqli);
?>

Awesome, thank you! This definitely looks like it will put my on the right track. Cheers! — Shaun Scovil, Nov 18 '13 at 23:30

score 1 · Answer 2 · answered Nov 18 '13 at 23:01

Typically, this sort of assignment is done using an Object Relational Mapper (ORM). These typically allow you to supply metadata about your columns that can be used for setting types for returned ("mapped") values. You can implement something. You can implement something similar yourself by utilizing the class_name parameter of the fetch_object method and implementing such casting in the setters for the specified class.

Alternatively, some ORM's make use of MySQL metadata from INFORMATION_SCHEMA and thus provide a more dynamic (though arguably less performant) way of making such mappings.

score 0 · Answer 3 · answered Nov 18 '13 at 23:00

0

Yes, this is possible, although it does take a detour to get it.

The MySQL INFORMATION_SCHEMA contains all information regarding tables, columns, data types, keys, indexes, etc.

answered Nov 18 '13 at 23:00

Sherlock

7,525
6
38
79

True, but this isn't really what the OP is asking for – Pekka Nov 18 '13 at 23:01

PHP: How to cast properties of mysqli_result->fetch_object() based on MySQL type?

3 Answers3