Good points, However, while I agree that you need at least one unique key, putting multiple unique keys in a table can also have serious performance ramifications as the system will slow down considerably if constantly rebuilding indexes. It probably depends upon how many fields have to be checked individually, and how busy you expect the system to be overall.
Andrew Hazen -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 02, 2002 8:17 PM To: [EMAIL PROTECTED] Subject: RE: data compare and duplicate entries.. IMHO it is an expensive to do a query like that. I would consider having a unique key defined and allow the insert form to be posted to the server. If it is a duplicate value then UNIQUE KEY wouldn't allow it be inserted and will return with an error. But if you do not have too many transactions going on then you can have an extra check what Andrew Hazen has suggested. But you cannot get away without having UNIQUE KEY. Because check suggested by Andrew is not fool-proof because if you have too many transactions going on at the same time then it is very likely that SELECT statement may come back with 0 but in the meanwhile somebody else inserted the same row. Vivek > -----Original Message----- > From: andrewhazen [mailto:[EMAIL PROTECTED]] > Sent: Thursday, May 02, 2002 6:44 AM > To: tech; mysql > Cc: andrewhazen > Subject: RE: data compare and duplicate entries.. > > > Not sure of your scripting language, but this logic should work. Just > apply it to each web_form variable posted. > > Select count(*) as number FROM table WHERE field=value_from_web_form > > If number==0 then .... do your inserts > Else > Echo "Sorry, field=value_from_web_form data already exists."; > Reset form > > > > Andrew Hazen > > > -----Original Message----- > From: Tech @NorthWeb [mailto:[EMAIL PROTECTED]] > Sent: Thursday, May 02, 2002 9:32 AM > To: mysql > Subject: Fw: data compare and duplicate entries.. > > hi, > I was looking for an SQL statement or script that whould allow me to > compare > data when it was entered into a mysql-php field on a web page > so that it > checks whether the data entered has not been used before in the > database.. > In short, when data needs to be entered, the statement/script > must check > in > the database whether the data is not already entered into the database > and > if its there, return with an error.. and not allow the data to be > entered... > if the data is not there, then it may be entered.. > > Thanx > Willem Pretorius > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
