xiaawan — 2010-01-06T15:39:00-05:00 — #1
Please bear with me I am having a very strange problem. I am having a table that is used to store complaints registered by users.
The primary key is auto incremented. But I have another field i.e. token_id it is also incremented by 1 every time a new entry is inserted in the table and this should be unique.
token_id is incremented in PHP code. What I do is, before adding a value in the database, first I get the maximum token_id and then I add 1 to it. Then I use this value for token_id.
But I have realized there are many duplicate token_id in the table. I have rechecked my PHP code and there is no error within this. There is also a complaint_date field that stores date and time. For some entries the date and time are same even seconds, but some entries are entered in the table with a difference of 1 to 2 minutes and some with seconds after.
There are almost 4800 users who are accessing the website. Is it possible when I use the query "select max(token_id) as token_value from table", and simultaneously many users are accessing the website and they are having the same max_id and it is used in the table that is causing the problem.
There are many users who are using the website at the same time and registering complaints.
One other thing is that, many people may be using a relatively slow internet connection.
At database level I can avoid duplicate entries by creating a unique index in the table for token_id, but why I am having duplicate entries for token_id when my PHP code is correct.
Any help would be appreciated, I never had such kind of strange issue before.
Thanks in Advance,
sjh — 2010-01-06T15:45:19-05:00 — #2
More than likely I'm afraid. Mind me asking why you have a separate token_id field? Would the auto_increment id field not serve this purpose just as well (or better, as the case may be)?
r937 — 2010-01-06T15:47:36-05:00 — #3
obviously, it is not
why do you need token_id, anyway?
xiaawan — 2010-01-06T15:49:13-05:00 — #4
Thank you for your quick reply.
Actually complaint_id is used for our purpose, and token_id is given to customers who are registering complaints. We want to keep these things separate. They are given a token_id to track their complaint. So the idea is to use two fields one for admin purposes and one for customer purposes.
Hope this justifies we are having two fields
xiaawan — 2010-01-06T15:53:08-05:00 — #5
One other thing, I have just noticed after having a look on my PHP code.
We are also allowing users to upload attachments, and the limit is up to 2MB. And I am querying the table before uploading the file. In other words, I store token_id into a variable, then script uploads a file. If successful I insert the record in the database. Is it possible while a file is being uploaded another users registers a complaint with same token_id?
r937 — 2010-01-06T16:01:16-05:00 — #6
actually, no it doesn't... why can't you use a single column for both purposes?
i'll bet it will save a lot of cross-referencing, not to mention the problem with duplicates
xiaawan — 2010-01-06T16:15:32-05:00 — #7
I agree 100% with you. It will save a lot of cross-referencing. For one second if we forget two fields, I want the thoughts of people on my duplicate values problem. Let's suppose,
User abc came to the site, he enters values in different fields and selects a file to upload to script does following,
stores the token_id by querying the table, gets the maximum token_id increments it and stores to a a variable.
$token_id = value;
Now same token_id is used in file name when uploading the file.
When script is busy in uploading the file a user xyz came into the site and fills the complaint form, and registers the complaints.
Now it uses the same token_id that was stored for other user because that hasn't yet uploaded and no entry is made in the table.
After uploading the file the script adds a new entry in the table with same token_id.
Does that make sense?
r937 — 2010-01-07T01:00:31-05:00 — #8
it makes sense that it is not working correctly, yes