0

I am having a bit of trouble converting my module db_query argument to pass as an array as oppose to a string. The module is D6 and I am trying to port it D7. Here is the snippet:

$result = db_query("SELECT nfi.field_name, nfi.label
  FROM {field_config_instance()} nfi
    INNER JOIN {field_config()} nf ON nfi.field_name = nf.field_name
  WHERE nfi.type_name = '%s' AND nf.active = 1 AND nf.locked = 0", $type_name);

And here the updated version as suggested below, but I am still getting a PDOException error:

$result = db_query("SELECT nfi.field_name, nfi.label
  FROM {field_config_instance()} nfi
    INNER JOIN {field_config()} nf ON nfi.field_name = nf.field_name
  WHERE nfi.type_name = ':type_name'  AND nf.active = 1 AND nf.locked = 0", array(':type_name'=>$type_name));

The full error is as below, line 49 is the line starting "WHERE" or the entire bottom line basically:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '() nfi INNER JOIN field_config() nf ON nfi.field_name = nf.field_name ' at line 2: SELECT nfi.field_name, nfi.label FROM {field_config_instance()} nfi INNER JOIN {field_config()} nf ON nfi.field_name = nf.field_name WHERE nfi.type_name = ':type_name' AND nf.active = 1 AND nf.locked = 0; Array ( [:type_name] => page ) in cck_private_fields_get_content_private_fields() (line 49 of C:|... common.inc).

Any suggestions welcome. When I manage to finish this module I'll be submitting it to Drupal.org so everyone can use it.

2 Answers 2

1

There are three errors in that query:

  • The database tables are not field_config_instance(), and field_config(), but field_config_instance, and field_config. Parentheses have a particular meaning in SQL, and putting them after a table name causes an error message.
  • The field_config_instance table doesn't contain any "label" field. See the schema definition given in field_schema().

    $schema['field_config_instance'] = array(
      'fields' => array(
        'id' => array(
          'type' => 'serial', 
          'not null' => TRUE, 
          'description' => 'The primary identifier for a field instance',
        ), 
        'field_id' => array(
          'type' => 'int', 
          'not null' => TRUE, 
          'description' => 'The identifier of the field attached by this instance',
        ), 
        'field_name' => array(
          'type' => 'varchar', 
          'length' => 32, 
          'not null' => TRUE, 
          'default' => '',
        ), 
        'entity_type' => array(
          'type' => 'varchar', 
          'length' => 32, 
          'not null' => TRUE, 
          'default' => '',
        ), 
        'bundle' => array(
          'type' => 'varchar', 
          'length' => 128, 
          'not null' => TRUE, 
          'default' => '',
        ), 
        'data' => array(
          'type' => 'blob', 
          'size' => 'big', 
          'not null' => TRUE, 
          'serialize' => TRUE,
        ), 
        'deleted' => array(
          'type' => 'int', 
          'size' => 'tiny', 
          'not null' => TRUE, 
          'default' => 0,
        ),
      ), 
      'primary key' => array('id'), 
      'indexes' => array(
        // Used by field_delete_instance().
        'field_name_bundle' => array('field_name', 'entity_type', 'bundle'),
        // Used by field_read_instances(). 
        'deleted' => array('deleted'),
      ),
    );
    
  • Drupal 7 placeholders aren't wrapped between quotes, when they are for strings.

The following query should not return any error.

SELECT nfi.field_name FROM {field_config_instance} nfi
  INNER JOIN {field_config} nf ON nfi.field_name = nf.field_name
  WHERE nfi.type_name = :type_name  AND nf.active = 1 AND nf.locked = 0
0

If this is for Drupal 7, you should name your array items and placeholders.

$result = db_query("SELECT nfi.field_name, nfi.label
  FROM {field_config_instance()} nfi
    INNER JOIN {field_config()} nf ON nfi.field_name = nf.field_name
  WHERE nfi.type_name = ':type_name' AND nf.active = 1 AND nf.locked = 0", array(':type_name'=>$type_name));

In Drupal 6, db_query() doesn't take an array as second argument.

4
  • Thank you for your help Drupac. I am still getting a PDOException error though unfortunately. I have updated the question above to reflect the changes and the error, please have a look :) Commented Aug 8, 2012 at 4:03
  • 2
    The quotes are wrong. Use nfi.type_name = :type_name.
    – Berdir
    Commented Aug 8, 2012 at 6:37
  • Hi Berdid, thanks for your input. If I take out the quotes from just the line above it still gives the same PDOException unfortunately. If I take them out of the array also i.e array(:type_name=>$type_name)); I get a fatal error on that line instead. Is that what you meant? Commented Aug 8, 2012 at 8:57
  • The documentation for db_query() clearly states that, "Instead of a variable number of query arguments, you may also pass a single array containing the query arguments." It is not true that in Drupal 6 db_query() doesn't take an array as second argument. Also, Drupal doesn't use parentheses after table names, such as in {field_config_instance()}; Drupal uses curly brackets.
    – apaderno
    Commented Aug 17, 2012 at 18:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.