Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Form Questions /

Validate form fields against databse


Go to End
Reply


10 Posts   1532 Views

Avatar
dross

Community Member, 7 Posts

28 November 2012 at 6:29am

I'm having some trouble with this and am hoping that someone can point me in the right direction. I have a registration form that I built using the great tutorial on SSbits here: http://www.ssbits.com/tutorials/2010/site-members-registering-users-and-allowing-them-to-edit-their-details/
Within that registration form, I have 2 fields that need to be validated against a database of numbers that have already been entered. These fields are "SourceCode" and "VoucherNumber". I need to make sure that the 2 fields match 2 rows in the database, basically just like a username and password. On the old site I used this code:

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
// source code and card number sent from form
$sourcecode=$_POST['location_code_c'];
$cardnumber=$_POST['card_number_c'];

// To protect MySQL injection
$sourcecode = stripslashes($sourcecode);
$cardnumber = stripslashes($cardnumber);
$sourcecode = mysql_real_escape_string($sourcecode);
$cardnumber = mysql_real_escape_string($cardnumber);

$sql="SELECT * FROM $tbl_name WHERE location_code='$sourcecode' and card_number='$cardnumber' AND registered='0'";
$result=mysql_query($sql);
   
// Mysql_num_row is counting table row
$count=mysql_num_rows($result);
// If result matched $sourcecode and $cardnumber, table row must be 1 row

if($count==1){
//
mysql_query ("UPDATE $tbl_name
SET registered=1
WHERE location_code='$sourcecode' and card_number='$cardnumber'");

As you can see from this code, I also make sure that the card isn't already registered by making sure that the value for "registered" is 0. If everything passes then I need the form to also change that to "1".

Here is the code to my registration form:
Card Numbers are now called Voucher Numbers and Location Codes are now called Source Codes.

<?php

class RegistrationPage extends Page {
      
}

class RegistrationPage_Controller extends Page_Controller {
   
   static $allowed_actions = array(
      'RegistrationForm'
   );

   function RegistrationForm() {
      $fields = new FieldSet(
         new TextField('SourceCode', 'Source Code'),
         new TextField('VoucherNumber', 'VoucherNumber'),
         new TextField('FirstName', '<span>*</span> First Name'),
         new TextField('Surname', '<span>*</span> Last Name'),
         new EmailField('Email', '<span>*</span> Email'),
         new TextField('Address', '<span>*</span> Address'),
         new TextField('AddressLine2', 'Address Line 2'),
         new TextField('City', '<span>*</span> City'),
         new TextField('State', '<span>*</span> State'),
         new TextField('PostalCode', '<span>*</span> Zip'),
         new TextField('HomePhone', 'Main Contact Phone'),
         new ConfirmedPasswordField('Password', '<span>*</span> Password'),
         new CheckboxField('UpdateMe', 'Update Me When New Merchants Join'),
         new CheckboxField('PrefAll', 'All'),
         new CheckboxField('PrefFoodDrink', 'Food/Drink'),
         new CheckboxField('PrefSports', 'Sports/Family Entertainment'),
         new CheckboxField('PrefRetail', 'Retail'),
         new CheckboxField('PrefHealth', 'Health & Wellness'),
         new CheckboxField('PrefEvents', 'Local Events')   
      );
      
      $actions = new FieldSet(
         new FormAction('doRegister', 'Register')
      );
      
      $validator = new RequiredFields ('FirstName', 'LastName', 'Email', 'Street', 'City', 'State', 'Zip');
      
      return new Form($this, 'RegistrationForm', $fields, $actions, $validator);
      
   }

   function doRegister($data, $form) {
      
      if($member = DataObject::get_one("Member", "`Email` = '" . Convert::raw2sql($data['Email']) . "'"))
      {
         $form->AddErrorMessage('Email', "Sorry, that email address already exists. Please choose another.", 'bad');
         
         Session::set("FormInfo.Form_RegistrationForm.data", $data);
         
         return Director::redirectBack();;
      }

      //Create Member and log them in
      $Member = new UrbanMember();
      $form->saveInto($Member);
      $Member->write();
      $Member->login();
      
      
      //Findo or create 'user' group
      
      if(!$userGroup = DataObject::get_one('Group', "Code = 'users'"))
{
$userGroup = new Group();
$userGroup->Code = "users";
$userGroup->Title = "Users";
$userGroup->Write();
$userGroup->Members()->add($Member);
}
      
      //Add member to user group
$userGroup->Members()->add($Member);
      
      //Get profile page
if($ProfilePage = DataObject::get_one('EditProfilePage'))
{
   return Director::redirect($ProfilePage->Link('?success=1'));
      }      
      
   }
}

?>

Here is the code for the CardNumbers DataObject which is pre-populated that I want to validate against.

<?php
class CardNumbers extends DataObject {
   static $db = array(
      'VoucherNumber' => 'Varchar(255)',
      'SourceCode' => 'Varchar(255)',
      'Registered' => 'Boolean',
      'Processed' => 'Boolean'
   );
   
   
   static $summary_fields = array(      
      'SourceCode' => 'Source Code',
      'VoucherNumber' => 'Voucher Number',      
      'CreatedText' => 'Created',
      'Registered' => 'Registered',
      'Processed' => 'Processed'
   );
   

}

?>

So, what would be the easiest way to validate those 2 fields against the existing database? I have some ideas, but not sure how to implement it. I have been all over the forums and can't find any examples of this type of validation. Any advice would be greatly appreciated. Let me know if more info is needed.

Avatar
lx

Community Member, 83 Posts

28 November 2012 at 10:34am

This is how i would do it: i would use the module "NetefxValidator", which we use for any form validation - not only for the frontend but also for modeladmin managed dataobjects.

In the documentation [url]http://netefx.de/Silverstripe-NetefxValidator.php[/url] there is a section about the function rule for custom validation rules. This allows you to add all kind of database driven rules.

Although this module was made for ss2.4 it works without problems with ss3. I guess only the unique rules needs to be updated for ss3.
If you update the unique rule to make it ss3 compatible it will also be usefull for your project to check the unique emailadress. Then you can remove this email validation in function doregister.

I know that the module looks quite complex in the beginning . But once you understand how to use it you will include it in all your ss projects. (Until there will be a new validator in ss one day)

But you might also ask someone else for a solution without using a module for this.

Avatar
dross

Community Member, 7 Posts

28 November 2012 at 12:08pm

Hey thanks. I'll give this module a shot.

Avatar
dross

Community Member, 7 Posts

29 November 2012 at 12:00pm

I'm using this Netefx Validator module and it seems promising, but I'm not sure how to write the validation function.
Does anyone have any idea how I would write this in Silverstripe, or can I use my old PHP code for the validation? Again, the old code is something like this:
      mysql_connect("$host", "$username", "$password")or die("cannot connect");
      mysql_select_db("$db_name")or die("cannot select DB");
      
      
      // source code and card number sent from form
      $sourcecode=$_POST['SourceCode'];
      $vouchernumber=$_POST['VoucherNumber'];
      
      // To protect MySQL injection
      $sourcecode = stripslashes($sourcecode);
      $vouchernumber = stripslashes($vouchernumber);
      $sourcecode = mysql_real_escape_string($sourcecode);
      $vouchernumber = mysql_real_escape_string($vouchernumber);
      
      $sql="SELECT * FROM $tbl_name WHERE location_code='$sourcecode' and card_number='$vouchernumber' AND Registered='0'";
      $result=mysql_query($sql);
      
      // Mysql_num_row is counting table row
      $count=mysql_num_rows($result);
      // If result matched $sourcecode and $cardnumber, table row must be 1 row
      
      if($count==1){
      //
      mysql_query ("UPDATE $tbl_name
      SET Registered=1
      WHERE location_code='$sourcecode' and card_number='$vouchernumber'");
      
      
      }
      else {
      return "Wrong source code or card number OR card is already registered";
      }

Avatar
lx

Community Member, 83 Posts

30 November 2012 at 3:50am

hi dross,

try this code. I didnt test it, but it think i will work.

<?php
class RegistrationPage_Controller extends Page_Controller {

static $allowed_actions = array(
'RegistrationForm'
);

function RegistrationForm() {
$fields = new FieldSet(
new TextField('SourceCode', 'Source Code'),
new TextField('VoucherNumber', 'VoucherNumber'),
new TextField('FirstName', '<span>*</span> First Name'),
new TextField('Surname', '<span>*</span> Last Name'),
new EmailField('Email', '<span>*</span> Email'),
new TextField('Address', '<span>*</span> Address'),
new TextField('AddressLine2', 'Address Line 2'),
new TextField('City', '<span>*</span> City'),
new TextField('State', '<span>*</span> State'),
new TextField('PostalCode', '<span>*</span> Zip'),
new TextField('HomePhone', 'Main Contact Phone'),
new ConfirmedPasswordField('Password', '<span>*</span> Password'),
new CheckboxField('UpdateMe', 'Update Me When New Merchants Join'),
new CheckboxField('PrefAll', 'All'),
new CheckboxField('PrefFoodDrink', 'Food/Drink'),
new CheckboxField('PrefSports', 'Sports/Family Entertainment'),
new CheckboxField('PrefRetail', 'Retail'),
new CheckboxField('PrefHealth', 'Health & Wellness'),
new CheckboxField('PrefEvents', 'Local Events')
);

$actions = new FieldSet(
new FormAction('doRegister', 'Register')
);

// Validator
$rule_FirstName_required = new NetefxValidatorRuleREQUIRED ("FirstName");
$rule_LastName_required = new NetefxValidatorRuleREQUIRED ("LastName");
$rule_Email_unique = new NetefxValidatorRuleUNIQUE ("Email", "Sorry, that email address already exists. Please choose another.", "error", array('Member', 'Email'));
$rule_Email_required = new NetefxValidatorRuleREQUIRED ("Email");
$rule_Street_required = new NetefxValidatorRuleREQUIRED ("Street");
$rule_City_required = new NetefxValidatorRuleREQUIRED ("City");
$rule_State_required = new NetefxValidatorRuleREQUIRED ("State");
$rule_Zip_required = new NetefxValidatorRuleREQUIRED ("Zip","whats your Zipcode?");
$rule_VoucherNumber_valid = new NetefxValidatorRuleFUNCTION ("VoucherNumber", "VoucherNumber and/or SourceCode are not valid", 'error', array('RegistrationPage_Controller', 'checkVoucherandSourceCode'));

$validator = new NetefxValidator($rule_FirstName_required,
$rule_LastName_required,
$rule_Email_unique,
$rule_Email_required,
$rule_Street_required,
$rule_City_required,
$rule_State_required,
$rule_Zip_required,
$rule_VoucherNumber_valid);
return $validator;

// Form
return new Form($this, 'RegistrationForm', $fields, $actions, $validator);

}

// Validation function used in the validator of the registrationform
static function checkVoucherandSourceCode ($data, $args) {

// if VoucherNumber or SourceCode is missing, we dont need to check it
if (!$data["VoucherNumber"]) return false;
if (!$data["SourceCode"]) return false;

// To protect MySQL injection (i guess thats what it does ?!)
$VoucherNumber = Convert::raw2sql($data["VoucherNumber"]);
$SourceCode = Convert::raw2sql($data["SourceCode"]);

// check if there is such a CardNumber
$CardNumber = DataObject::get_one("CardNumbers", "VoucherNumber = '".$VoucherNumber."' AND SourceCode = '".$SourceCode."'");
if (!$CardNumber) return false;
else return true;
}
}

?>

Avatar
dross

Community Member, 7 Posts

30 November 2012 at 6:56am

This works great! Thanks a lot for your help!

Avatar
dross

Community Member, 7 Posts

30 November 2012 at 7:55am

Please forgive me as I'm pretty new to SS, but I have one more quick question. I need to update the "Registered" column to "1" in the "CardNumbers" table if validation passes. In my old code it was:
if($count==1){
//
mysql_query ("UPDATE $tbl_name
SET Registered=1
WHERE location_code='$sourcecode' and card_number='$vouchernumber'");
I was thinking something like this but I can't get it to work:

      if( $CardNumber= true ){

      DB::query('UPDATE ' . $CardNumber . ' SET Registered=1');

Avatar
dross

Community Member, 7 Posts

30 November 2012 at 8:32am

i got this to work using:

   // check if there is such a CardNumber
         $CardNumber = DataObject::get_one("CardNumbers", "VoucherNumber = '".$VoucherNumber."' AND SourceCode = '".$SourceCode."' AND Registered = '0'" );
         if (!$CardNumber) return false;
         else return
         
         DB::query("UPDATE CardNumbers
         SET Registered = '1'
         WHERE SourceCode = '$SourceCode' and VoucherNumber = '$VoucherNumber'");   

Go to Top