MySQL data types

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
woot
Forum Newbie
Posts: 8
Joined: Tue Feb 19, 2013 8:35 am

MySQL data types

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: MySQL data types

Post 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.
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: MySQL data types

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: MySQL data types

Post 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',
    ),
);
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: MySQL data types

Post 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
User avatar
woot
Forum Newbie
Posts: 8
Joined: Tue Feb 19, 2013 8:35 am

Re: MySQL data types

Post 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.
Post Reply