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:

Code: Select all

DESCRIBE `table_name`;
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 :mrgreen: 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.