Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts

    Preventing 2 Max Numbers

    My website allows members to post Comments beneath each Article.

    To make things more user-friendly, I include a system generated "Comment Number" with each post. The idea being that have primary keys would get too large over time, and confuse non-IT people that expect #1, #2, #3,...

    Today while working on another issue, it occurred to me that my code has the following flaw...


    Scenario:
    User1 and User2 are eager to post a Comment about my article "5 Tax Shelters You Need To Know"

    When they click on "Add a Comment", they are taken from "article.php" to "add_comment.php". From there, my PHP code first runs this query...
    PHP Code:
        // Find Last CommentNo.

        // Build query.
        
    $q2 "SELECT MAX(comment_no) AS lastCommentNo
                FROM article_comment
                WHERE article_id = ?"
    ;

        
    // And so on...


        // Check # of Records Returned.
        
    if (isset($lastCommentNo)){
            
    // Maximum Found.
            // Increment CommentNo.
            
    $commentNo $lastCommentNo 1;

        }else{
            
    // Maximum Not Found.
            // Assume No Comments Exist.
            
    $commentNo 1;
        } 
    Let's say that originally there were 15 Comments.

    Well, when User1 and User2 each tries to add a Comment, each of their respective instances of my code will yield $commentNo = 16

    Now, I'm not worried about them submitting things at the exact same nanosecond and there being a collision in my database - although maybe that is another issue I have not accounted for?!

    But what I am worried about is this...


    Continuing to follow this scenario, let's say that User1 is quicker writing a Comment, clicks "Submit", and my "add_comment.php" script executes the code above, plus this...
    PHP Code:
        // Submit Comments.    

        // Build query.
        
    $q3 "INSERT INTO article_comment(article_id, member_id, comment_no, body, created_on)
                VALUES(?, ?, ?, ?, NOW())"
    ;

        
    // Prepare statement.
        
    $stmt3 mysqli_prepare($dbc$q3);

        
    // And so on... 
    A new Comment record is created with a CommentNo = 16.

    So far so good.


    HOWEVER, when User2 clicks "Submit", the same code above runs, and now I have a 2nd Comment record with a CommentNo = 16!


    See the problem??


    How can I prevent this from happening??

    If my website takes off, this could actually be a major problem - I see people posting Comments every second on sites like The Washington Post when there is a hot topic.

    The last thing I want is to have 5 Comments all labeled as #16!! (Technically, this wouldn't hurt the backend, because I have an AutoNumber that creates the "comment_id". Still, it would look really bad if I have all of these Comments on my website with "duplicate" Comment Numbers!!)

    Sincerely,


    Debbie

  • #2
    Senior Coder
    Join Date
    Apr 2010
    Posts
    1,453
    Thanks
    71
    Thanked 102 Times in 101 Posts
    Just butting in on the issue of their might being two posts with the same value. You can do a check to make sure it's a unique id, such as: PHP: uniqid - Manual
    Been a sign maker for 7 years. My business:
    American Made Signs

  • #3
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by myfayt View Post
    Just butting in on the issue of their might being two posts with the same value. You can do a check to make sure it's a unique id, such as: PHP: uniqid - Manual
    Did you read my OP?

    Your response makes absolutely no sense...


    Debbie

  • #4
    Senior Coder
    Join Date
    Apr 2010
    Posts
    1,453
    Thanks
    71
    Thanked 102 Times in 101 Posts
    Yes I read the whole thing and haven't figured out the proper fix to it, but thought I'd throw in that suggestion. But I'll move on to other posts.
    Been a sign maker for 7 years. My business:
    American Made Signs

  • #5
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by myfayt View Post
    Yes I read the whole thing and haven't figured out the proper fix to it, but thought I'd throw in that suggestion. But I'll move on to other posts.
    Where were you going with that then?

    I stated that I use AutoNumber on the "id" field...

    The problem is not with the "id", it is with the "comment_no" that I am creating which could have dups due to multiple people running things at the same time.

    It is like I need a way to tell PHP/MySQL, "User1 is checking for the Last CommentNo, so you have to wait until they are done."

    I see where the problem is, but am unsure of how to solve it...


    Debbie

  • #6
    Regular Coder
    Join Date
    Sep 2011
    Posts
    428
    Thanks
    18
    Thanked 26 Times in 26 Posts
    Quote Originally Posted by myfayt View Post
    Just butting in on the issue of their might being two posts with the same value. You can do a check to make sure it's a unique id, such as: PHP: uniqid - Manual
    This is right but he just didn't explain how to do it so it wasn't much help.


    You should make a unique key for the article id and comment number. If you use PMA then just select add new key on 2 columns and make it unique and set it to those two columns.
    Doing this will allow this:
    Code:
    ARTICAL|COMMENT
    1	1
    1	2
    1	3
    2	1
    2	2
    3	1
    1	4
    2	3
    2	4
    1	5
    3	2
    etc.
    So you are able to have a row unique based on both column values, where no row has the same value match as another, for instance I couldn't add 2|3 because it's already there, however I could add 2|5 because it's not.

    Think of it as making one unique row of text separated by pipes:
    Code:
    1|1
    1|2
    1|3
    2|1
    2|2
    3|1
    1|4
    2|3
    2|4
    1|5
    3|2
    Then declaring that column as a unique text column. This way only keeps it completely separated and allows for different types, such as integers.


    Also, as for the numbering issue at the same time, try running the query in one run rather than selecting then inserting. I'm not sure on how to do this and this is probably already posted somewhere, but you should be able to get the highest value based on this situation and then increment it once, I'm not that advanced with MySQL so you'd probably be better off searching for the example or waiting for someone to tell you.

  • #7
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,108
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Dubz View Post
    This is right but he just didn't explain how to do it so it wasn't much help.


    You should make a unique key for the article id and comment number.
    Thanks for the explanation, but that isn't the issue. (I get what you are saying.)


    Quote Originally Posted by Dubz View Post
    Also, as for the numbering issue at the same time,
    This is the issue!!!

    For example, let's say that User1, User2, and User3 all decide to post a Comment at the same time. And let's assume that the $lastCommentNo was 12.

    It is possible that User1, User2, and User3's scripts would tell them the new $commentNo = 13.

    And if User1's script processed slightly faster, he/she would succeed in INSERTing a new Comment where comment_no = 13.

    But when User2 and User3's scripts came in a fraction of a second later, they would get errors because of the unique index you suggested above!

    (I know this, because I added just such a composite index before supper, and then I forced a duplicate value, which in turn made my script fail.)


    How do you think my website will be received if every time people submit Comments at the exact same moment as someone else, only one person's submission will succeed?!


    (Maybe I could display a message, "Oops! You weren't quick enough! Re-type in your Comment, and maybe you'll have better luck next time?!")


    This is a *major* design flaw on my part, and would start creating issues almost instantly! And yet, there must be a solution out there...


    Quote Originally Posted by Dubz View Post
    try running the query in one run rather than selecting then inserting. I'm not sure on how to do this and this is probably already posted somewhere, but you should be able to get the highest value based on this situation and then increment it once, I'm not that advanced with MySQL so you'd probably be better off searching for the example or waiting for someone to tell you.
    Yep, we see the same issue, but are short on answers!

    Sincerely,


    Debbie
    Last edited by doubledee; 05-25-2014 at 04:58 AM.


  •  

    Posting Permissions

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