Jump to:

2012 Posts in 1438 Topics by 621 members

Form Questions

SilverStripe Forums » Form Questions » Validate form fields against databse

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Page: 1 2
Go to End
Author Topic: 1381 Views
  • dross
    Avatar
    Community Member
    7 Posts

    Validate form fields against databse Link to this post

    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.

  • lx
    Avatar
    Community Member
    83 Posts

    Re: Validate form fields against databse Link to this post

    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 http://netefx.de/Silverstripe-NetefxValidator.php 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.

  • dross
    Avatar
    Community Member
    7 Posts

    Re: Validate form fields against databse Link to this post

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

  • dross
    Avatar
    Community Member
    7 Posts

    Re: Validate form fields against databse Link to this post

    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";
          }

  • lx
    Avatar
    Community Member
    83 Posts

    Re: Validate form fields against databse Link to this post

    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;
    }
    }

    ?>

  • dross
    Avatar
    Community Member
    7 Posts

    Re: Validate form fields against databse Link to this post

    This works great! Thanks a lot for your help!

  • dross
    Avatar
    Community Member
    7 Posts

    Re: Validate form fields against databse Link to this post

    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');

  • dross
    Avatar
    Community Member
    7 Posts

    Re: Validate form fields against databse Link to this post

    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'");   

    1381 Views
Page: 1 2
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.