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.