• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

SOLVED: Validate count of records on submission

Status
Not open for further replies.

dimoss

Well-Known Member
Hi

I post it here because for some reason I cannot post anymore in Standard forums.

I want to validate the count of records based on criteria. To accomplish that I have created a php validation:

PHP:
$db = JFactory::getDBO();
$player = '{fab_entries_na___player_raw}';
$assoc = '{fab_entries_na___assoc_raw}';
$gender = '{fab_entries_na___gender_raw}';
$query = "SELECT count(*) FROM fab_entries_na WHERE player = ". $db->quote($player)." AND gender = ". $db->quote($gender)." AND assoc = ". $db->quote($assoc);
$db->setQuery( $query );
$x = (int)$db->loadResult();
return $x < 5;

With the above I want to limit the number of records to no more than 5 setting the validation to run on 'New' which means during the submission the query calculates the number of records already in the db table and returns true or false for the validation.

My question is how I could run the same validation also on 'Edit'?

Thanks for any help.
 
Not sure why you want to run it on Edit, presumably if this runs on New then there is never more than 5 records in the database. Are you saying you wish to disable edit when there are 5 records?

You can test if you are working on a new record or editing an exiting one by checking the value of the ID element. If null it is New, if not then you are editing an exiting record.
 
I gave an example of a simple validation with only one criteria.
Normally I use multiple 'if' to cover more cases so I want to let the user to change something and select a different value then I have to ensure that the system will prevent that entry on Edit also. See below the full example with more criteria:

PHP:
$db = JFactory::getDBO();
$assoc = $db->Quote('{fab_entries_na___assoc_raw}');
$fun = (int)'{fab_entries_na___function_1_raw}';
$query = "SELECT count(*) FROM fab_entries_na WHERE assoc = $assoc AND (function_1 = $fun OR function_2 = $fun OR function_3 = $fun)";
$db->setQuery( $query );
$x = (int)$db->loadResult();
if ($fun == 2)
{return $x < 2;}
else if ($fun == 19)
{return $x < 1;}
else if ($fun == 20)
{return $x < 1;}
else if ($fun == 3)
{return $x < 1;}
else if ($fun == 4)
{return $x < 1;}
else if ($fun == 5)
{return $x < 2;}
else if ($fun == 6)
{return $x < 1;}
else if ($fun == 7)
{return $x < 1;}
else if ($fun == 8)
{return $x < 2;}
else if ($fun == 9)
{return $x < 6;}
else
{return false;}

What I want to check during Edit is that when the user tries to change the value of {fab_entries_na___function_1_raw}, the system will block him according to the max given numbers.
 
Not sure why you want to run it on Edit, presumably if this runs on New then there is never more than 5 records in the database. Are you saying you wish to disable edit when there are 5 records?

Nope. I don't want to disable Edit. I just want to ensure that even on Edit the system will block according to the given max number records.
 
The validation is also run on edit.
Do you need different counts (e.g <5 on new, <=5 on edit)? I think {rowid} is 0 on new.

There's also a limit form plugin, I don't know if it would be easier to do it there (I didn't use it).
 
The validation is also run on edit.
Do you need different counts (e.g <5 on new, <=5 on edit)? I think {rowid} is 0 on new.

There's also a limit form plugin, I don't know if it would be easier to do it there (I didn't use it).

It can run <=5 on Edit but this won't ensure that the validation will be correct if the user change to a different value.
I haven't tried the limit form plugin.
 
Ok. I think you must exclude the current record in your query, something like AND id<>'{rowid}'
 
I don't think the limit plugin would help. It only runs on load, not submit. And if I understand the requirements here, it's that the user can potentially change a field on the form, which controls how many records they can "own", so has to be run on submit, not load.

Personally I would do this in a form submission plugin, not a validation. Although I guess it doesn't really matter.

And yes, if you need to count on both new and edit, you have to account for the existing row when editing. If you are selecting matches from the table on new, the count won't include the new record being submitted (because it hasn't been written out to the database yet). So as Troester says, you have to exclude the {rowid} from your count, and use <.

-- hugh
 
Sorry to open that thread again but I have a similar situation with another php validation and I cannot find what is wrong
The code is:

PHP:
$db = JFactory::getDBO();
$assoc = '{fab_entries_na___assoc_raw}';
$gender = '{fab_entries_na___gender_raw}';

$query = "SELECT count(*) FROM fab_entries_na WHERE position BETWEEN 21 AND 100 AND sngls = 1 AND gender = ". $db->quote($gender)." AND assoc = ". $db->quote($assoc);
$db->setQuery( $query );
$top100 = (int)$db->loadResult();

$query1 = "SELECT count(*) FROM fab_entries_na WHERE position BETWEEN 1 AND 20 AND sngls = 1 AND gender = ". $db->quote($gender)." AND assoc = ". $db->quote($assoc);
$db->setQuery( $query1 );
$top20 = (int)$db->loadResult();

$query2 = "SELECT count(*) FROM fab_entries_na WHERE sngls = 1 AND gender = ". $db->quote($gender)." AND assoc = ". $db->quote($assoc);
$db->setQuery( $query2 );
$total = (int)$db->loadResult();

if ($top20 == 1 && $top100 == 1) || ($top20 == 1 && $top100 > 1) || ($top20 > 1 && $top100 > 1) || ($top20 == 1 && $top100 > 1) || ($top20 > 1 && $top100 == 1) || ($top20 > 1 && $top100 == 0)
return $total < 6;

The validation runs on NEW and should be ok because at the time of submission at least one condition is TRUE.
However the validation fails.
Maybe I miss something but I don't know what.

Any help is appreciated.
 
I think () is missing around your if conditions
if ( ()||()... )

Correct. I knew that something was missing :)
Now the validation counts the recorded values. How I could make it to count also the submitted value so that the validation runs after?
 
The validation must be before submit (afterwards all is already stored). Add your form element values to the results of your queries.
 
The validation must be before submit (afterwards all is already stored). Add your form element values to the results of your queries.

The form element that controls the whole validation is the 'position' or in other words '{fab_entries_na___position_raw}'. According to the value on this we get the different 'if's' to control the validation.

As the 'position' is a calc element and take values according to the another element in the same form, I don't see how I could add it to my results.
 
Last edited:
@troester You gave me a good idea saying "Add your form element values to the results of your queries".
I did that getting the raw value using:
PHP:
$position = $formModel->getElementData('fab_entries_na___position');
Then I re-arranged the if's adding where needed and I got the result I was looking for!
 
Last edited:
Yes - as I said in Skype yesterday, add the submitted value to the database values.

Also, if you allow editing records, make sure you exclude the current rowid from the query, by adding something like

"WHERE id != " . (int)'{rowid}'

... to the queries.

-- hugh
 
Yes - as I said in Skype yesterday, add the submitted value to the database values.

Also, if you allow editing records, make sure you exclude the current rowid from the query, by adding something like

"WHERE id != " . (int)'{rowid}'

... to the queries.

-- hugh

Thanks Hugh!
 
Status
Not open for further replies.
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top