error when adding a new record with duplicate value on unique field

ourteamweb

When using the New button to add a new record, I get the following error when the value already exists:
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'test@test.com' for key 'Username'

I would prefer if a check was performed first with a message such as "This username already exists, please try another".

Any idea where I can add that?

Thanks
Jim

allan

Hi Jim,

An ugly error to show users that for certain! Are you using the Editor PHP libraries? If so, there is a built in unique validator which you can use. It is described on the PHP validation page (bottom of the list). The way it works is something like:

Field::inst( 'Username' )
  ->validator( 'Validate::unique' )

You can use the parameters described in the manual if you wish to specify a custom error message (default is This field must have a unique value), db, table and or column.

Regards,
Allan

jboscher

Hi Allan,
I tried what you wrote (with a field : email)

Field::inst( 'email' )
->validator( 'Validate::unique' )

without success.
Is it still valid?

In addition, how do you specify same time that the the field is required?
Like that ?
Field::inst( 'email' )->validator( 'Validate::email_required' ),
Field::inst( 'email' )->validator( 'Validate:: unique' ),

Thx

allan

Yes that should work for the current release of Editor. In what way did it not work? No validation or error message?

In addition, how do you specify same time that the the field is required?

Using the validate options. So you might have:

Field::inst( 'Username' )->validator( 'Validate::unique', array(
  "required" => true
) )

Allan

jboscher

In browsers.php, when I set :
Editor::inst( $db, 'wp_qspread_invite' ) // Nom de la BdD et celui de la table
->fields(

    Field::inst( 'email' )->validator( 'Validate::unique' ),
    Field::inst( 'invitation' )->validator( 'Validate::numeric_required' ),
    Field::inst( 'statut' ),
    Field::inst( 'parrain' ),
    Field::inst( 'nom_invite' )->validator( 'Validate::required' ),
    Field::inst( 'date_invitation' ),
    Field::inst( 'reponse_question_binaire' )
)

I get the message "Error" (nothing else)

and when I put what you proposed , data can't be parsed

allan

What "Error" message? Is there an error reported back from the server in the JSON, or is it not valid JSON? Or is there something else that is in an error state?

and when I put what you proposed , data can't be parsed

Sorry - I used javascript object notation. I'll fix it now.

Allan

jboscher

Hi Allan,

1 -
it seems that browser.php returns :
{"id":-1,"fieldErrors":[{"name":"email","status":null}],"sError":"","aaData":[]}

2 -
can you give me the right notation (previous question)

Thx

allan

1 - it seems that browser.php returns

Odd - I don't way understand why an error message wouldn't be given. Try this:

Field::inst( 'email' )->validator( 'Validate::unique', array(
  "required" => true,
  "message" => "This field must be unique"
) )

2 - can you give me the right notation (previous question)

Yes - I corrected my post above. Rather than "required": true it should have been "required" => true (as it is now).

Allan

jboscher

Regarding Validate::unique, I understand now why it does not work.
It's not implemented in my version.

You added "unique" to Editor 1.3.0 (in May) and my version is from April.

I tried to replace Validate.php (old version) by the new one but it does not work.
Does it mean that I need to move to latest version?
I did some modifications in the SW...

Thx

allan

It's not implemented in my version.

Ah! That would do it :-).

Yes, there were a number of changes in the PHP libraries in order to support what was needed for the unique method so if you wanted to use mine you would need to update. Alternatively you could implement it using a closure function with a little bit of custom SQL, although that isn't so good for long term maintainability.

Allan

jboscher

Thx. I'll move to the new version.

jboscher

I moved to the new version. Now I can use "unique".

I am wondering, how to manage this problem.
Lets say, I have 2 fields: fieldLetter and fieldNumber
I refuse to have twice the save value in fieldLetter+fieldNumber (conjunction)
e.g. I accept (row1: a,1 -row2: a,2)
but I refuse (row1: a,1 -row2: a,1)

How to write it?
Thx

allan

You would need to provide your own validation method - as an anonymous function that would perform the validation required, querying the database if needed. The Editor PHP manual on validation describes how validation methods can be written.

Allan