Tough question - Need help - PHPBuilder Forums

Tough question - Need help

Anon

I have a table with the structure:

pin_id (PRIMARY) -> auto_increment
user_id (UNIQUE KEY) -> varchar16
email (UNIQUE KEY) -> varchar100

Normally, a person registers at my site and supplies the user_id and email which goes into the database, and a pin_id is automatically generated.

However, I have a need to only ask for the email address of a new member and not the user_id, so I would only be inserting the email address into the database. The pin_id is auto_increment.

The problem is that user_id is UNIQUE KEY and has to stay that way. Is there a way for me to somehow insert the pin_id into the user_id field DURING the FIRST database call?
For example, it would NOT work if I use last_insert_id since the first database call would have returned an error with duplicate empty string user_id fields ("" and "").

Or is there a better way to approach the problem?

Thanks!

Tennisguy

Anon

try something like this:
before writing to the database do a select query that returns the current last_insert_id(). Count 1 to it and you'll have the next id nr. Concatinate it with the email adress and store it into userid. Then insert the userid and the emailadress into the table. Make sure u lock the table when making this transaction.

Anon

Yeah be careful with the locking and thread safety. This is a pretty messy way to do it and it only increases the mysql locking problems. If your site is busy, don't do this.

I'd probably do it with 2 queries if you absolutely have to do it, or make the user_id a md5() hash of the microtime() or find some other unique number to put in there.

Anon

Thanks for the help guys.

The site is very busy and I am trying to figure out the most efficient way to do it.

Originally we used a user-supplied user_id as the primary key to reference all the other tables. However, in preparation for a new sign up form where some users would only supply an email address (and not a member name/user_id), I introduced a new primary key called pin_id which is auto_incremented.

However, not wishing to have duplicated member names (even when people only supply an email address), I made the user_id field an UNIQUE KEY. However, it seems that having 2 empty string user_id rows violates this and MYSQL does not allow me to just insert an email address by itself - I have to insert an unique user_id.

So it seems that I would need to figure out a way to insert an UNIQUE user_id into the user_id field when I first insert the email address (OR IS THERE A WAY TO GET AROUND THIS? I tried "NULL" but that did not work).

I am not familiar with table locking and based on Tim's post I would hope to avoid taking that route. I could just use the email address (it is UNIQUE) for the user_id but the email address can go up to 100 characters and user_id can only go up to 16 (hence an UNIQUE email address might have the same first 16 characters).

Tim, when you wrote "I'd probably do it with 2 queries if you absolutely have to do it, or make the user_id a md5() hash of the microtime() or find some other unique number to put in there.", can you give me an example of doing it using 2 queries?

Also, suppose 2 people filled out the forum and submitted at the EXACT same time, wouldn't that produce a duplicate if I use the md5() hash?

Best Wishes and thank you for your help.

tennisguy

Anon

Originally we used a user-supplied user_id as the primary key to
reference all the other tables.

How impossible is it to redesign things to make the problem go away? And if you can't change things, you can at least be a big warning sign to everyone else: if you use anything other than an arbitrary number for linking your tables (and anything user-supplied most certainly fails to qualify as arbitrary) YOU WILL BE SORRY.

So at least you have the comfort of others learning from your grief. :-)

Anon

This is WHY I just introduced the pin_id (auto_incremented) to reference other tables.
But the user_id must be unique since we offer user_id@foo.com email.

But please help answer the original question.
I am a newbie at this and is still learning.

tennisguy

Kirk Parker wrote:

Originally we used a user-supplied user_id as the primary key to
reference all the other tables.

So at least you have the comfort of others learning from your grief. :-)

Anon

I just thought about a possible solution to the problem. Your comments are GREATLY appreciated.

Currently, I have a dynamicac row length table which contains the auto_increment pin_id, the UNIQUE varchar16 user_id and UNIQUE varchar16 email.

The problem was the figure out what to do with the user_id if all I wanted to do was to insert the email.

I am thinking of breaking the original table into two tables.

TABLE #1 = mail_list table which contains an auto_increment pin_id and a varchar100 email

and

TABLE #2 = user_information table which contains the pin_id of the people who actually provided an user_id and the user_id.

For example:

TABLE might have

pin_id email
1 1@foo.com
2 2@foo.com
3 3@foo.com
4 42foo.com

and TABLE 2 might have

pin_id user_id
1 username1
3 username3

Both tables would still be dynamic row length,
but now I can insert to my heart's content first into mail_list then grab the MYSQL_INSERT_ID() => is this function safe to use to retrieve the last insert if even one have 20+ simultaneous users?

and then use the MYSQL_INSERT_ID() to populate the other fields.

What do you think?

tennisguy

Anon

Ah, now that I go back and reread I see that is clear, and I just missed it. On the design issues, what about simply making the email address be the userid?

As far as an immediate solution, your proposal might work. You will probably find more places than you like where you have to replace a single query with a sequence of queries, but oh well that's life with mysql!

Anon

The reason we cannot use the email address as the user_id is because we give free email at user_id@foo.com to users.

However, what do you think of the proposed solution in the post headed "Here is a solution - what do you think?"

Anon

Not only am I having trouble reconciling this statement:

  The reason we cannot use the email address as the user_id is   because we give free email at user_id@foo.com to users.

with your earlier one:

  However, I have a need to only ask for the email address
  of a new member and not the user_id,

but I am also failing to see how the email wouldn't have to be unique regardless, and thus I don't see why you couldn't use the email as the userid.

Anon

Well, once the member gives us an email address, he or she goes onto the mailing list. But since no user id was chosen, we cannot provide a free email account at user_id@foo.com

However, I have already implemented to solution in the post below using an auto_incremented pin_id to reference across tables and separate the email address table from the table with the user_ids.

Thank you.

tennisguy

Kirk Parker wrote:

Not only am I having trouble reconciling this statement:

  The reason we cannot use the email address as the user_id is   because we give free email at user_id@foo.com to users.

with your earlier one:

  However, I have a need to only ask for the email address
  of a new member and not the user_id,

but I am also failing to see how the email wouldn't have to be unique regardless, and thus I don't see why you couldn't use the email as the userid.

Anon

Hi:
I think you can use a easy way by Just save a serias number .When u get a new use u can add a rand number to it .The rand number is useful to get rid of conflicting when system are busy.
Good luck.

Anon

best solution is to create a primary over the userid and the username... only problem is you have to rebuild your complete database..