Querying Custom User Profile Fields in Drupal 7

Question

I have created a module that hooks to and modifies the default Drupal registration form. When processed the form is grabbing information from an external web page (via DOM Parsing) and checking to see if the data being retrieved is already present in an existing user's profile field to prevent duplicates. The parsing script itself is retrieving the required information (as this information is present in the error message.) For the life of me I just can not get the query to scan the appropriate profile field in my database and retrieve any data.

Here is the function I have written:

function CustomVerify_CheckName($name, $server){

$result = db_select('field_data_field_character_first_name','u')
    ->fields('u',array('field_character_first_name'))
    ->condition('field_character_first_name', $name)
    ->condition('field_character_server', $server)
    ->range(0,1)
    ->execute()

if($result->rowCount > 0):

    return TRUE;

endif;}

I have tried several variations; replacing 'field_data_field_character_first_name' with 'users', swapping the fields for the 'data' field (since I believe this is where the appropriate grouping is recorded in a user's profile table entry?) with no luck.

The error message is this:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'u.field_character_first_name' in 'field list': SELECT u.field_character_first_name AS field_character_first_name FROM {field_data_field_character_first_name} u WHERE (field_character_first_name = :db_condition_placeholder_0) AND (field_character_server = :db_condition_placeholder_1) LIMIT 1 OFFSET 0; Array ( [:db_condition_placeholder_0] => David Mortimer [:db_condition_placeholder_1] => Saturn ) in CustomVerify_CheckName() (line 78 of C:\xampp\htdocs\drupal\sites\all\modules\customverify\customverify.module).

Using this message I have tried several variations, all with the same result. You can see from the condition placeholders that the user's profile data is being retrieved from the off-site DOM okay.

Evidently my problem is not fully understanding how to structure this query to retrieve the necessary information. The fields are attached to the user's default profile (I'm not using any custom profile modules) as 'field_character_first_name' and 'field_character_server' - Since people can have the same names on different servers, I need to check that the character name does not exist on the server that the parsing DOM object is returning; and to reiterate the DOM Object portion is working perfectly.

Woody Payne Woody Payne · Accepted Answer · 2013-07-05T09:33:50

After a little reading with the Drupal Database API documentation, the answer to this problem lies in the execution. Using db_select for retrieving entity data (such as user profile fields, node fields, etc) is overkill and unrequired. You can use EntityFieldQuery to achieve the above both in more understandable terms and more understandable markup.

For anyone else that needs this functionality in the future, here's how to do it:

    $query = new EntityFieldQuery();
    $query->entityCondition('entity_type', 'user')
    ->fieldCondition('field_character_first_name', 'value', $CheckName)
    ->fieldCondition('field_character_server', 'value', $CheckServer)
    ->count()
    ->range(0,1);

In my case I want to check if the row exists, not actually output the data. The count() condition makes the query return the number of the results. A simple

    if($result > 0):

        form_set_error('link','Username taken!');

    endif;

Attached to the form validation callback function adds the necessary errors if any matching rows are found.

Querying Custom User Profile Fields in Drupal 7

1 Answers