[SOLVED] Can calc element ajax be used in Where clause of dbjoin

T

trb

Member
Hi,

I have a field element on my form to enter a date in the form mm/dd/yyyy
I use a calc element (ajax enabled) to watch this date field and calculate the users age based on the date entered.
All works fine.

Now, I need to use this calculated age as one of the values in a WHERE clause on a dbjoin element dropdown on the same form.

It seems when the ajax fires which calculates the age in the calc element, the result of the calculation is not seen by the dbjoin element and considered in the where condition.

I use two other field element placeholders in this where clause which work fine in the WHere clause of the dbjoin, but the calc element does not work.

Is it possible to use the calc element in the where clause and if so is the placeholder syntax any different from a field element placeholder??? I've tries with and without _raw but with no luck.

Much thanks.
 
Did you enable Ajax update in the dbjoin?

But I don't think it will do.
You can't use a calc element in a calc element (it's not determined which calculation is running first) and an ajax update of the Data-where is similar.
 
Hi @troester,

Thank you for your reply. I was afraid that might not be possible.

Can you offer a suggestion on how I might go about doing this?

I have a form with the following elements:
- a dbjoin with ajax enabled rendered as a dropdown with options of my retail store products
- field1 (season)
- field2 (pay)
- field3 --> user enters date (dob)
- calc element watching field3 which calculates user age based on the date entered (age)

now in my dbjoin dropdown, I need to limit the product options which are displayed based on field1, field2, and the age which is calculated in the calc element from the date entered in field3

so the WHERE clause in my dbjoin element looks like this:
where {thistable}.season = '{jos_products_17_repeat___season_raw}' AND {thistable}.pay = '{jos_products_17_repeat___pay_raw}' AND {thistable}.age = '{jos_products_17_repeat___age_raw}'

It seems that last condition in the where clause is not possible since it is the result of a calc element calculation and updated via ajax.

How else might I accomplish this? I need the calculated age to dynamically adjust the options available in the dropdown on the form in conjunction with the inputted values of the other two fields on the form???

Thanks for your help...
 
You can try calculating the age in your WHERE clause like:

AND {thistable}.age = TIMESTAMPDIFF(YEAR, '{jos_products_17_repeat___dob_raw}', CURDATE())
 
Hi @juuser,

Thank you for your suggestion. My age calculation is not straightforward unfortunately. If the month of the date entered is August or less (ie 8 or less) then the age is calculated as you suggest. But if the month of the date entered is September or greater (ie 9 or more) then we need to subtract 1 year from the calculated age. For example here is the code I currently use in the calc element

$exp=explode('/','{jos_multiple_registrations_17_repeat___a_dob}');
$date=2021;
$mm=$exp[0];
$dd=$exp[1];
$yy=$exp[2];

if($mm<=8)
{
$age=$date-$yy;
}

if($mm>8)
{
$age=($date-$yy)-1;
}
return $age;

I could add a button to the form and have the user enter the date of birth and then click the button and I can then do the calculation on the server.
I would then need to write the calculated age to the age element on the form. Can this be done? How to write the calculated age value to the form element?

Thanks for your help....
 
You should be able to use userAjax to do the calculation and update the age field. You can add element javascript to your "dob" element's blur event and the code something like:
Code: 
var dob = this.form.formElements.get('yourtable___dob').getValue();

jQuery.ajax({
    url: 'index.php?option=com_fabrik&format=raw&task=plugin.userAjax&method=CalculateAge&userdob='+dob,
    method: 'get',
    context: this
}).done(function (myresult) {
   var UsersAgeEl = this.form.formElements.get('yourtable___age');
   UsersAgeEl.update(myresult);
});

and in Fabrik's user_ajax.php add function like:
Code: 
public function CalculateAge() {

   $app = JFactory::getApplication();
   $input = $app->input;
   $userdob = $input->get('userdob', '');
 
   $exp=explode('/',$userdob);
   $date=2021;
   $mm=$exp[0];
   $dd=$exp[1];
   $yy=$exp[2];

   if($mm<=8) {
     $age=$date-$yy;
   }

   if($mm>8) {
     $age=($date-$yy)-1;
   }

   echo $age;
}

Just a suggestion that you would be much more on a safe side if you would use jdate eleement and let users pick the date from popup-calendar. Manually added dates are just so error prone.

P.S. this exact code is not tested, so I'm not sure whether it triggers dbjoin element's ajax. Might need some extra adjustement.
 
Last edited:
Hi @juuser,

THANK YOU so much for this example code... I truly appreciate your help. I will give it a try and report back when I have a solution in hand.

Much appreciated!
 
Hi @juuser,

I have tried your approach and have been attempting to get it to work...but with no luck. Here is what I have done:

- added the JS code you provided to the dob elements Javascript , published it, and set the event to blur
- added the PHP code to user_ajax.php file.

When I enter a date in the dob field , I do not detect any ajax firing (using chrome developer tools) and nothing is written to the age element field.
So, I suspect my issue is with the javascript added to the dob element. I have tried inserting an alert into the javascript using
}).done(function (myresult){
alert(myresult);
});
but nothing happens.

I also tried adding these two lines to the CalculateAge() function: (not sure if they are needed but found them while researching other posts)
$db = FabrikWorker::getDbo();
$query = $db->getQuery(true);
but this did not help either.

So I believe the problem is that the javascript/Ajax is not firing from the dob element

Any suggestions on how I might troubleshot this further?

Thank you...
 
Start troubleshooting step by step:

1) See if the dob elemenent's blur event fires and var dob has prober value, so add console.log("dob "+dob); after the "var dob" line. Now lose the focus from dob field and see what you get in browser console.
2) Next, see if the ajax url call gets done and the url with the parameter is correct. Check browser console-> Network -> XHR and blur the dob field. You should see the ajax url with dob parameter.
3) If 1-2 is ok, check that you get the dob parameter correctly in user_ajax.php. Add echo $userdob; right after the line $userdob = $input->get('userdob', ''); and then in element javascript add console.log(myresult); right after }).done(function (myresult) { line. And check again do you have the correct dob value returned from ajax call.
4) If so far so good, you must have a problem in your age calculation or in the element name which you update in ajax done function.

P.S. you don't need the $db-stuff as you are not making any database queries in user_ajax.php.
 
In MySql there is also an IF function, try something like

{thistable}.age = IF(MONTH('{jos_products_17_repeat___dob_raw}')<9, TIMESTAMPDIFF(YEAR, '{jos_products_17_repeat___dob_raw}', CURDATE()),some-date-add-function)
 
Good point. I think in that case it's necessary to have the birthdate in YYYY-MM-DD format which would be a good idea anyway to use date element instead of field.
troester said:
In MySql there is also an IF function, try something like

{thistable}.age = IF(MONTH('{jos_products_17_repeat___dob_raw}')<9, TIMESTAMPDIFF(YEAR, '{jos_products_17_repeat___dob_raw}', CURDATE()),some-date-add-function)
Click to expand...
 
Hi @juuser,

Thanks for the troubleshooting steps. The issue is with the first line of the javascript. For some reason it is not reading the element value int to the dob variable.

Here is what I have in the inline javascript for the dob field element:

var dob = this.form.formElements.get('jos_multiple_registrations_17_repeat___dob').getValue();
console.log("dob"+dob);

I removed the rest of the javascript while debugging.

blur event is set and when enter a date and then lose focus on the element, I see the following error in my console:

VM8198:1 Uncaught TypeError: Cannot read property 'getValue' of null
at eval (eval at <anonymous> (element.js:3), <anonymous>:1:87)
at Object.<anonymous> (element.js:3)
at HTMLInputElement.m (mootools-core.js?23c…504b7594b44b860:119)
eval @ VM8198:1 (anonymous) @ element.js:3
m @ mootools-core.js?23c…504b7594b44b860:119

I've tried adding _raw to the element name. I've tried changing the element from a field to a date type element.
Both produce the same error.

Any ideas?

Thank you....
 
What happens when you change it to:

var dobEl = Fabrik.getBlock('form_xx').formElements.get('table___element');

dob = dobEl.get('value');

Of course change the element name and replace xx with your form id. Do not use raw in element name.
 
var dobEl = Fabrik.getBlock('form_16').formElements.get('jos_multiple_registrations_17_repeat___dob');
dob = dobEl.get('value');

Still get the same result .... ugh!
 
Argh, didn't notice this is a repeat group element. So try:

var i = this.getRepeatNum();

var dobEl = Fabrik.getBlock('form_16').formElements.get('jos_multiple_registrations_17_repeat___dob_'+i);
dob = dobEl.get('value');

PS. I'm not at my pc, so i cannot check and test properly.
 
Almost there!!! Definitely making progress !!! Thank you,

The console error is gone and I see the date entered in the dob field written in the console.
The age field also gets a value written to it on the form, however it is not being calculated correctly.
I do see the ajax URL in my console XHR and it is correct with the data appended to the end of the URL.

So my issue is now why is the age calculation not correct when done using user_ajax.php , yet it is correct if done using a calc element on the form set to ajax update and watchin the dob field ???
2020 is always written to the age field regardless of the date entered in the DOB field, However, I added a calc element to the form using the same age code that is in user_ajax.php and the calc element correctly displays the age ???

Here is the updated inline javascript in the dob field:

var i = this.getRepeatNum();
var dobEl = Fabrik.getBlock('form_16').formElements.get('jos_multiple_registrations_17_repeat___dob_'+i);
dob = dobEl.get('value');
console.log("dob= "+dob);
jQuery.ajax({
url: 'index.php?option=com_fabrik&format=raw&task=plugin.userAjax&method=CalculateAge&userdob='+dob,
method: 'get',
context: this
}).done(function (myresult) {
var UsersAgeEl = this.form.formElements.get('jos_multiple_registrations_17_repeat___age_'+i);
UsersAgeEl.update(myresult);
});

And here is the PHP in user_ajax.php (not calculating correctly)
class UserAjax
{
public function CalculateAge() {
$app = JFactory::getApplication();
$input = $app->input;
$userdob = $input->get('userdob', '');
$exp=explode('/',$userdob);
$date=2021;
$mm=$exp[0];
$dd=$exp[1];
$yy=$exp[2];
if($mm<=8) {
$age=$date-$yy;
}
if($mm>8) {
$age=($date-$yy)-1;
}
echo $age;
}

And here is how the php looks in the calculation of the calc element: (claculates correctly)
$exp=explode('/','{jos_multiple_registrations_17_repeat___dob}');
$date=2021;
$mm=$exp[0];
$dd=$exp[1];
$yy=$exp[2];
if($mm<=8)
{
$age=$date-$yy;
}
if($mm>8)
{
$age=($date-$yy)-1;
}
return $age;
 
Hi @juuser,

So the last issue was that while the ajax URL was being built correctly, for example the dob was appended as 05/03/2013, but when it was read into $userdob, the forward slashes had been removed and $userdob contained 05032013 and my age calculation was looking to explode the string on those forward slashes '/' which were not present.

I adjusted the php to parse the string differently as below and the age is now calculated correctly and updates the age field on the form!
$userdob = $input->get('userdob', '');
$exp = str_split($userdob,2);
$date=21;
$mm=$exp[0];
$dd=$exp[1];
$yy=$exp[3];

Now, the database join element shows the correct options in the dropdown considering the age!

THANK YOU SO MUCH for all your help!

However, I now have a workflow issue in that, if the user goes back and changes the date of birth (dob), a new age is calculated correctly and populates the age element, however it has no effect on the dbjoin dropdown options. I tried adding a condition in the WHERE clause of the dbjoin to check that the dob field was not empty , thinking it would now also watch that field as well and update the dropdown list should it change (along with the age field), but that does not seem to be the case. I will try a few more ideas and see if I can find a solution and if not I may open another thread for this one.... :)

Thanks again!!!!
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 64 (members: 2, guests: 62)
    Back
    Top