Avoiding Duplicate Database Entries

Hi, searched for this problem and still no luck.
I have a very simple form, only two fields for the user to complete, but the first field needs to be unique. I have set the unique atribute in the table for this field via phpMyAdmin and this works. However, I am trying to customise the error message that gets displayed when a user tries to post a form with a number that already exists in the database. Am I going about this the wrong way by setting the atribute in the table directly? The error message looks like this:[attachment=0]Screenshot - 08_04_2009 , 17_45_58.png[/attachment]
Is there a way to customise the message to something a bit more meaningful?

Thanks in advance

Hi JohnnyG,

That's a Joomla System message.

I think you need to run a validation check before the data is saved. You could so this in serverside validation - or with JavaScript/Ajax from the form itself.

Bob

Thanks Bob,
You've confirmed what I was thinking. Would it be better to remove the "unique" attribute from the MySql table and use the serverside validation? If so what would the code look like?

Hi JohnnyG,

Can you say a bit more about the application? Usually it's good to have a unique ID in a table (but not always). Depends what kind of rerocrd you are storing.

Bob

Hi JohnnyG,

you can use the server side validation to run some query and check that no duplication will occur, and if there is any, it will report the error you want for the user back!

Cheers
Max

Hi Bob, and Max!
Thanks for the update.
The application is a really simple form. The table holds a list of documents and their respective reference numbers. I have publishers who write the documents and add a unique refernce number to the document in the form BTB-xxx, where the xxx part needs to be unique. The form is used for the publisher to "reserve" a document reference number. When I built the form I just added two fields via the wizard, Ref Number and Document Title. The form submission works like a charm, posting the entry and redirecting the publisher to a "thankyou" page. I just want to make it "idiot proof" so that I don't end up with two (or more) documents with the same reference. Like I said before, setting the unique attribute against the Ref Number field directly on the table has the desired effect (not saving the duplicate entry), but looks a bit messy!
I'm sure there's a really easy soultion to the problem but I must admit my php/Javascript/Ajax knowledge is somewhat limited!
Thanks again for your help
John

Hi JohnnyG,

Hmmm . . . the only difficult piece in this is making sure that the number is unique. The easy way to do it is to set the number when the data is stored in the database and tell the user afterwards. Otherwise there's always the (remote) possibility that someone else will grab the number between the form being shown and submitted.

It would be neatest to do this with Ajax - though fiddly to set up. I think I'd go with Max's solution and use server-side validation

<?php
$ref_num  = JRequest::getInt('ref_num', '', 'post');
if ( !$ref_num ) {
    return "You must enter a reference number";
} 
$db =& JFactory::getDBO();
$query = "
    SELECT count(*)
        FROM `#__table_name`
        WHERE `ref_num` = ".$db->Quote($quote)." ;
";
$db->setQuery($query);
$in_use = $db->loadResult();
if ( $in_use) {
    return "That number is in use, please try again";
}
?>

Note: not tested and will need debugging.
You'll also need to substitute the correct table, and field names.

Bob

Hi Bob,
Thanks for the code. Am I correct in thinking that wherever you have placed "ref_num" I replace this with my field name? Also, should I remove the unique index from the table field?
Regards

John

Hi John,

I replace this with my field name?

yes!

your table should have some primary key to work fine with Chronoforms DB connection!

Regards
Max

Hi John,

Just to confirm - you'll need to replace ref_num and table_name with the appropriate values, - as MAX says, keep a unique primary key to your table.

Bob