Search code examples
phpmysqluser-preferences

How would I go about saving user preferences? (PHP)?

I am developing a share market type website. I was wondering how I would go about allowing users to save company quotes into a watchlist with all the updated data within similar to many others out there available.

Would appreciate any help on the matter.

Solution

  • For anything that is always set for every user you should tend to keep that in the Users table, per usual normalization. As for optional config I tend to like the following table structure:

    TABLE Users:
  id INT AI
  name VARCHAR
  ...

TABLE User_Settings
  user_id INT PK,FK
  name VARCHAR PK
  type BOOL
  value_int INT NULL
  value_str VARCHAR NULL

    Where User_Settings.type specifies whether the integer or string field should be referenced.

    ie:

    INSERT INTO Users (id, name) VALUES (1, 'Sammitch');
INSERT INTO User_Settings (user_id, name, type, value_int) VALUES (1, 'level', 1, 75);
INSERT INTO User_Settings (user_id, name, type, value_str) VALUES (1, 'lang', 0, 'en');

    And for the INSERT/UPDATE issue:

    INSERT INTO User_Settings (user_id, name, type, value_str) VALUES (1, 'lang', 0, 'fr')
  ON DUPLICATE KEY UPDATE value_str='fr';

    Also, as most other people are saying, serializing and storing the preferences is not a particularly good idea because:

    1. You can't retrieve a single value with a query, you must retrieve the entire serialized string, de-serialize it, and discard the unnecessary data.

    2. It's easily corruptable, and difficult to recover from.

    3. It's a pain in the booty to write a raw query for, ie: to globally fix a certain setting.

    4. You're storing what is essentially tabular data within a single table field.

    This should do the job. If so, please feel free to accept the answer.