Possible values for "type" in mysql_fetch_field result

Question

When calling mysql_fetch_field, the result has a property called "type". I'm seeing things like "string", which is a PHP type, but also "blob", which is a MySQL type. Is there any authoritative and exhaustive list of the possible types this function returns?

The PHP documentation is vague at best. I need to know I'm covering every possibility. I did manage to find some documentation for http://www.php.net/manual/en/function.mysql-field-type.php which does a bit better: "The returned field type will be one of "int", "real", "string", "blob", and others..."

It points me to the MySQL documentation. In there, it seems to want me to start looking at C header files, because all it says is "The MYSQL_FIELD structure for the current column".

Answer 1

Taken from PHP: http://php.net/manual/en/mysqli.constants.php#constantmysqli-group-flag

0    MYSQLI_TYPE_DECIMAL: Field is defined as DECIMAL
1    MYSQLI_TYPE_CHAR: Field is defined as TINYINT. For CHAR, see MYSQLI_TYPE_STRING
2    MYSQLI_TYPE_SHORT: Field is defined as SMALLINT
3    MYSQLI_TYPE_LONG: Field is defined as INT
4    MYSQLI_TYPE_FLOAT: Field is defined as FLOAT
5    MYSQLI_TYPE_DOUBLE: Field is defined as DOUBLE
6    MYSQLI_TYPE_NULL: Field is defined as DEFAULT NULL
7    MYSQLI_TYPE_TIMESTAMP: Field is defined as TIMESTAMP
8    MYSQLI_TYPE_LONGLONG: Field is defined as BIGINT
9    MYSQLI_TYPE_INT24: Field is defined as MEDIUMINT
10   MYSQLI_TYPE_DATE: Field is defined as DATE
11   MYSQLI_TYPE_TIME: Field is defined as TIME
12   MYSQLI_TYPE_DATETIME: Field is defined as DATETIME
13   MYSQLI_TYPE_YEAR: Field is defined as YEAR
14   MYSQLI_TYPE_NEWDATE: Field is defined as DATE
16   MYSQLI_TYPE_BIT: Field is defined as BIT (MySQL 5.0.3 and up)
246  MYSQLI_TYPE_NEWDECIMAL: Precision math DECIMAL or NUMERIC field (MySQL 5.0.3 and up)
247  MYSQLI_TYPE_ENUM: Field is defined as ENUM
248  MYSQLI_TYPE_SET: Field is defined as SET
249  MYSQLI_TYPE_TINY_BLOB: Field is defined as TINYBLOB
250  MYSQLI_TYPE_MEDIUM_BLOB: Field is defined as MEDIUMBLOB
251  MYSQLI_TYPE_LONG_BLOB: Field is defined as LONGBLOB
252  MYSQLI_TYPE_BLOB: Field is defined as BLOB
253  MYSQLI_TYPE_VAR_STRING: Field is defined as VARCHAR
254  MYSQLI_TYPE_STRING: Field is defined as CHAR or BINARY
255  MYSQLI_TYPE_GEOMETRY: Field is defined as GEOMETRY

Answer 2

From here

blob - Column containing or expression that returns a Binary Long OBject. This includes all BLOB or TEXT type columns.

date - DATE column. Expressions that return a date value are of type int, real, orstring, depending on the value returned.

datetime - DATETIME column. Expressions that return a datetime value are of type int, real, orstring, depending on the value returned.

int - Column containing or expression that returns integer data. This includes all INT type columns.

null - Expression that returns NULL.

real - Column containing or expression that returns a floating-point number. This includes the DECIMAL, FLOAT, and DOUBLE column types.

string - CHAR, ENUM, SET, or VARCHAR column, or an expression that returns character data.

Note:
Even if the number of characters returned by an expression exceeds the maximum length of 255 characters for a CHAR/VARCHAR column, the type returned is string and not blob, as you might expect.

time - TIME column. Expressions that return a time value are of type real or string, depending on the value returned.

timestamp - TIMESTAMP column. Expressions that return a timestamp value are of type int.

year - YEAR column. Expressions that return a year value are of type int.

unknown - Type that doesn't match any type known by mysql_fetch_field() . An occurrence of this type may indicate that the version of MySQL is more recent than the version of PHP.

---

And here from the book "PHP Functions Essential Reference" edited by Zak Greant

Answer 3

See http://php.net/manual/en/function.mysql-fetch-field.php, that provides some useful information. I think the type will only be the common ones such as int, float, string or blob.