Multi user application

[eluser]arthurhanlon[/eluser]
Hey,

I was wondering if anyone could shed some light on this one for me.

I am about to embark on the development of my first multi user application written on top of CodeIgniter and hopefully Ion Auth and since I have never really written anything that users other than myself have used, I realise that I don't know how to go about one specific thing (I'm sure many more "specific" thing will crop up as I go along).

I'm unsure as to how I would go about having the users add records through the app to a database at the same time. These records would have system generated unique IDs based on the last ID + 1 and I was wondering how many of you out there tackle the same task. I just don't know how to overcome the fact that if a user logs on and starts to create a new record with ID 100011 (which is based on the last record ID 100010), potentially, another user could start to create a record at the same time with ID 100011 and when they are written to the DB.

I could use record locking within the app for editing but creation baffles me a bit.

I suppose that I could have the system generate the ID during the process of writing the record to the database THEN present the user with the ID after the save.

I hope I have explained myself well enough, anyone have a more eloquent way of doing this or can point me in the right direction to learn?

Cheers,
Arthur

[eluser]Wondering Coder[/eluser]
when your saying about ID, is it your primary key to your table? Since I'm not sure you can set your ID primary key and even if almost at the same time a different user create a record it will still follow that last ID in your db. So you don't have to worry about that. And now if your ID is not a primary key to your table you can create your own validation for the purpose to check if the ID is already in your db.

[eluser]jblack199[/eluser]
Wondering Coder is correct, when you run mysql_insert_id() in normal code or $this->db->insert_id() in CI it doesn't matter if 100 people simultaneously input data as it is tied directly to the resource id of the query itself.

Which having the id be a primary auto-incremented key is the best solution to prevent any mishaps and a lot of debugging/coding time to handle it otherwise.

[eluser]arthurhanlon[/eluser]
It'll probably help to give you guys a little more info.

The app is to keep track of product returns and missing goods and essentially, each record will have a unique number along the lines of RMA100011, RMA100012 etc. This I suppose could well be the primary key BUT, doesn't necessarily have to be. I was thinking of having a separate primary key.

The thing I am finding hard to figure out is that the user inputting a new record must pass this unique number (RMA100011) onto the customer so that they have a unique reference. The unique number would therefore be created when the record was created but not saved into the DB until the record was complete and the details were saved.

The problem, I guess, is how can I effectively 'reserve' the unique number while the record is being created so that if another user tries to create an entry, they are given the next number in the sequence?

I thought of storing the newly created records in a temporary table then when the record was saved, remove them from this table and port them over to the correct table. This would keep the sequence going and allow me to generate new numbers easily. If a user then cancels the creation of the new record, the number is removed from the temp table and the record is discarded.

The crux of it is that the user needs to be aware of the number when entering/after saving the record to pass this onto the customer.

Sorry, I have a tendancy to waffle on but I hope that gives a slightly clearer picture of what I am trying to do.

Cheers,
Arthur

[eluser]jblack199[/eluser]
What I would do, is say start my primary key @ 100000.. and make the primary key the RMA # so in your 'display' you'd do something like:

Code:

$q = $this->db->insert();
$id = $this->db->insert_id();

echo 'RMA'.$id;

making it easy to manage and your primary key is left intact.

then for the customer, if you have a 'search' function for them to find out the status of their return or whatever they type in RMA100000 and the code strips off the RMA and searches the table where ID = 100000

[eluser]danmontgomery[/eluser]

The problem, I guess, is how can I effectively ‘reserve’ the unique number while the record is being created so that if another user tries to create an entry, they are given the next number in the sequence?

You don't. There's no reason to ever create the key before the record is inserted. You insert the record, and retrieve the ID from the database to show the user.

[eluser]arthurhanlon[/eluser]
I see how this can be done now. Big thanks guys for making the penny drop, it hurt on the way down