Form Advice - Sessions

[nottoolate]

I have a form that multiple users are accessing at the same time.

Within the form is a project number field. The project number is generated from a function that queries the project table in the database for the maximum project number then adds 1.

 

It's starting to be a problem where multiple users access the form and are given the same project number. The form doesn't insert into the table, instead an excel spreadsheet is generated with the form info and the user emails it to someone else for data entry.

 

Is there a way that I can resolve my issue so that each time someone accesses the form no one receives the same project number?

 

[PFMaBiSmAd]

You will only know what the actual number is after you have inserted the row into the table. Getting the current highest number and adding one doesn't work with concurrent users, unless you lock the table for the duration of the process (which would slow your processing down to a crawl since no one else could do anything until the first person has finished.)

 

If you need to get the actual number for display purposes (this rarely actually ever needed), you can insert an empty row (with the date/time of creation) into the table to 'reserve' the number. Then update the row once the actual information is known. This of course creates additional work since you must now cleanup and delete any rows that don't actually get completed, which is why this is normally not done this way.

 

Why do you need the actual identifying number while the form is being filled in with data? How about just getting it at the end of the process when the data is inserted into the table?

[nottoolate]

I agree that it would be better after the fact. I originally had it that way when I first created the form, but I was told to change it so that users can see the number while filling it out.

 

I may have to attempt the reserve the number solution and see how that works out.

 

Thanks for the advice.

[nottoolate]

Ok, I've decided to try this instead.

 

I now have access to our MySQL database that I can edit any way I please (I didn't have full control over our Oracle database). I've created a Project table and auto incremented the project_id field. I set the auto increment value to start at 0025. So all of that is good.

 

But where do I go from here. How do I go about setting the form so that the project field increments by 1 when the user opens the form and inserts into the Project table as well.

[litebearer]

Just a thought...

 

1. Short note/blurb on form to USER informing that  submission of the form the form will immediately reload to: (A) confirm info submitted; and (B) provide USER with an order number for their reference.

 

2. When form is submitted, handle the data (sanitize and validate); post to db; get id from appropriate db record; redirect to confirmation page.

 

3. make sure you have some way to track orders as to quoted/inprocess/finalized

 

 

[gizmola]

Ok, I've decided to try this instead.

 

I now have access to our MySQL database that I can edit any way I please (I didn't have full control over our Oracle database). I've created a Project table and auto incremented the project_id field. I set the auto increment value to start at 0025. So all of that is good.

 

But where do I go from here. How do I go about setting the form so that the project field increments by 1 when the user opens the form and inserts into the Project table as well.

 

I just have one comment.  You did not state in your post what database you were using.  If you had stated that you were using Oracle, I would have let you know that Oracle has sequence objects.  This is different from mysql auto_increments, in that a sequence is a number generator, and it can be used to get a unique number in advance, that you can then use when you actually insert a row. 

 

You could in fact develope your own sequence generator even with mysql by creating a table that had no purpose other than to store a sequence number.  With the proper locking code, this could be used to get a unique number which could then safely be used as the key for your table.

 

Of course what you could also do is simply insert a blank row purely in order to get the project_id, and then use that to update the row when data was submitted.

[PFMaBiSmAd]

So, is this database table even being used to hold anything besides the project number/autoincrement column? Your first post states that the form doesn't insert into the table.

 

If all you are doing is generating the project number, execute an INSERT query to insert the next auto-increment value, followed by a mysql_insert_id statement to get the id.