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 6 of 6
  1. #1
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,381
    Thanks
    264
    Thanked 32 Times in 31 Posts

    lock table at one row maximum

    hi i am using limit 1 but i wondered if it was possible to limit a table thru phpmyadmin to one row max.

    i made a special table for member of the month, it will only have one row in it ever, there will never be two members of the month, so when i change the name i will just delete it all and insert the new name every time.

    so is there a way thru phpmyadmin to limit that table to one row max.

    it is a varchar 30 field. for a username and a int 11 field for an id, thats it.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Umm...I have a simple suggestion.

    NEVER do an INSERT into that table. Except right now, using phpmyadmin. You put in one dummy record. Maybe with 0 for the id, blank for the name?

    And from now on, in your code, you *ONLY* use UPDATE on that one record. Never DELETE, never INSERT.

  • Users who have thanked Old Pedant for this post:

    durangod (08-10-2011)

  • #3
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,381
    Thanks
    264
    Thanked 32 Times in 31 Posts
    that is a fabulious idea OP good thinking, sitting here thinking about it, what would happen if somehow my client deleted that row for some reason, you know how they are..

    would the update bomb?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Yes, the update would then bomb.

    Okay, so here's what you do:

    (1) Remove permissions from the *TABLE* to anybody except admin.
    (2) Using the admin account, create a STORED PROCEDURE that is the only way for anybody other than admin to modify that table.
    (3) Have that stored proc always DELETE and then always INSERT.
    (4) Give permissions to just that stored proc to your users.

    Of course, if your users have admin access, then you are hosed.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,200
    Thanks
    75
    Thanked 4,342 Times in 4,308 Posts
    Oh, silly me!

    An easier way to protect everything!

    Not foolproof, but...

    Code:
    CREATE TABLE whatevername (
        userid INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(xxx)
        );
    And then, to get the record, always use
    Code:
    SELECT username FROM whatevername ORDER BY userid DESC LIMIT 1
    Let them add as many names as they want. You will only ever get the latest one.

    Of course, if they wipe them all out--or wipe the wrong one out--they are hosed. But that's not your fault.

  • Users who have thanked Old Pedant for this post:

    durangod (08-10-2011)

  • #6
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,381
    Thanks
    264
    Thanked 32 Times in 31 Posts
    great thanks for that it is very much appreciated.


  •  

    Tags for this Thread

    Posting Permissions

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