Search code examples
phpmysqlsqldatatypes

Comparing data types between MySQL and PHP

For example: I have variable $name and I like to save it to my database to table $table and column name. But before I want to vefiry that it has the same data type as the database column (for varchar, char, text, ... it must be string).

How can I do this?

To select MySQL data type:

$query = mysql_query("SELECT data_type
               FROM information_schema.columns
               WHERE table_schema = '".$dbName."'
               AND table_name = '".$table."'
               AND column_name = '".$column."' ");

Getting PHP type:

 gettype($variable);

But how can I compare the data types (varchar, char, text to string)?

Thank you very much!

Solution

  • This should be a simple problem to work around with a mapping array like the following sample code snippet:

    $type_map = array(
    'varchar' => 'string',
    'char' => 'string',
    'text' => 'string',
);

$column_type = 'text'; // get from DB query
$my_var = 'some string';
$var_type = gettype($my_var);

if(array_key_exists($column_type, $type_map) and
   $type_map[$column_type] == $var_type) {
    //proceed with insert of record
}