1. Fabrik 3.9 has been released. If you have updated Joomla to 3.9, this is a required update.
    Dismiss Notice

SOLVED: Validate count of records on submission

Discussion in 'Community' started by dimoss, Nov 23, 2018.

Thread Status:
Not open for further replies.
  1. dimoss

    dimoss Well-Known Member

    Level: Community
    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.
     
  2. achartier

    achartier Active Member

    Level: Community
    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.
     
  3. dimoss

    dimoss Well-Known Member

    Level: Community
    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.
     
  4. dimoss

    dimoss Well-Known Member

    Level: Community
    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.
     
  5. troester

    troester Well-Known Member Staff Member

    Level: Community
    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).
     
  6. dimoss

    dimoss Well-Known Member

    Level: Community
    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.
     
  7. troester

    troester Well-Known Member Staff Member

    Level: Community
    Ok. I think you must exclude the current record in your query, something like AND id<>'{rowid}'
     
    dimoss likes this.
  8. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    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
     
    dimoss likes this.
  9. dimoss

    dimoss Well-Known Member

    Level: Community
    Thank you both :)
    Yes, I think that excluding the {rowid} will make the trick.
     
  10. dimoss

    dimoss Well-Known Member

    Level: Community
    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.
     
  11. troester

    troester Well-Known Member Staff Member

    Level: Community
    I think () is missing around your if conditions
    if ( ()||()... )
     
    dimoss likes this.
  12. dimoss

    dimoss Well-Known Member

    Level: Community
    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?
     
  13. troester

    troester Well-Known Member Staff Member

    Level: Community
    The validation must be before submit (afterwards all is already stored). Add your form element values to the results of your queries.
     
    dimoss likes this.
  14. dimoss

    dimoss Well-Known Member

    Level: Community
    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: Dec 8, 2018 at 8:13 AM
  15. dimoss

    dimoss Well-Known Member

    Level: Community
    @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: Dec 8, 2018 at 5:11 PM
  16. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    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
     
    dimoss likes this.
  17. dimoss

    dimoss Well-Known Member

    Level: Community
    Thanks Hugh!
     
Thread Status:
Not open for further replies.

Share This Page