View Full Version : lock table at one row maximum

08-10-2011, 02:23 AM
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.

Old Pedant
08-10-2011, 02:30 AM
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.

08-10-2011, 03:01 AM
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?

Old Pedant
08-10-2011, 04:04 AM
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.

Old Pedant
08-10-2011, 04:09 AM
Oh, silly me!

An easier way to protect everything!

Not foolproof, but...

CREATE TABLE whatevername (
username VARCHAR(xxx)

And then, to get the record, always use

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.

08-10-2011, 04:20 AM
great thanks for that it is very much appreciated.