SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot xiaawan's Avatar
    Join Date
    Sep 2007
    Location
    Islamabad, Pakistan
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Duplicate entries issue

    Hi everybody,

    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,
    Zia Awan

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by xiaawan View Post
    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.
    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)?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by xiaawan View Post
    ... why I am having duplicate entries for token_id when my PHP code is correct.
    obviously, it is not

    why do you need token_id, anyway?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot xiaawan's Avatar
    Join Date
    Sep 2007
    Location
    Islamabad, Pakistan
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Zia Awan

  5. #5
    SitePoint Zealot xiaawan's Avatar
    Join Date
    Sep 2007
    Location
    Islamabad, Pakistan
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Zia Awan

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by xiaawan View Post
    Hope this justifies we are having two fields
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot xiaawan's Avatar
    Join Date
    Sep 2007
    Location
    Islamabad, Pakistan
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i'll bet it will save a lot of cross-referencing, not to mention the problem with duplicates
    Dear r937,

    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?
    Zia Awan

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by xiaawan View Post
    Does that make sense?
    it makes sense that it is not working correctly, yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •