How to allow a column variable to be changed only once with PHP/MySQL?

I have a table in my MySQL DB with information from each user that they submitted during registration.

I would now like to allow users to change one of those columns (Column X), but only once.

What is the best way to do that?

The only way I can think of is to add an additional column (Column Z) to the table with a binary value that defaults to 0,开发者_StackOverflow社区 and changes to 1 when Column X is updated by the user. If Column Z is 0, the site allows the change, otherwise, it does not allow it.

Is there a better way? Should Column Z be in the same table as Column X? Any other relevant points/issues I should consider?

Thanks.

You could have a default value for column x that gets created once the row is inserted to the table. Then when the user wants to update his row, the db checks this value, if it has not changed since insertion, then the user can be allowed to update. Otherwise it rejects

CREATE TABLE example_table (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  data VARCHAR(100),
  value_to_be_updated_once DEFAULT NULL
);

in your code you can check if the column (value_to_be_updated_once) is null, then the user should be allowed to edit.

You must make sure that the user does not set this value to NULL, unless that is something you want to have. (maybe the user changed his/her mind and will edit later)

You may, at some point, decide to allow the user to change columnX 3 times. A boolean will not allow for this. And what if you decide to allow the user to change columnY too. What then?

If you are absolutely positive that you will never need to change your rules, a binary flag will work fine. But if you will potentially be allowing and limiting changes on multiple columns with possibly different limits, you might consider a User_Edits table. It might look something like this:

tablename varchar(30) not null,
columnname varchar(30) not null,
user_id int unsigned not null,
changetime timestamp default current_timestamp,
oldvalue text

To find out how many edits a user had done:

SELECT COUNT(user_id) FROM User_Edits
WHERE tablename='Mytable' AND columnname='ColumnX';

And, as an added bonus, you'll have an audit trail that allows you to undo changes.