Membership registration with existing membership database

Hi Folks,

I have an existing membership database (600 member dog club) used by the club secretary. This database (Excel spreadsheet) contains basic membership information including email addresses for about 87% of the members and a unique ID number. I have added Password, UserName & UserLevel columns and imported it into my online MySQL database .

I want to create a user login app. Using WA SecurityAssist I have created the applicable pages. I have marked UserName to be unique.

After manually adding a Password & UserName into the database I tested the WA SecurityAssist LogIn & Profile pages and they are working fine (Hooray).

This Login/Registration process is for existing members only, there is a separate manual process for folks to apply for membership.

How can I modify/use the Registration page to utilize the existing database and only allow registration for those already in the database?

One tedious option I thought about because of my lack of programming skills was to create random Passwords & UserNames in the database records and mail merging them into an email to send to the users. A lot of work for 600 people and users would not be able to choose there UserName.

Thanks,
Dennis

you will need to make sure that all of the existing records have an email address.

you should also create a new column for Registerred, set the default value to 0, this will be used to make sure they dont come back and overwrite the registration with a new one.

next, create a recordset to filter the email address column on the email address form element, then set the recordset to advanced view, in the bottom section expand the tdatabase and the users table, select the registered column and click the where button to add it to the where clause, find it in the where clause;
AND Registered

and add " = 0":

ANDRegistered = 0

this will look up the email address and be used to make sure they have not filled in the registration already.

Now add server validation from Form Toolkit to the page:
?lid=webforms

Select number validation. For the server variable, enter:
$totalRows_<RecordsetName>

where <RecordsetName> is the name of the recordset you created.

set the minimum number to 1 and the max number to 100

This will allow the form to submit only if the record set returns a value.


Next you will need to replace the Insert behavior on the registration page with an Data Assist Update behavior to update the registration record, make sure to update the Registered column to 1.

Hey Jason, thank you so much!

I believe I was actually able to follow your instructions, not that they were difficult but that my skills with this are very limited. But that makes it even more gratifying when it works.

However to get it to work, I had to delete the server validation I created (Form Toolkit) so I must be doing something incorrectly with it.

With the server validation, the registration form would just reappear when Register was clicked, without it the Log In page appears and when I check the database the record is changed correctly. I also tried changing the numbers to 1 and 999 since there are just under 600 row in the database

Attached is a screen image of the server validation window.

Another thing I noticed during testing, if the Registered column for the member is 1 the form appears to work bringing up the Log In form. But the member can’t login with the new info because the database wasn’t updated. Could an error message be generated?

Thanks,
Dennis

for the server variable, the recordset name does not go in the brackets, instead of:
$totalRows_<rsMembersAreaReg>

use:
$totalRows_rsMembersAreaReg


if could be that an error is preventing the record update, se an copy of the page so I can see the code.

Opp's, sorry Jason, I had noticed the brackets and removed them but forgot to capture an update screen image.

The record not being updated is correct operation with a 1 in the Registered column. The problem is the Registration form appears to be accepting the registration by bringing up the Log In form, where instead it should fail alerting the member to the fact that they are already registered.

Attached is the page.

Thanks,
Dennis

1) in the number validation, you have set the minimum number to 0, set it to 1 instead.


2) The code for the recordset is at lines 20 - 60:

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 

{

  if (PHP_VERSION < 6) {

    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  }



  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);



  switch ($theType) {

    case "text":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;    

    case "long":

    case "int":

      $theValue = ($theValue != "") ? intval($theValue) : "NULL";

      break;

    case "double":

      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

      break;

    case "date":

      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

      break;

    case "defined":

      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

      break;

  }

  return $theValue;

}

}



$colname_rsMemberAreaReg = "-1";

if (isset($_POST['Email_M1'])) {

  $colname_rsMemberAreaReg = (get_magic_quotes_gpc()) ? $_POST['Email_M1'] : addslashes($_POST['Email_M1']);

}

mysql_select_db($database_connOESCA, $connOESCA);

$query_rsMemberAreaReg = sprintf("SELECT Member_Account_ID, Email_M1 FROM member_accounts WHERE Email_M1 = %s AND Registered = 0", GetSQLValueString($colname_rsMemberAreaReg, "text"));

$rsMemberAreaReg = mysql_query($query_rsMemberAreaReg, $connOESCA) or die(mysql_error());

$row_rsMemberAreaReg = mysql_fetch_assoc($rsMemberAreaReg);

$totalRows_rsMemberAreaReg = mysql_num_rows($rsMemberAreaReg);?>

move it to line 5 so the recordset is created before the validation occurs.

ooh, ooh, I'm so excited, it's working.

PS: I am so amazed how fast you read my note, unzipped the attachment, critiqued the code and replied with the fixed. All in, or less than, 5 minutes.


I have another question. This is my first time using WA SecurityAssist and the pages the wizard creates.

Currently when attempting to open a restricted page (WA SA Access Page Manager) the visitor is directed to the Log In form. After successfully logging in they are taken to their Profile page. The profile page only has an Update button that when clicked reloads the Profile page again, an endless loop. This could become tedious, that every time they log in they have to deal with their profile page. And then find the link back to the page they wanted to go to from the beginning.

I remember seeing the option to redirect to source page or some wording like that. That was selected by default, I can’t remember where I saw that option.

How can the user be redirect back to the restricted page after successfully logging in? And how can I create a link so they can get to their Profile page when they want to update it?

Thanks,
Dennis

on the login page, use the following code for the action attribute of the form tag:

 action="<?php echo (htmlentities($_SERVER["PHP_SELF"]));?><?php echo(isset($_SERVER['QUERY_STRING'])?"?".str_replace("<","%3C",str_replace(">","%3E",str_replace('"',"%22", $_SERVER['QUERY_STRING']))):""); ?>"

also make sure that in the Security Assist Authenticate user Server behavior the "Go to previous URL (if it exists)" Option is checked.

Jason,

"Go to previous URL (if it exists)" and "Pass original query string on redirect" are both checked in both instances of the SecurityAssist Authenticate server behaviors. On the Log In page I do not check either "Remember me" or "Auto log in".

<form  action="<?php echo (htmlentities($_SERVER["PHP_SELF"]));?><?php echo(isset($_SERVER['QUERY_STRING'])?"?".str_replace("<","%3C",str_replace(">","%3E",str_replace('"',"%22", $_SERVER['QUERY_STRING']))):""); ?>" method="post" name="WAATKLogInForm" id="WAATKLogInForm">

is the action line of code.

After Log Off, I attempt to open the restricted page, I Log In and I am taken to the Forgot Password page. I have confirmed the Log In credentials.


Also, how can I create a seperate link to the persons profile page.

Dennis

I have created a support ticket so we can look into this issue further.

To view and edit your support ticket, please log into your support history:
supporthistory.php

If anyone else is experiencing this same issue, please append to this thread.