Drupal 6: Getting custom fields into the database

Question

I needed a custom field that couldn't be provided by the core profile module (Select list populated from a SQL query). I was able to successfully add the field with proper options; however, I am unsure how to handle this new field once submitted.

From what I understand, I need to write a function that handles my SQL insert, and then call that function from a hook_form_alter submit button.

As of now, it is passing only the field name, not the value. And the field name is being serialized and stored in the 'data' field of the user table. I'm attempting to pass it to its own column.

Here is my code...

//takes value and inserts it to account field   
 function accountselect_submitaccount() {
        db_query( "INSERT INTO {user} (account)
                  VALUES {account_name}" );
      }

Then...

//call the above function using custom submit (I suspect this is the troubled area)
  function accountselect_form_alter(&$form, $form_state, $form_id) {
    if($form_id == 'user-register')
    $form['#submit'][] = 'accountselect_submitaccount';
  }

Answer 1

To my knowledge, new fields are only added to the data column if there's no column matching the field name. You should be able to just create a column of account into the user table and it'll get populated with that data.

As for the code you've posted, your submission function has a number of issues:

• Submit functions take functions that include the submitted values. submit_function($form, &$form_state) gives you access to the $form_state['values'] array.
• An INSERT will never work. You need an UPDATE query.
• You can't do {account_name} in the query. You need to actually use the value from $form_state['values'].

Answer 2

If I got you right, you are trying to alter the registration form of a user, so that beside inserting the "regualar" fields (username, password, etc...) the user will be requested to fill in additional fields. If I got it right, this is how I would proceed (as you said you are new to Drupal I tried to be as specific as I could).

Preliminary notes

1. I would not modify the users table. One of the key design principle in Drupal is that you shold never modify others' code: you should achieve what you want by hooking yours into the general system of callbacks and hooks (see also note below, though). In the rare cases where this is technically not possible (and the nature of your modification will increase usability/functionality for the general public), you should submit a patch for the attention of the code mantainers.
2. Select boxes always return their array index and not their array value. This is so because of a number of good reasons. One for all: the value of the array will most probably be some text which is different in each language, so forms are normally defined something down the lines of [bread] => t('bread') so that when using the form in other languages the choice will display "pain" or "bröd" or "pane", but the value returned (and possibly stored in the DB will be always be "bread".

Design

1. Create your own module, and make it interact via hooks with the core users one.
2. Create your own table: the primary key will be the user uid (UID) from the users table. You can have as many fields in the table as you need. In your specific case, if you use MySQL you might evaluate if you want to use an enum type, or simply write values as passed by the PHP code (I personally tend to limit the number of constraints in the DB and manage data validation in the code, so I would go for the second option... but it's a matter of style, really).
3. Implement hook_form_FORM_ID_alter() where FORM_ID will be the ID of the form the user is going to use for registration. If there is more than one possible form to be used for registering, implement hook_form_alter() instead, and create a switch/case structure in it.
4. Use the FAPI (form API) to define validation and callback functions. You can see what fields are supported by the select box here. You can define the #element_validate field to assign a validation callback, if you need to. You will have to append (as opposed to assign) the name of your submit callback function to the field #submit of the button you use for submitting the entire form.
5. Implement your callbacks. Callbacks that deals with forms normally receive two arguments, conventionally called $form and $form_state. The latter contains form values as selected by user. Your submit callback will be the one containing the drupal_write_record() instruction, to save relevant data in your custom table.

Final notes

1. Unluckily, while CCK made it into the core, the possibility to create user profiles as custom content didn't, so you really wish to code your module nicely, as it will be something that you will be using most probably until Drupal 8.

2. I discovered (thanks to another responder to this very same question) that Drupal does provide a mechanism for altering the user's table, indeed. I still do not like the design principle behind this, though:

   • because it tangles things that should be logically kept separate (core and custom/contrib modules and data)
   • because it does not offer namespace protection (say you add a custom field called "timezone" and months later you want to install Date API (which will try also to create a field called "timezone"...)
   • because it makes code less readable and more difficult to maintain

   An informative thread to read on this is however here. It might be finally worth mentioning that the underlying mechanism behind this feature is changing between D6 to D7.

HTH!