Faced such a problem: The user chooses from 1 to 16 words, I need to add them to the database in order to display them later on the main one.

Problem: How to organize the request and the database itself? As far as I understand, you do not need to create 16 fields for absolutely identical data (just one or two words), also provided that the user can select only one word, and not all 16.

  • one
    If each of the words you really need in the work separately, then you do under the words a table with literally two fields: the id-entities to which the word and the word itself belong. those. each of the words is put in a separate entry - Mike
  • And if you do not need something, as an option, you can simply save in one field - json_encode (['word 1', 'word 2', 'word 3']) or serialize (['word 1', 'word 2', ' word 3 ']), and when outputting - json_decode ($ data [' col ']) or unserialize ($ data [' col ']), respectively. - Bookin

2 answers 2

Structure (classical structure много - много ):

 CREATE TABLE users( id integer, name varchar(255) primary key (id) ); CREATE TABLE words( id integer, word varchar(255), primary key (id) ); CREATE TABLE users_x_words( user_id integer, word_id integer, Primary Key (word_id,user_id), Foreign Key (word_id) REFERENCES words(id), Foreign Key (user_id) REFERENCES users(id) );

Sample:

 SELECT * FROM users_x_words JOIN words ON users_x_words.word_id = words.id WHERE users_x_words.user_id = 1

    You need to create the words table with the id and name fields, and you need to add a new word_id column to the users table.

    If your DB is relational, try not to store JSON in it.

    • Hi, this is StackOverflow in Russian , and it’s common in the community to communicate in Russian. If you need help with the translation, you can ask me. - D-side
    • pls translate this ^^ - Alexandru Gojinetchi
    • Done You may have multiple words selected. That is many-to-many between words and users. Or I didn’t quite understand your point, that's possible. I made a more-or-less direct translation. - D-side
    • I dont know if there is many to many, "that a user can select only one word". - Alexandru Gojinetchi Sep.
    • Or "User selects from 1 to 16 words." That goes to OP for clarification then. UPD: ah, I get it now, that's "add many words into the table". You do seem to be correct, yes. - D-side