Validating based on multiple conditions?

arcadeicons · Apr 10, 2017

I have two circumstances that I need to validate submissions that require checking multiple conditions. I'm using the validation 'greater or less than' plugin but I'm confused, is the validation to fail or succeed. e.g. IF A == B then ok to submit? or fail?

Situation 1) I need to replace a player's current score for a game in a tournament at a specific location, only if their new score is higher than the currently logged/or no existing, score for that player. So I need to validate something like this...

IF {tournID} = submit_score___tournID or NULL
AND {eventID} = submit_score___eventID or NULL
AND {locID} = submit_score___locID or NULL
AND {gameID} = submit_score___gameID or NULL
AND {userID} = submit_score___userID or NULL
AND newscore > submit_score___gamescore or NULL
THEN submit and overwrite submit_score___gamescore with newscore

else message "You already have a higher score for {Game Name} at {Tournament Name}, {Location Name}

Situation 2) Some tournaments are 'Public with invite only' meaning a tournament manager will hand out an invite code for people to use when they register. Other tournaments are 'Public - Open' with no code needed.

People use the same form to register for all tournaments so I need to validate on

IF add_tournament___invite_code is NULL
OR invite_code = add_tournament___invite_code
THEN submit

else message 'Incorrect invitation code'

cheesegrits · Apr 10, 2017

You probably need the PHP validation plugin. Form data will be in $formModel->formData (actually I think we add a $formData as shorthand before eval'ing your code).

I'm not sure on your logic in the pseudo-code. When testing things like "IF {tournID} = submit_score___tournID or NULL", are you wanting to test that the submitted tourID has been changed? ie. comparing submitted value with original (prior to edit) value? So you allow people to change things like tournament ID when editing? Or do you not care if it's changed, you just want to look at existing data in the table for the submitted values.

I think to do this, you'll need to come up with a query on existing data.

I'm assuming you will have validations on all those inputs (location, game, tournament, etc) so they'll have values?

Anyway, your query would look like ...

PHP:

// get score
$gameScore = $formModel->formData['submit_score___gamescore'];
// get joins, they'll probably be arrays, so have to check for that
$tournID = $formModel->formData['submit_score___tournID_raw'];
$tournID = is_array($tourID) ? $tourID[0] : $tourID;
// etc ... get rest of variables here

// build a query to find any existing rows with higher score
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery
   ->select('*')
   ->from('submit_score')
   ->where('tournID = ' . $myDb->quote($tournID))
   // ... etc insert rest of where clauses for variables
   ->where('gamescore > ' . $gameScore);
$myDb->setQuery($myQuery);
$scores = $myDb->loadObjectList();

// returns false if we found any rows
return empty($scores);

In the second one, for the invite code, I don't understand what you are testing the code against. Typically with "codes" you'll have a separate table with valid codes in it, or some algorithm for validating (hashing things like tournament and date against the wind direction and your mothers maiden name).

-- hugh

arcadeicons · Apr 10, 2017

cheesegrits said:
You probably need the PHP validation plugin. Form data will be in $formModel->formData (actually I think we add a $formData as shorthand before eval'ing your code).

I'm not sure on your logic in the pseudo-code. When testing things like "IF {tournID} = submit_score___tournID or NULL", are you wanting to test that the submitted tourID has been changed? ie. comparing submitted value with original (prior to edit) value? So you allow people to change things like tournament ID when editing? Or do you not care if it's changed, you just want to look at existing data in the table for the submitted values.

I need to keep only a player's single highest score on a game {gameID} in a tournament {tournID} during an event {eventID} at a location {locID}.

The form already has eventID and tournID pre-populated in hidden fields. UserID and gameID get chosen in drop downs and then the score entered in a field. I have to go this granular as the scoreboard needs to track a player's high score on the same game, but at a different location, or maybe even the same game at the same location but NOT during a tournament.

cheesegrits said:
In the second one, for the invite code, I don't understand what you are testing the code against. Typically with "codes" you'll have a separate table with valid codes in it, or some algorithm for validating (hashing things like tournament and date against the wind direction and your mothers maiden name).

The tournament creator just makes a single tournament invite code for that tournament (doesn't need to be per user, just needs to stop a random user accidentally signing up for a closed event). This code is stored in the invite_code column of the add_tournament table. So my rule just needs to check if the tournament being signed up for has a code in this column, if so what is it? Public tournaments that don't need invite codes will have NULL in that field.

cheesegrits · Apr 11, 2017

Yup, just fashion that score info into a query. The code I gave you should be a good start.

Sent from my HTC6545LVW using Tapatalk

arcadeicons · Apr 13, 2017

Well, it nearly works, just in the wrong direction, it only fails validation if my score is higher than one already posted.

PHP:

// get score
$gameScore = $formModel->formData['submit_score___gamescore'];
// get joins, they'll probably be arrays, so have to check for that
$tournID = $formModel->formData['submit_score___tournID_raw'];
$tournID = is_array($tournID) ? $tournID[0] : $tournID;
$eventID = $formModel->formData['submit_score___eventID_raw'];
$eventID = is_array($eventID) ? $eventID[0] : $eventID;
$gameID = $formModel->formData['submit_score___gameID_raw'];
$gameID = is_array($gameID) ? $gameID[0] : $gameID;
$locID = $formModel->formData['submit_score___collectiontype_raw'];
$locID = is_array($locID) ? $locID[0] : $locID;
$playerID = $formModel->formData['submit_score___userID_raw'];
$playerID = is_array($playerID) ? $playerID[0] : $playerID;

// build a query to find any existing rows with higher score
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery
   ->select('*')
   ->from('submit_score')
   ->where('tournID = ' . $myDb->quote($tournID))
   ->where('eventID = ' . $myDb->quote($eventID))
   ->where('gameID = ' . $myDb->quote($gameID))
   ->where('collectiontype = ' . $myDb->quote($locID))
   ->where('userID = ' . $myDb->quote($playerID))
   ->where('gamescore > ' . $gameScore);
$myDb->setQuery($myQuery);
$scores = $myDb->loadObjectList();

// returns false if we found any rows
return empty($scores);

cheesegrits · Apr 13, 2017

Nope, nothing baked in. You'll have to write a little php form submission script, running onAfterProcess. Same query as the validation, but search for anything with lower score, and delete whatever you find.

Also, on after process, need to use formDataWithTableName instead of formData for the posted values.

Sent from my HTC6545LVW using Tapatalk

arcadeicons · Apr 13, 2017

I think you responded to one of my deleted posts. I thought it was working but it's still only validating in the wrong direction. Doesn't the PHP code need to return a true value rather than a false one in this situation?

cheesegrits · Apr 13, 2017

For the validation code, the logic is ...

Select any rows for that game with a higher score.

Return true if the resulting selection is empty (no higher scores), false if it is not empty (there was a higher score).

-- hugh

arcadeicons · Apr 18, 2017

Yep the php select works perfectly, but even when it returns a false on finding entries with all values matching and higher scores, the form still posts, my other settings are attached. I can't see why this wouldn't work.

cheesegrits · Apr 18, 2017

Is this in a popup form? I committed a fix this week for a situation where forms submitted through Ajax (pop-ups, content plugin) were sometimes incorrectly submitting even when a validation failed.

Sent from my HTC6545LVW using Tapatalk

arcadeicons · Apr 18, 2017

No this is a standard form, nothing fancy about it

arcadeicons · Apr 18, 2017

the way it is working right now, if I post a score higher than an existing one with all other factors matching the form fails showing my error message 'You already have a higher score on this machine.' so it's basically working backwards right now

arcadeicons · Apr 18, 2017

NM, I am an idiot....don't ask. Working as it should. /facepalm

arcadeicons · Apr 18, 2017

Now my submission check is working I'm trying to write the afterprocess delete php. I have this, but it doesn't seem to delete the lower score entries.

Code:

// get score
$gameScore = $formModel->formDataWithTableName['submit_score___gamescore'];
// get joins, they'll probably be arrays, so have to check for that
$tournID = $formModel->formDataWithTableName['submit_score___tournID_raw'];
$tournID = is_array($tournID) ? $tournID[0] : $tournID;
$eventID = $formModel->formDataWithTableName['submit_score___eventID_raw'];
$eventID = is_array($eventID) ? $eventID[0] : $eventID;
$gameID = $formModel->formDataWithTableName['submit_score___gameID_raw'];
$gameID = is_array($gameID) ? $gameID[0] : $gameID;
$locID = $formModel->formDataWithTableName['submit_score___collectiontype_raw'];
$locID = is_array($locID) ? $locID[0] : $locID;
$playerID = $formModel->formDataWithTableName['submit_score___userID_raw'];
$playerID = is_array($playerID) ? $playerID[0] : $playerID;

// build a query to find any existing rows with higher score
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$conditions = array(
$db->quoteName('tournID = ' . $myDb->quote($tournID))
$db->quoteName('eventID = ' . $myDb->quote($eventID))
$db->quoteName('gameID = ' . $myDb->quote($gameID))
$db->quoteName('collectiontype = ' . $myDb->quote($locID))
$db->quoteName('userID = ' . $myDb->quote($playerID))
$db->quoteName('gamescore < ' . $gameScore)
);
$query->delete($db->quoteName('submit_score'))->where($conditions);

$db->setQuery($query);
$result = $db->query();

arcadeicons · Apr 19, 2017

went through and cleaned up the code and tried to make everything in line with Joomla formatting, but this delete script still isn't firing...

Code:

// get score
$gameScore = $formModel->formDataWithTableName['submit_score___gamescore'];
$gameScore = is_array($gameScore) ? $gameScore[0] : $gameScore;
$tournID = $formModel->formDataWithTableName['submit_score___tournID_raw'];
$tournID = is_array($tournID) ? $tournID[0] : $tournID;
$eventID = $formModel->formDataWithTableName['submit_score___eventID_raw'];
$eventID = is_array($eventID) ? $eventID[0] : $eventID;
$gameID = $formModel->formDataWithTableName['submit_score___gameID_raw'];
$gameID = is_array($gameID) ? $gameID[0] : $gameID;
$locID = $formModel->formDataWithTableName['submit_score___collectiontype_raw'];
$locID = is_array($locID) ? $locID[0] : $locID;
$playerID = $formModel->formDataWithTableName['submit_score___userID_raw'];
$playerID = is_array($playerID) ? $playerID[0] : $playerID;

// build a query to find any existing rows with lower scores
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$conditions = array(
$db->quoteName('tournID') . '=' . ('$tournID')
$db->quoteName('eventID') . '=' . ('$eventID')
$db->quoteName('gameID') . '=' . ('$gameID')
$db->quoteName('collectiontype') . '=' . ('$locID')
$db->quoteName('userID') . '=' . ('$playerID')
$db->quoteName('gamescore') . '<' . ('$gameScore')
);

//delete any rows found that match conditions
$query->delete($db->quoteName('submit_score'));
$query->where($conditions);


$db->setQuery($query);
$result = $db->execute();

echo($query->__toString());

cheesegrits · Apr 19, 2017

Your conditions array looks wrong, try ....

Code:

$conditions = array(
$db->quoteName('tournID') . ' = ' . $db->quote($tournID),
$db->quoteName('eventID') . ' = ' . $db->quote($eventID),
$db->quoteName('gameID') . ' = ' . $db->quote($gameID),
$db->quoteName('collectiontype') . ' = ' . $db->quote($locID),
$db->quoteName('userID') . ' = ' . $db->quote($playerID),
$db->quoteName('gamescore') . ' < ' . $gameScore
);

And to see the query, dump it and exit before executing.

Code:

var_dump((string) $query); exit;
$result = $db->execute();

Using 'echo' in a submission won't work, 'cos there's an implicit redirect after submitting, so nothing echo'ed during submission will make it to the browser. So you need to dump and exit.

Also, if there's an error in the query, echo'ing it after executing won't work, as the error will usually throw and exception.

Also, it's good to be in the habit of using something other than $db and $query in any code you write that gets eval'ed, as those are commonly used inside Fabrik, and it's possible to step on our variables. So use something like $myDb and $myQuery. I think it'll be OK in this context, but it's just a good habit to be in.

-- hugh

arcadeicons · Apr 19, 2017

Ok with this code...

Code:

// get score
$gameScore = $formModel->formDataWithTableName['submit_score___gamescore'];
$gameScore = is_array($gameScore) ? $gameScore[0] : $gameScore;
$tournID = $formModel->formDataWithTableName['submit_score___tournID_raw'];
$tournID = is_array($tournID) ? $tournID[0] : $tournID;
$eventID = $formModel->formDataWithTableName['submit_score___eventID_raw'];
$eventID = is_array($eventID) ? $eventID[0] : $eventID;
$gameID = $formModel->formDataWithTableName['submit_score___gameID_raw'];
$gameID = is_array($gameID) ? $gameID[0] : $gameID;
$locID = $formModel->formDataWithTableName['submit_score___collectiontype_raw'];
$locID = is_array($locID) ? $locID[0] : $locID;
$playerID = $formModel->formDataWithTableName['submit_score___userID_raw'];
$playerID = is_array($playerID) ? $playerID[0] : $playerID;

// build a query to find any existing rows with lower scores
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$conditions = array(
$myDb->quoteName('tournID') . ' = ' . $myDb->quote($tournID),
$myDb->quoteName('eventID') . ' = ' . $myDb->quote($eventID),
$myDb->quoteName('gameID') . ' = ' . $myDb->quote($gameID),
$myDb->quoteName('collectiontype') . ' = ' . $myDb->quote($locID),
$myDb->quoteName('userID') . ' = ' . $myDb->quote($playerID),
$myDb->quoteName('gamescore') . ' < ' . $gameScore
);

var_dump((string) $myQuery); exit;
$result = $myDb->execute();

I get a result string(0) ""

arcadeicons · Apr 19, 2017

dumping the values from the form model shows they are working properly...

string(5) "14100"
string(1) "2"
string(1) "2"
string(2) "23"
string(1) "5"
string(3) "633"

must be something with the conditions syntax

arcadeicons · Apr 19, 2017

got it!! Working with this....

Code:

// get score
$gameScore = $formModel->formDataWithTableName['submit_score___gamescore'];
$gameScore = is_array($gameScore) ? $gameScore[0] : $gameScore;
$tournID = $formModel->formDataWithTableName['submit_score___tournID_raw'];
$tournID = is_array($tournID) ? $tournID[0] : $tournID;
$eventID = $formModel->formDataWithTableName['submit_score___eventID_raw'];
$eventID = is_array($eventID) ? $eventID[0] : $eventID;
$gameID = $formModel->formDataWithTableName['submit_score___gameID_raw'];
$gameID = is_array($gameID) ? $gameID[0] : $gameID;
$locID = $formModel->formDataWithTableName['submit_score___collectiontype_raw'];
$locID = is_array($locID) ? $locID[0] : $locID;
$playerID = $formModel->formDataWithTableName['submit_score___userID_raw'];
$playerID = is_array($playerID) ? $playerID[0] : $playerID;

// build a query to find any existing rows with lower scores
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$conditions = array(
$myDb->quoteName('tournID') . ' = ' . $myDb->quote($tournID),
$myDb->quoteName('eventID') . ' = ' . $myDb->quote($eventID),
$myDb->quoteName('gameID') . ' = ' . $myDb->quote($gameID),
$myDb->quoteName('collectiontype') . ' = ' . $myDb->quote($locID),
$myDb->quoteName('userID') . ' = ' . $myDb->quote($playerID),
$myDb->quoteName('gamescore') . ' < ' . $gameScore
);

$myQuery->delete($myDb->quoteName('submit_score'));
$myQuery->where($conditions);

$myDb->setQuery($myQuery);

$result = $myDb->execute();

arcadeicons · Apr 20, 2017

Also, regarding the invite code. I used this PHP script on the 'invite code' field element to validate an invite code required to register for a specific tournament. Putting it here in case it helps someone else...

Code:

// get invite code
$inviteCode = $formModel->formData[add_tournament_repeat_guests___invite_code];
// get joins, they'll probably be arrays, so have to check for that
$tournID = $formModel->formData[add_tournament_repeat_guests___parent_id_raw];
$tournID = is_array($tournID) ? $tournID[0] : $tournID;

// build a query to find any existing tournaments with same invite code
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery
   ->select('invite_code')
   ->from('add_tournament')
   ->where('tournID = ' . $myDb->quote($tournID))
   ->where('invite_code = ' . $myDb->quote($inviteCode));

$myDb->setQuery($myQuery);
$codes = $myDb->loadObjectList();

// returns false if we found any rows
return empty($codes);

Validating based on multiple conditions?

Member

Support Gopher

Member

Support Gopher

Member

Attachments

Support Gopher

Member

Support Gopher

Member

Attachments

Support Gopher

Member

Member

Member

Member

Member

Support Gopher

Member

Member

Member

Member

Useful links