Form Submit - Validation Error if Records exist?

[Tessa]

Hello!

I have a form like this:

id | tool1 | tool2 | tool3 | tool 4| ...tool35.... | etc
1 | Yes | No | Yes | No
2 | No | Yes | Yes |Yes
..3...
199...
200...

The issue is, when there is 200+ records of different tool sets of possibilities for a ski or snowboard , and a user is trying to search for a record that fits, it is most frequently missed and they end up creating a duplicate record.

Is it possible that they get an error on submit that the entire record (aside from id) is unique? And tell you which record they need to submit? And possibly select that record?

Or am I dreaming?

Thanks in advance!

 

[cheesegrits]

You could up a PHP validation to see if there was a matching existing row. Wouldn't be that hard. But telling them which record they need / loading it would be trickier, from a validation, as validations don't really have a way of feeding back per-failure specific messages / actions. They just spit out the predefined fail message. "A matching record already exists" or whatever.

So I think to do this the way you want, you'd need to do a form submission script. It would do the same basic thing as a validation - grab all the selected values you need to match on from the submitted data, and do a DB query to see if there is a matching row - but it could also do a redirect to an existing row if it finds one (i.e. reload a Fabrik &view=form&rowid=X page), and put up a standard J! info notice (using enqueueueueueueMessage()) saying "These are the droids you are looking for", and let the user re-submit that.

It shouldn't be too hard. Done onBeforeProcess in a PHP form submission script. If you need a hand, we can do a remote desktop session again, and I can point you in the right direction.

-- hugh

 

[Tessa]

I would love that!

I'll send you a skype message asking what times are best for you. =)

 

[cheesegrits]

OK. Well, you know me. I never know when is going to be a good time. So we'll just play our usual Skype tag till we can find some mutually workable time.

-- hugh

 

[cheesegrits]

OK, we got that one going with a form submission script, running onBeforeProcess, on new, which queries the table to see if a matching row exists, and if so, redirects to the form for that row, and puts up an "informative" message.

Paste the code here if you want, help out anyone else doing something similar, and also so we have a record of it.

-- hugh

 

[Tessa]

Thanks again Hugh for making this successful script.

To simply the functionality: "On submit, check if the record exists, if exists, redirect to existing record on edit screen with error message that it exists".

Of course you may have to set it up a bit differently since it's set up more complicated to search and match for brand filter and 34 checkbox fields. =P

Here is the code:

<?php
function doLayupExists() {
    $app = JFactory::getApplication();
    $input = $app->input;
    $brand_id = $input->get('tblfoo___brand_id', '', 'array');
    $brand_id = $brand_id[0];
 
    $sps = array();
    for ($sp = 1; $sp <= 34; $sp++) {
        $sp_val = $input->get('tblfoo___sp' . $sp, '', 'array');
        $sp_val = is_array($sp_val) ? $sp_val[0] : $sp_val;
        if (empty($sp_val)) {
            $sp_val = "(sp" . $sp . " = '[\"\"]' OR sp" . $sp . " = '')";
        }
        else {
            $sp_val = "sp" . $sp . " = '[\"1\"]'";
        }
        $sps[$sp] = $sp_val;
    }
    $sp_where = implode(' AND ', $sps);
    $sp_where .= " AND brand_id = " . $brand_id;
    $db = JFactory::getDbo();
    $query = $db->getQuery(true);
    $query->select('*')->from('tblfoo')->where($sp_where);
 
 
 
    //echo $query;exit;
    //var_dump($sps, $sp_where);exit;
    $db->setQuery($query);
    $row = $db->loadObject();
    if (!empty($row)) {
        $app->enqueueMessage('Dumbass, this one exists');
        $app->redirect("/form/73/" . $row->id);
    }
    else {
        $app->enqueueMessage("Query: " . (string)$query);
    }
}
?>

 

[cheesegrits]

Don't forget to change that message if the row exists, lol!

And for anyone in the peanut gallery ... the only real wrinkle to watch for working with checkboxes is, if there is no selection, in the raw table data you may either get a JSON representation of an empty array, [""], or you may just get an empty field, depending on ... well, I think it's what direction the wind is blowing, or something. or perhaps whether it's a default non-selection, or the user selected and then deselected an option.

Either way, you have to check for both possibilities in your query.

-- hugh

 

[Tessa]

Don't forget to change that message if the row exists, lol!

And for anyone in the peanut gallery ... the only real wrinkle to watch for working with checkboxes is, if there is no selection, in the raw table data you may either get a JSON representation of an empty array, [""], or you may just get an empty field, depending on ... well, I think it's what direction the wind is blowing, or something. or perhaps whether it's a default non-selection, or the user selected and then deselected an option.

Either way, you have to check for both possibilities in your query.

-- hugh

Thanks Hugh.

I'll leave that message to "Dumbass....." If I get fired for that, then oh well. I can do better. =P

 

[cheesegrits]

But then I'd feel guilty, LOL! Actually, I'm lying, I'd laugh my butt off if I got you fired.

-- hugh

 

[Tessa]

HAHahaha =P

 

[felixkat]

LOL, great message. Thanks for posting the code up Tessa