mysql DISTINCT question

[willg825]

Quick question for you experts...

I have a form, which when submitted, adds to a mysql database. If someone enters the form, then goes back, and enters it again, two entries will result. We of course want the latter entry, since it reflects some sort of change in the original one.

Is there a (simple) way to automatically remove the first one when they resubmit it? I dont want to just check for their "name" being the same, because then someone else could register with the same "name" and delete their entry together...

Also, when I list the organizations, I use the mysql DISTINCT command so any repeats are only listed once. The problem is the DISTINCT command, by default, returns the first entry - and I want it to return the last entry. Is there a flag or another keyword that will make DISTINCT return the _last_ field it encounters duplicates of instead of the first?

Thanks in advance, I appreciate it!

-will

[httpwebwitch]

A different strategy is needed. If someone can sign up with an existing name and overwrite someone else's profile, that's bad. It's also not nice to give other people the ability to change someone else's information just by typing in their name.

You'll want two kinds of data entry:
1) create a "new" user (uses INSERT)
2) modify an existing user (uses UPDATE)

When someone tries to create a new user, you check if the name is already taken. do that with a "SELECT... WHERE name='$name' LIMIT 1". if the name is taken, you force them back and tell them to choose another. Have them choose a password, too.

Then make your users sign in with their password before you allow them to modify their profile.

[ergophobe]

This might help with the first part

[webmasterworld.com...]

[edit: I'm getting closer - same timestamp - with luck I will actually post before httpwebwitch someday!]

[httpwebwitch]

I re-read your post and maybe I misunderstood what you wanted.

You will need something unique to identify an entry, which is up to you to decide. "name" is probably a good one.

When the user submits, do a SELECT. if the name exists, UPDATE. if it doesn't, then INSERT.

But whatever you do, get out of the habit of keeping multiple records in the db that refer to the same thing. Showing just the last good entry sitting atop a pile of garbage is not a good solution.

[willg825]

Yeah... I agree. Even if I could get around it by having a DISTINCT command later, its better to never have duplicate entries

httwebwitch, I think I am going to do just that... Upon submit, it will check to see if that name already exists... if it does exist, then check the timestamp to see if the two entries are close (if the entries aren't within a couple minutes of each other, it means someone else is trying to register with a name already being used).. not flawless, I guess. But that way it will UPDATE the existing row, instead of inserting a new row

thanks for your help!

-will