RE: [PHP-DB] Table locking to prevent duplicate inserts?

John, Thanks much for the excellent tips, I guess I was trying to make it too difficult. We're working on the SQL and indexes to alleviate the slowdowns and I don't expect this to really be an issue in the future, it just concerned me that it was even possible... I thought I'd coded it better than that. The duplicates are an issue because this is a stats-gathering application for area high school sports, and duplicate stat entries resulted in doubled stat values. I guess we should also be looking at tightening up our reporting code using SELECT DISTINCT, perhaps, but really hadn't considered that we'd have any dupes to begin with. Adding a unique constraint isn't a bad idea either, but since it'd have to be unique across several fields I'd have to read up and figure out how to do that. > Sorry about that; I hate to keep my employers waiting. BTW, I haven't > received my paycheck this month. ;) I certainly didn't mean to imply that anyone on the list had an obligation to answer my post and hope no one took it that way. I was just apologizing for the duplicate post and trying to explain why I was trying again. Anyway, thanks again for the help. I'll be able to fix this app and will incorporate your suggestions into my work in the future. Chuck > -----Original Message----- > From: John W. Holmes [mailto:holmes072000@charter.net] > Sent: Monday, October 20, 2003 11:32 AM > To: Mayo, Chuck > Cc: php-db@lists.php.net > Subject: Re: [PHP-DB] Table locking to prevent duplicate inserts? > > > Mayo, Chuck wrote: > > Hi all, > > > > Hope you'll forgive the duplicate post but the list seems > to be hopping > > today (unlike the friday afternoon I sent the original > query)... since > > everyone seems to be awake today I thought I'd try it again: > > Sorry about that; I hate to keep my employers waiting. BTW, I haven't > received my paycheck this month. ;) > > > 1) A user submits a form containing about a hundred form > fields which PHP > > needs to insert into a MySQL table. > > > > 2) MySQL is busy doing a lengthy SELECT for someone else, > so the user's > > browser hangs until the SELECT is done. > > Okay, first of all, fix this "lengthy" select or whatever other > slowdowns there are in your scripts. > > > 3) The user gets tired of waiting and hits submit again > while his browser is > > still hung. > > > > 4) When the long SELECT finishes, two PHP threads kick off > (I'm assuming), > > the user's original submit and his second, resulting in > duplicate INSERT's > > Easy way is to set a session variable when they submit a form > flagging > the form as submitted at time X. If it's been less than X > seconds since > the last time the form was submitted, then deny the re-submission. > > Other method would be to supply a unique id to the form in a hidden > element. When the form is submitted, put that variable in the > session. > If the form is submitted, the value is already in the session, so you > know this is a duplicate entry. > > What problems do the duplicate entries cause? You may be able > to handle > this at the database level with a UNIQUE restraint on your column. > > -- > ---John Holmes... > > Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ > > php|architect: The Magazine for PHP Professionals – www.phparch.com > > > >