Auto assign role based on registration code

well, after studying the database and some of the documentation, I think the best way to do this is to add a new column to the {role} table, which i've called 'key', that stores the unique registration code for the roles.

next I add a textfield to the registration page for users to insert their codes. when they press submit the code is checked against the {roles} table for any matching 'key' fields, and passing back the 'rid' for that role.

finally the determined 'rid' and the new users' 'uid' get added to the {users_roles} table as normal.

after a bit of guesswork I've come up with the snippets of code below. I have no idea if it's anything near to workable, so if anyone can suggest the next step in getting this to work?

<?php

  $form['regcoderole_code'] = array(
    '#type' => 'textfield',
    '#title' => t('Registration Code'),
    '#value' => $regcoderole_code,
    '#size' => 32,
    '#maxlength' => 32,
    '#required' => TRUE,
    '#description' => t('Please enter your registration code.')
  );  
 return $form;
 
  $sql1 = "SELECT rid FROM {role} WHERE key = '$regcoderole_code'";
  $regcoderole_role = db_result(db_rewrite_sql($sql1));

  $sql2 = 'INSERT INTO {users_roles} (uid, rid) values ($user->uid, $regcoderole_role)';
  $result = db_query(dbrewrite_sql($sql2));

?>

OK so after playing with the regcode module, I've come up with this so far, where it's mainly from the last function with some changes. it loads and displays the 'Registration Code' box on the registration page, but when you submit it, it comes up with the following error:

    * warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/adfad666/public_html/drupal/includes/database.mysql.inc on line 236.

The user is still created as normal, but no extra roles are assigned in the {user_roles} table.

getting closer! can anyone see what needs to be changed?

<?php
// $Id: regcoderole.module,v 0.1 $

/**
 * @file
 *   regcoderole.module
 */

function regcoderole_perm() {
  return array('administer registration codes');
}

function regcoderole_user($op, &$edit, &$account, $category = NULL) {

  // Only do the checking if the codes variable is set.
    switch ($op) {

      case 'register':
        // Inject the registration code field into the registration form.
        $form['regcoderole_code'] = array(
          '#type' => 'textfield',
          '#title' => t('Registration Code'),
		  '#value' => $regcoderole_code,
          '#required' => TRUE,
          '#description' => t('Please enter your registration code.')
        );
        return $form;

      case 'validate':
        if (($category == 'account') && (!$account->uid)) {   
          // Make sure that the entered code is in the list.
         
		   $sql1 = "SELECT rid FROM {role} WHERE key = '$regcoderole_code'";
		   $regcoderole_role = db_result(db_rewrite_sql($sql1));
                   return $regcoderole_role;
		 
		 $sql2 = 'INSERT INTO {users_roles} (uid, rid) values ($account, $regcoderole_role)';
		 $result = db_query(db_rewrite_sql($sql2));
		 
        }
        break;
    } // switch
}


?>

Some of these posts must look rediculous to the knowledgable coders out there...

but anyway here's the latest on what i've got. the code is a bit tidier and should make a little bit more sense. it doesn't give errors anymore, but it still doesn't add the fields to the database.

<?php

<?php
// $Id: regcoderole.module,v 0.1 $

/**
 * @file
 *   regcoderole.module
 */

function regcoderole_perm() {
  return array('administer registration codes');
}


function regcoderole_user($type, &$edit, &$account, $category = NULL) {
    switch ($type) {
      case 'register':
        $form['regcoderole_code'] = array(
          '#type' => 'textfield',
          '#title' => t('Registration Code'),
		  '#value' => $regcode,
          '#required' => TRUE,
          '#description' => t('Please enter your registration code.')
        );
        return $form;
      case 'validate':		 
		 if ($regcode > 0) {
		   $sql = 'SELECT rid FROM {role} WHERE key = (%d)'
		   $result = db_result($sql, $regcode);
		     if ($result > 0) {
		       $sql = 'INSERT INTO {users_roles} (uid, rid) values (%d, %d)'
			   db_query($sql, $account->uid, $result);
		    }
         }
        }
}

?>

the code sucessfully assigns a role to a user based on a registration code they enter during registration.

now all I need to do is write an admin page that calls the list of roles and allows me to set the registration code there, and even add a new role and registration code all at once... currently i've done it all manually via phpmyadmin, but i'll save that for another day!

Thanks for your help Alan, much appreciated!!!

Adam

<?php
// $Id: regcoderole.module,v 0.1 $
/**
 * @file
 *   regcoderole.module
 */
function regcoderole_perm() {
  return array('administer registration codes');
}
function regcoderole_user($op, &$edit, &$account, $category = NULL) {
    switch ($op) {
      case 'register':
        $form['regcoderolecode'] = array(
          '#type' => 'textfield',
          '#title' => t('Registration Code'),
		  '#default_value' => $regcode,
          '#required' => TRUE,
          '#description' => t('Please enter your registration code.'),
        );
        return $form;
		break;
      case 'insert':
         $regcode = trim($edit['regcoderolecode']);
         if (strlen($regcode)) {
              $sql = "SELECT rid FROM {role} WHERE rolekey = '%s'";
           if ($result = db_result(db_query($sql, $regcode))) {
               $sql = "INSERT INTO {users_roles} (uid, rid) values (%d, %d)";
               db_query($sql, $account->uid, $result);              
            }
         }
         break;
       }
}
?>