Find actual Data Type in MySQLi

Question

I am trying to find out the:

1. data type
2. length

of a column in table in PHP. I noticed that I could do that from INFORMATION_SCHEMA as well as my own table as:

$query = $link->query("SELECT * FROM `events` LIMIT 1");
$row = mysqli_fetch_fields($query);
foreach($row as $field){
    print_r($field);
}

The response is definitely an array. I pretty much found out everything like:

( 
    [name] => id 
    [orgname] => id 
    [table] => events 
    [orgtable] => events 
    [def] => 
    [db] => myliveca_baikalpik 
    [catalog] => def 
    [max_length] => 1 
    [length] => 11 
    [charsetnr] => 63 
    [flags] => 49667 
    [type] => 3 
    [decimals] => 0 
) 

So far at this point I have the length of the data but the data type still seems to be encoded (or whatever) represented in number as:

• 3 for int
• 11 for time
• 252 for text

How do I backtrace this?

Answer 1

I ran through some crazy loops and found out the complete trail (excluding the grometric and enums)

1   tinyint
2   smallint
9   mediumint
3   int
8   bigint
246 decimal
4   float
5   double
5   real
16  bit
1   boolean
8   serial
10  date
12  datetime
7   timestamp
11  time
13  year
254 char
253 varchar
252 tinytext
252 text
252 mediumtext
252 longtext
254 binary
253 varbinary
252 tinyblob
252 mediumblob
252 blob
252 longblob

Good Luck!

Answer 2

You are probably looking for predefined mysqli constants:

["MYSQLI_TYPE_DECIMAL"]=>
  int(0)
  ["MYSQLI_TYPE_TINY"]=>
  int(1)
  ["MYSQLI_TYPE_SHORT"]=>
  int(2)
  ["MYSQLI_TYPE_LONG"]=>
  int(3)
  ["MYSQLI_TYPE_FLOAT"]=>
  int(4)
  ["MYSQLI_TYPE_DOUBLE"]=>
  int(5)
  ["MYSQLI_TYPE_NULL"]=>
  int(6)
  ["MYSQLI_TYPE_TIMESTAMP"]=>
  int(7)
  ["MYSQLI_TYPE_LONGLONG"]=>
  int(8)
  ["MYSQLI_TYPE_INT24"]=>
  int(9)
  ["MYSQLI_TYPE_DATE"]=>
  int(10)
  ["MYSQLI_TYPE_TIME"]=>
  int(11)
  ["MYSQLI_TYPE_DATETIME"]=>
  int(12)
  ["MYSQLI_TYPE_YEAR"]=>
  int(13)
  ["MYSQLI_TYPE_NEWDATE"]=>
  int(14)
  ["MYSQLI_TYPE_ENUM"]=>
  int(247)
  ["MYSQLI_TYPE_SET"]=>
  int(248)
  ["MYSQLI_TYPE_TINY_BLOB"]=>
  int(249)
  ["MYSQLI_TYPE_MEDIUM_BLOB"]=>
  int(250)
  ["MYSQLI_TYPE_LONG_BLOB"]=>
  int(251)
  ["MYSQLI_TYPE_BLOB"]=>
  int(252)
  ["MYSQLI_TYPE_VAR_STRING"]=>
  int(253)
  ["MYSQLI_TYPE_STRING"]=>
  int(254)
  ["MYSQLI_TYPE_CHAR"]=>
  int(1)

You can find values of all predefined constants with:

<?php

echo "<pre>";
   var_dump(get_defined_constants());
echo "</pre>";
?>