Checking Database Type of a Field in Drupal

Recently I was asked how to check if a particular field in is stored as an INT(11) in a database. The user requested a function to do this check for any field. So I looked into the Drupal API to figure something out. I derived 2 possible solutions:

Solution 1: Some Drupal API + MySQL queries

function _mymodule_is_db_column_int11($drupal_field_name) {
  $drupal_field_info = field_info_field($drupal_field_name);

  // From this result you can get the table name, which follows a known
  // convention in the Field API (but this lets you get it grammatically)
  // and also value field names from the definition array.
  $sql_details = $drupal_field_info['storage']['details']['sql'];
  $table_name = key($sql_details[FIELD_LOAD_CURRENT]);
  $value_col_name = $sql_details[FIELD_LOAD_CURRENT]['value'];

  // Then in MySQL check the column type. I see no way to do this using the drupal api.
  $raw_table_info = db_query("SHOW COLUMNS FROM :tbl;", array(':tbl' => $table_name)->fetchAll();

  $is_int11_field = FALSE;
  foreach ($raw_table_info as $result_id => $recObject) {
    if ($recObject->field == $value_col_name && $recObject->Type == 'int(11)') {
      $is_int11_field = TRUE;
    }
  }
  return $is_int11_field;
}

Some downsides to this approach:

  1. It doesn’t fully use the Drupal API.
  2. Ideally it should cache results per field.
  3. You may need specific MySQL user permissions to use the SHOW statement.

Solution 2: Only Drupal API

function _mymodule_is_db_column_int11($drupal_field_name) {
  $fields = field_read_fields();
  $my_field = &$fields[$field_name];

  $field_uses_sql_storage = $my_field['storage']['type'] = 'field_sql_storage';
  $field_value_uses_int_11 = $my_field['columns']['value']['type'] == 'int' && $my_field['columns']['value']['length'] == 11;

  return $field_uses_sql_storage && $field_value_uses_int_11;
}

This solution is more robust and Drupal-y without depending on custom MySQL usage, and solely using the Drupal API. field_read_fields() is used to get the Schema and other Drupal information on each Field actively used in the website.

Note that all fields including inactive (disabled) fields on the site can be introspected by passing $include_additional['include_deleted'] as TRUE to field_read_fields(). You could also limit the number of fields you lookup from Drupal by passing filter parameters to the function. An example could be filtering on the module value found in the field_config table for module names which you have custom programmed yourself.

This is small example of how learning more of the Drupal API can make your code more robust and generic.

Looking for quality Drupal Web Hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , , , , | Posted under Drupal | RSS 2.0

Author Spotlight

David Gurba

David Gurba

I am a web programmer currently employed at UCSB. I have been developing web applications professionally for 8+ years now. For the last 5 years I’ve been actively developing websites primarily in PHP using Drupal. I have experience using LAMP and developing data driven websites for clients in aviation, higher education and e-commerce. If you’d like to contact me I can be reached at david.gurba@arvixe.com

Leave a Reply

Your email address will not be published. Required fields are marked *


9 − 3 =

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>