CODEWITHSUNDEEP
phpmysqluser-preferences
Habib M
Habib M

Reputation: 53

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.

Upvotes: -3

Views: 1957

Answers (1)

NMALKO
NMALKO

Reputation: 166

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.

Upvotes: 4

Related Questions