Page 1 of 1
MySQL data types
Posted: Tue Feb 26, 2013 5:48 am
by woot
Hi there.
Is there a way to retrieve allowed field types in MySQL using PHP?
This can be achieved for each individual field of a particular table querying the database with:
and then parsing the returned array to fetch the 'Type' column ($row['Type'], when using a loop), but what I want to have isn't the type of a specific field but rather something similar to what exists in phpMyAdmin (for example) when you want to create a new table, which is, I want to populate a dropdown control with existing field data types in MySQL but it seems like there's no direct query which can be executed to provide me that information directly (or at least I couldn't find it in my search tries so far).
Is there a practical solution to achieve this?
Thanks in advance.
Re: MySQL data types
Posted: Tue Feb 26, 2013 2:34 pm
by requinix
A list of all the possible data types? Unless you find a list somewhere,
here's the place to get them from. Or borrow them from phpMyAdmin.
As for the thing you don't want, the information_schema "database" provides all that same information but in a more code-friendly format.
Re: MySQL data types
Posted: Tue Feb 26, 2013 2:59 pm
by mecha_godzilla
Hi,
Just to add to that, I don't think phpMyAdmin does actually retrieve a list of supported data types as such because my copy doesn't allow me to select the BIT data type when I'm creating a new table even though my copy of MySQL supports it.
I think in this particular situation, you need to consider what problem you're trying to solve here. As requinix mentions, if you need to know what data types are supported for a specific version of MySQL then you'll need to go through the manual and check, but it presumably wouldn't be that difficult to have a script that defines a baseline of data types that will work in all versions (assuming v5.0+) and then extend that with the additional data types that the later versions support.
HTH,
Mecha Godzilla
Re: MySQL data types
Posted: Tue Feb 26, 2013 4:38 pm
by requinix
I can't find it in GitHub but did find it in phpMyAdmin 3.5.7.
/libraries/data_drizzle.inc.php
Code: Select all
// VARCHAR, TINYINT, TEXT and DATE are listed first, based on estimated popularity
$cfg['ColumnTypes'] = !empty($cfg['ColumnTypes']) ? $cfg['ColumnTypes'] : array(
// most used
'INTEGER',
'VARCHAR',
'TEXT',
'DATE',
// numeric
'NUMERIC' => array(
'INTEGER',
'BIGINT',
'-',
'DECIMAL',
'DOUBLE',
'-',
'BOOLEAN',
'SERIAL',
'UUID',
),
// Date/Time
'DATE and TIME' => array(
'DATE',
'DATETIME',
'TIMESTAMP',
'TIME',
),
// Text
'STRING' => array(
'VARCHAR',
'TEXT',
'-',
'VARBINARY',
'BLOB',
'-',
'ENUM',
),
);
Re: MySQL data types
Posted: Tue Feb 26, 2013 5:38 pm
by mecha_godzilla
I'm pleased to see I'm not the only one that trawled through the phpMyAdmin codebase looking for that information

In my copy (v3.3.9.2) a similar routine can be found in the "config.inc.php" script.
Code: Select all
// varchar, tinyint, text and date are listed first, based on estimated popularity
$cfg['ColumnTypes'] = array(
'VARCHAR',
'TINYINT',
'TEXT',
'DATE',
'SMALLINT',
'MEDIUMINT',
'INT',
'BIGINT',
'FLOAT',
'DOUBLE',
'DECIMAL',
'DATETIME',
'TIMESTAMP',
'TIME',
'YEAR',
'CHAR',
'TINYBLOB',
'TINYTEXT',
'BLOB',
'MEDIUMBLOB',
'MEDIUMTEXT',
'LONGBLOB',
'LONGTEXT',
'ENUM',
'SET'
);
What's interesting(?) about the code being in this particular script is that there's some expectation on the part of the developers that you might want to edit these values.
M_G
Re: MySQL data types
Posted: Tue Feb 26, 2013 6:54 pm
by woot
Silly me, I could have done the same but that thought didn't even cross my mind.
Anyways, thanks a lot for the info guys, that should be enough for what I want to do.