Xataface Forums

I have an email address field that is marked as a primary key in mysql and by definition should be unique. The problem is that if you create a new record in Xataface and use an email address value that is already in the database it updates the original record with that email address. It should throw an error warning to the user that "This email address is already in use."

I found a lot of old posts on this topic suggesting that validation with php is the answer, but could not find any documentation on how to do that. I found what looked like a good link in a 2008 post but the link is now broken.

I found the custom validation documentation here: http://xataface.com/documentation/how-t ... validation

I have attempted a test method in my table's Delegate Class:

Code: Select all

  // This is to check and see if the email address is already in use.
  function full_email__validate( &$record, $value, $params=array()){
        $result = stristr($value, 'z');
        if ( $result == false ){
            $params['message'] = "What's up doc?.";
            return false;
        } else {
            // email not in use.
            return true;
        }
    }

The test method simply checks for a "z" character in the email address, and it seems to work. The problem is that my $params['message'] = "What's up doc?."; is not used and on submit the form responds with "Permission Denied" beside the Email input field.

I have it working. I'm not sure it's the best approach.

Code: Select all

class tables_educator {
  // This is to check and see if the email address is already in use.
  function full_email__validate( &$record, $value, &$message){

        $query = "SELECT * FROM educator WHERE full_email = '$value'";
        $result = mysql_query( $query);
        $num_rows = mysql_num_rows($result);

        if ( $num_rows > 0 ){
            $message['message'] = "You have used an email that's already taken.";

            return false;
        } else {
            // email not in use.
            return true;
        }
    }
}

Good workaround. Certainly the fact that it is overwriting an existing record from the new record form is a bug. I have filed it at
http://bugs.weblite.ca/view.php?id=1197

Glad to hear it was a bug. I thought the bug was in my brain.

It seems like my work around effects editing an existing record in a bad way. It blocks saving an edited record showing a duplicate email error. Both "New record" and "edit" must use the same validation function. Any thoughts as to when this bug might be fixed?

If you do your validation inside one of beforeSave(), beforeInsert(), beforeUpdate(), beforeAddRelatedRecord(), beforeAddNewRelatedRecord(), or beforeAddExistingRelatedRecord(), you will have better precision of when it is called. __validate() is called on every form (edit, new, add related, etc...)

-Steve

I don't know whether it's me or more of the same bug but the beforInsert() method seems to act as though we are doing an update. If the email address is found it just treats it like an update of that record, but if the email is not found then it treats it like an insert. Maybe I'm doing something wrong in my beforeInsert() declaration.

Ahh.. yes. Because the email field is your primary key, it treats it as an update if you insert with the same email address. Can you change the database structure so that you have a separate autoincrementing primary key, and just change the email address column to a unique key?

-Steve

This project is a port from zoho.com and they had no unique. It was just exported with cvs and had no primary autoincrement field which is not how I would have set it up. There are about 40,000 records in there. I've never added a field like that to a live database, but am sure it can probably be done. Any quick tips would be appreciated meanwhile I'll research it.

1. Delete the current primary key index (that is on the email field)... don't delete the email field, just the index.
2. Add a new field as the primary key field with type INT(11) auto_increment PRIMARY KEY
3. Add a unique index to the email field.

Thanks Steve! That works fine and now the good news it appears that Xataface is now enforcing unique fields as it's supposed to. That's without beforeInsert() or full_email__validate() being used.

Could be there was no bug after all, and just that the database needed to be compliant with Xataface requirements.

Thanks for the update. I think it is still a bug that should be fixed. I.e. if you add a new record with the same primary key as an existing record, it *should* throw an error rather than edit that existing record.