How can I avoid selling one item to more than one customer?

rushenas

Hi all
I have developed a system, which sells e-vouchers. I am using MySQL for it's database, and people receive their vouchers immediately after they pay for it.
I stored all vouchers in database, with status available. When somebody wants to purchase a voucher, I mark one record as reserved in database. When he pays for ir, then I change the status to sold, and show him bought voucher.
The problem is, when two (or more) customer arrive at the same time to payment page, program mark same voucher for all of them. This means selling one voucher to more than one people. And this will cause serious problems for me.
Is there anyway to lock table (or something similat to this) at the time of updating, in order to avoid such problem?

benracer

This is a problem with your database/project design. You need a way to identify each voucher as unique. You could have an id column in your database that is auto increment for each row inserted. If this doesnt help could you post the code that updates the table and the inserts the vouchers. Also you could have a seperate table for orders.

bretticus

I agree with benracer. I think instead of having the voucher records already, you should build them at the time of purchase. You could use php like

$voucher = md5(uniqid(rand(), true));

to get a unique sequence, for example.

You should, however, be able to lock a table before updating it...

$query = "LOCK TABLES Vouchers WRITE";
mysql_query($query);

$query = "UPDATE Vouchers SET status = 'sold' WHERE voucher_id = 3";
mysql_query($query);

$query = "UNLOCK TABLES";
mysql_query($query);

lingsharma

Do you have another collumn which stores the customer name when they purchase the voucher? If you do have than i think if you make the voucher id and the customer name together as primary key then duplicate entry regarding the voucher will not occur.