Enforce Unique Field

A place for users and developers of the Xataface to discuss and receive support.

Enforce Unique Field

Postby mermentau » Wed Nov 28, 2012 8:26 pm

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.
mermentau
 
Posts: 14
Joined: Tue Nov 20, 2012 9:26 am

Re: Enforce Unique Field

Postby mermentau » Thu Nov 29, 2012 8:16 am

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.
mermentau
 
Posts: 14
Joined: Tue Nov 20, 2012 9:26 am

Re: Enforce Unique Field

Postby mermentau » Thu Nov 29, 2012 8:43 pm

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;
        }
    }
}
mermentau
 
Posts: 14
Joined: Tue Nov 20, 2012 9:26 am

Re: Enforce Unique Field

Postby shannah » Fri Nov 30, 2012 4:58 am

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
--
Steve Hannah
@shannah78 (on twitter)
sjhannah.com blog
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Enforce Unique Field

Postby mermentau » Fri Nov 30, 2012 3:34 pm

Glad to hear it was a bug. I thought the bug was in my brain. :D
mermentau
 
Posts: 14
Joined: Tue Nov 20, 2012 9:26 am

Re: Enforce Unique Field

Postby mermentau » Mon Dec 10, 2012 9:21 am

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?
mermentau
 
Posts: 14
Joined: Tue Nov 20, 2012 9:26 am

Re: Enforce Unique Field

Postby shannah » Mon Dec 10, 2012 9:47 am

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
--
Steve Hannah
@shannah78 (on twitter)
sjhannah.com blog
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Enforce Unique Field

Postby mermentau » Mon Dec 10, 2012 4:25 pm

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.
mermentau
 
Posts: 14
Joined: Tue Nov 20, 2012 9:26 am

Re: Enforce Unique Field

Postby shannah » Tue Dec 11, 2012 8:47 am

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
--
Steve Hannah
@shannah78 (on twitter)
sjhannah.com blog
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Enforce Unique Field

Postby mermentau » Tue Dec 11, 2012 9:01 am

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.
mermentau
 
Posts: 14
Joined: Tue Nov 20, 2012 9:26 am

Re: Enforce Unique Field

Postby shannah » Tue Dec 11, 2012 9:22 am

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.
--
Steve Hannah
@shannah78 (on twitter)
sjhannah.com blog
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Enforce Unique Field

Postby mermentau » Tue Dec 11, 2012 10:30 am

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.
mermentau
 
Posts: 14
Joined: Tue Nov 20, 2012 9:26 am

Re: Enforce Unique Field

Postby shannah » Tue Dec 11, 2012 10:36 am

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.
--
Steve Hannah
@shannah78 (on twitter)
sjhannah.com blog
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm


Return to Xataface Users

Who is online

Users browsing this forum: No registered users and 30 guests

cron
Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved