mysqli: how to get the type of a column in a table

Question

How can i get the type of a column in a table, e.g. datetime, int(32) or varchar(40)?
i have got a result, that is fetched into an object, the vardump prints:

object(stdClass)[8]
  public 'id' => string '3' (length=1)
  public 'email' => string '[e-mail]' (length=6)

id should be an integer and email a varchar(i). I am working with php5 and mysqli.

thx for your help!

Answer 1

If you're using mysqli, which I highly recommend for so many reasons, it will be as easy as

$query = "SELECT * from " . $table_name;

if($result = $mysqli->query($query)){
    // Get field information for all columns
    while ($column_info = $result->fetch_field()){
        echo $column_info->type;
    }
}

That would print out some numbers, these numbers indicate the type of each column as following:

numerics

BIT: 16

TINYINT: 1

BOOL: 1

SMALLINT: 2

MEDIUMINT: 9

INTEGER: 3

BIGINT: 8

SERIAL: 8

FLOAT: 4

DOUBLE: 5

DECIMAL: 246

NUMERIC: 246

FIXED: 246

dates

DATE: 10

DATETIME: 12

TIMESTAMP: 7

TIME: 11

YEAR: 13

strings & binary

CHAR: 254

VARCHAR: 253

ENUM: 254

SET: 254

BINARY: 254

VARBINARY: 253

TINYBLOB: 252

BLOB: 252

MEDIUMBLOB: 252

TINYTEXT: 252

TEXT: 252

MEDIUMTEXT: 252

LONGTEXT: 252

And for more information about fetch_field check this out http://us2.php.net/manual/en/mysqli-result.fetch-field.php

Answer 2

how can i get the type of a column in a table, e.g. datetime, int(32) or varchar(40)?

obviously you don't need it.

id should be an integer and email a varchar

Well - you have to ask so. Just use prepared statement with mysqli

$stm = $db->prepare("select 1 `int`, 'str' `string`, NULL `NULL`");
$stm->execute();
$res = $stm->get_result();
$row = $res->fetch_assoc();
var_dump($row);

array(3) {
  ["int"]=>
  int(1)
  ["string"]=>
  string(3) "str"
  ["NULL"]=>
  NULL
}

Answer 3

Everything that php gets from the database is always of type string

Answer 4

To get that information, you need to query the information_schema.columns table. Something like this will get the MySQL data type:

SELECT DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_NAME = 'my_table' AND COLUMN_NAME = 'my_column'

Answer 5

In response to your comment to get the data type, you can run the query DESC tablename, and iterate over the resultset, which will give you data on each column in the table.

This would give you everything for a table, you can of course modify Patrick's answer to do the same (or if you want it to return this more specifically).