MySQL assigning users unique codes

[inactive]

I'm sure this kind of question has been asked before, but I wasn't really sure of what to search for, so here goes:

 

We have a form which users fill out, submitted to our PHP backend. We have a list of 5000 promotion codes, and each time the form is submitted we need to pass one back to the user, marking the code off as used.

 

I haven't had much experience with MySQL, but I understand how to do this. What I don't know how to do it ensure that if two people submit the form at the exactly same time (unlikely, but we are heavily trafficked so it is possible), how do I ensure that they both don't get the same promo code before the second part of the script marks that particular promo code as used?

 

I.e. I want to avoid this:

 

User 1: submits form

User 2: submits form

 

User 2: is assigned code 27

User 1: is assigned code 27

 

User 1: marks code 27 as taken

User 2: marks code 27 as taken again

 

Thanks guys.

 

 

 

[l0ve2hat3]

u can lock the tables, but this will 99.999% never happen

[inactive]

u can lock the tables, but this will 99.999% never happen

 

You are probably right, but this will sitting on the website of the biggest office supplies chain in Aus, so I need to be 100%, or else my arse is grass.

 

Locking the table sound like an idea, thanks, but I'm still open to other ideas - anyone else?

 

Like I said, I'm a bit of a noob here lol.

 

Thanks again.

 

[PFMaBiSmAd]

If your table of promo codes has a "used" column, you can do an UPDATE query -

 

UPDATE your_table SET used_column = 1 WHERE used_column = 0 AND promo_code = "the_desired_code"

 

For concurrent accesses, the first update query will find used_column = 0 and it will have a TRUE WHERE clause. This will set the used_column to 1 and give you a mysql_affected_rows() value of 1. Any additional queries for that promo_code won't update because used_column will already be 1 and the WHERE clause will be FALSE. mysql_affected_rows() will give a value of 0 and you would need to loop to get a different promo_code and then try the UPDATE again...

[inactive]

Thats wicked, now we're cooking!

The only thing is, I need MySQL to actually provide me with the next available (i.e. used columb = 0) promo code. Would I need to do this first with a Select, and then do the Update?, or can I do both functions at once?

 

Thanks again, really helpful.

[inactive]

Grrr...column not columb lol.