PHP mysqli_fetch_field data type

I need some help tracking down a bit of nitty gritty information on the information in the fetch_field method of a mysqli result object.

Specifically the type property - from the documentation it would seem that this field returns an integer...

Great!

I just can't seem to find开发者_StackOverflow社区 a table that will let me translate the number to it's respective data type. I'm not even sure if I'm looking for php or mysql specific information. Push come to shove I can map it out myself, but I'd much rather if someone can point me to the actual documentation.

What am I missing?

You can compare this number to various predefined constants, which are listed here:

http://www.php.net/manual/en/mysqli.constants.php

e.g. MYSQLI_TYPE_SHORT

The PHP function mysqli_fetch_field() seems to map directly to the MySQL C API function mysql_fetch_field(), which returns a C struct of type MYSQL_FIELD, defined in mysql.h.

The type field of the structure is an enum_field_types, which is defined as follows:

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
};

First I want to say I do recognize the 5 year difference in this answer compared to the accepted answer.

Though I feel the accepted answer does point people in the right direction, and I have found many other answers that point back to this post, I do not feel this is a solid answer to the question, or many others like it, specifically the part that says, "that will let me translate the number to it's respective data type".

For this I set out to bring back a couple functions that php used to have that already handled this for us, mysqli_field_flags($result,$field_offset) and mysqli_field_type($result,$field_offset), though the mysqli_field_flags returns exactly what the old mysql_field_flags would give us (for the most part, let me know if anyone finds something that doesn't work exactly like the old one, and we can update it here so all can get access to it). The second one, mysqli_field_type, only actually returns the type data for the field, unlike mysql_field_type, which would also return the flags in the same string.

Both of these can be grabbed from here, let me know if either have issues. (further, I can not grab full responsibility for this answer, as credit goes to andre at koethur dot de for commenting with most of what we need over at the php.net website), I have just added this direct bit to get our numbers, and adjusted the names to better fit the scheme of things, and since there are no php mysqli_field_flags and mysqli_field_type functions already for mysqli, these work out as perfect backward compatibility functions.

IF THESE HELP YOU OUT PLEASE VOTE UP SO OTHER GET THE MESSAGE, IF OP READS THIS PLEASE CHANGE THE ANSWER ;)

PHP 5.5 mysql_field_type "Backward Compatibility Function", mysqli_field_type (notice the i)

/**
 * Returns a string that represents the mysql field type
 *
 * @param mysqli_resource $result The result resource that is being evaluated. This result comes from a call to mysql_query().
 * @param integer $field_offset The numerical field offset. The field_offset starts at 0. If field_offset does not exist, an error of level E_WARNING is also issued.
 */
function mysqli_field_type( $result , $field_offset ) {
    static $types;

    $type_id = mysqli_fetch_field_direct($result,$field_offset)->type;

    if (!isset($types))
    {
        $types = array();
        $constants = get_defined_constants(true);
        foreach ($constants['mysqli'] as $c => $n) if (preg_match('/^MYSQLI_TYPE_(.*)/', $c, $m)) $types[$n] = $m[1];
    }

    return array_key_exists($type_id, $types)? $types[$type_id] : NULL;
}

PHP 5.5 mysql_field_flags "Backward Compatibility Function", mysqli_field_flags (notice the i)

/**
 * Returns a string that represents the mysql field flags
 *
 * @param mysqli_resource $result The result resource that is being evaluated. This result comes from a call to mysql_query().
 * @param integer $field_offset The numerical field offset. The field_offset starts at 0. If field_offset does not exist, an error of level E_WARNING is also issued.
 */
function mysqli_field_flags( $result , $field_offset ) {
    static $flags;

    // Get the field directly
    $flags_num = mysqli_fetch_field_direct($result,$field_offset)->flags;

    if (!isset($flags))
    {
        $flags = array();
        $constants = get_defined_constants(true);
        foreach ($constants['mysqli'] as $c => $n) if (preg_match('/MYSQLI_(.*)_FLAG$/', $c, $m)) if (!array_key_exists($n, $flags)) $flags[$n] = $m[1];
    }

    $result = array();
    foreach ($flags as $n => $t) if ($flags_num & $n) $result[] = $t;

    $return = implode(' ', $result);
    $return = str_replace('PRI_KEY','PRIMARY_KEY',$return);
    $return = strtolower($return);

    return $return;
}

I think that'll only give you some flags; nullable or not etc.

You might be better off querying INFORMATION_SCHEMA.COLUMNS to get that sort of details.