BF data validation with sql query (testing for unique data)

  • easytherm
  • Topic Author
  • Offline
  • Platinum Boarder
  • Platinum Boarder
  • Posts: 397
  • Thank you received: 2

easytherm created the topic: BF data validation with sql query (testing for unique data)

Hi,

I have a field in a BF form wich should be unique. There is no simple rule for setting this field value, so I can't use a simple counter.

I would like to use the standard validation process of bf forms with a custom validation and an error message if the test declares a duplicate value

The datafield which should be manually edited but unique may have as a name "VoucherNumber"

Is there any example you could provide to me for doing this?

best regards

Jacques
#202333
  • Posts: 6478
  • Karma: 170
  • Thank you received: 667

tomeperica replied the topic: BF data validation with sql query (testing for unique data)

Hey there,

AJAx solution would be best option, but I want to know do you have already that value stored in BF tables or somewhere else, or you simply do no want have duplicate values of that field value in your database?

Regads,
Tome

Regards,
Tome
==========================================
Need to renew Pro Support?
Buy Here!
==========================================
Satisfied?
Consider a membership!
==========================================
Like us on Facebook
==========================================
Support Crosstec and get ALL EXTENSIONS and professional support for just $5
Here!
#202377
  • easytherm
  • Topic Author
  • Offline
  • Platinum Boarder
  • Platinum Boarder
  • Posts: 397
  • Thank you received: 2

easytherm replied the topic: BF data validation with sql query (testing for unique data)

I have to enter some data for a travel voucher and these data are stored using BF with a unique tag given by the editor during form access. The correct name for this field is NumBon

The idea is to have a tag such as 2017-09.
It may also be 2017-34BV (freely chosen by the user)

The initscript searches the last NumBon entered (for example 2017-18), splits this data and adds 1 to the last number, for example 2017-19. The user can edit this proposed NumBon and modify it if he wishes. The only restriction is to have a unique NumBon. I could use an index but I lack the knowledge to implement it. It could be another way of doing that.

My idea is simply to check in the database during validation of this field in order to check that the selected value is not allready present.

I hope you catch my thoughts...

best regards

Jacques

Here is the code used in the before form pieces of that form
//Include BreezingForms Library
$this->execPieceByName('ff_InitLib');

//Create database Object
$database =& JFactory::getDBO();

//Get highest ref_number that is already stored in the database
$database->setQuery('SELECT value FROM #__facileforms_subrecords WHERE id=(SELECT max(id) from #__facileforms_subrecords WHERE name = "NumBon")');
$result = $database->LoadResult();

//Split ref_number so we retrieve only the number behind "-"
$number = explode("-", $result);

$numAnnee = $number[0]; //somethink like 2017
$numBon = $number[1]; //somethink like 11
$numBon =str_pad($numBon+1, 2, '0', STR_PAD_LEFT); //somethink like 11

$nextNumber= $numAnnee.'-'.$numBon; //somethink like 2017-12
ff_setValue('NumBon', $nextNumber);
#202386
  • Posts: 6478
  • Karma: 170
  • Thank you received: 667

tomeperica replied the topic: BF data validation with sql query (testing for unique data)

OK I ll try to create only this part with validation process, to be precise, when user input the code into NumBon field and try to submit, then validation will fire and check if that value is already present in database.

I will build basic example of validation on my local host and then send it to you so you can implement in your form, I ll wrote you detailed instructions.

Will notify you when I am done.

Regards,
Tome

Regards,
Tome
==========================================
Need to renew Pro Support?
Buy Here!
==========================================
Satisfied?
Consider a membership!
==========================================
Like us on Facebook
==========================================
Support Crosstec and get ALL EXTENSIONS and professional support for just $5
Here!
#202407
The following user(s) said Thank You: easytherm
  • Posts: 6478
  • Karma: 170
  • Thank you received: 667

tomeperica replied the topic: BF data validation with sql query (testing for unique data)

Hey there,

pls find attached form below, download, unzip it and install via package installer. Form will work out of the box, just enter your desired code and submit. After trying to enter that same code again it will trigger a validation that code exists.

So you have code in NumBon element -> Validation section and in Action Script section:

Validation code:
function ff_num_validation(element, message)
{
   if(ff_valuenotempty(element, message) != '') return 'NumBon code exists already!';
    var myreturn = '';
    JQuery.ajaxSetup({async:false});
    JQuery.post('index.php', { option: 'com_breezingforms', ff_form: ff_processor.form, Itemid: 0, format: 'html', num: element.value }, function(data){console.log(data);    if( data != '1' ){ message == '' ? myreturn = 'NumBon code exists already!' : myreturn = message; } } );
 return myreturn;
} // ff_num_validation

Action Script code:
function ff_num_action(element, action)
{
    switch (action) {
        case 'change':
            JQuery('#bfEmailError').remove();
            if(element.value == '') return;
            var myreturn = '';
            JQuery.ajaxSetup({async:false});
            JQuery.post('index.php', { option: 'com_breezingforms', ff_form: ff_processor.form, Itemid: 0, format: 'html', num: element.value }, function(data){ if( data != '1' ){ myreturn = '<div id="bfEmailError"><p style="display: block; color: red;">NumBon code exists already!</div>'; } else { myreturn = ''; } } );
            if(myreturn != '') { JQuery(element).after(myreturn); } else { JQuery('#bfEmailError').remove(); }
            break;
        default:;
    } // switch
} // ff_num_action

And in Your Form -> Advanced tab -> More options -> Form Pieces -> Before Form -> Custom:
if( JRequest::getVar('num') !== null ){
   while (@ob_get_level() > 0) {
    @ob_end_clean();
   }
   $db = JFactory::getDBO();
   $db->setQuery("Select s.id From #__facileforms_records As r, #__facileforms_subrecords As s Where s.record = r.id And r.form = ".$this->form." And s.`value` = " . $db->Quote(JRequest::getVar('num')));
   if(trim($db->loadResult())){
     echo 0;
   } else {
     echo 1;
   }
   exit;
}

So now you now how to implement it in your form.

Regards,
Tome

Regards,
Tome
==========================================
Need to renew Pro Support?
Buy Here!
==========================================
Satisfied?
Consider a membership!
==========================================
Like us on Facebook
==========================================
Support Crosstec and get ALL EXTENSIONS and professional support for just $5
Here!
#202566
Attachments:
  • easytherm
  • Topic Author
  • Offline
  • Platinum Boarder
  • Platinum Boarder
  • Posts: 397
  • Thank you received: 2

easytherm replied the topic: BF data validation with sql query (testing for unique data)

wow super!!

I test and keep you informed

Jacques
#202568
Moderators: ForumSupporttomeperica
Time to create page: 0.075 seconds

New Icon Packs Category!

Crosstec is now offering icon packs.

If you are a paying subscriber, icon packs are automatically added to your account.

Check out our icon packs page!

Live Support Chat Opened!

Join our Discord chat here to receive live support and talk directly to the team!

Summer Sale!

50% discount on all of our extension subscription plans, templates and icon packs!

Get Your Subscription Here

News and Updates

Get informed about new downloads, updates and more in our News and Updates newsletter.

All Extensions Subscription

Get 1 year access to all of our current and future products and 1 year of professional support -- 99 for just 49! (Summer Sale)

No support per domain or website installation limits! Includes all of our current and future Joomla!® extensions, Joomla!® templates for the duration of your membership. This means, by purchasing an All Extensions Subscription you'll have it all covered!

Get it from here

3rd Party Discount - 25% Off

We help you to keep your costs under control. If you are a new member and purchased a form building tool from a different form vendor, then you'll get a 25% discount on our subscription plans.

How to receive the discount:

Send us a quick email to sales@crosstec.org with a proof of purchase (for example a paypal receipt), await payment instructions and enjoy your membership!

Live Support Chat Opened!

Join our Discord chat here to receive live support and talk directly to the team!

Community Reward

Help us to create new extensions and plugins! With only $5 you help us a lot and get unlimited download access to all of our products, professional support and even more. Get your reward now!

Read More Here