-2

I am working on a project that requires a MySQL database.

The project is a quiz/study site and it needs to store a users login data and then a list of the quizes they have saved, and their score for each question on each quiz they have saved. So say we have user 1. I want to go to a table called "user_content" and then be able to see which quizes they have saved and their scores for those quizes.

The quizes will be made in a unique database, and referenced via a foreign key.

The issue is, I want to have a fixed number of columns in the "user_content" section, so I need just one column that has all the foreign keys for the quizes that a user has saved. I don't want a system where you have column "quiz-1" and "quiz-2" is there a way to just have one column that stores a list or array of all the quiz foreign keys? The same goes for storing the user's scores for their favorite quizes, I want a single column that will store an array/list of all their scores. Is this possible in MySQL and what is the best way to achieve this? (This is using php, not javascript)

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Pegladon
  • 503
  • 1
  • 3
  • 12
  • 1
    update your question with what you have done so far ? and also update your question with database structure only explaination is not sufficient for solution. – Bhavin Dec 11 '16 at 08:33
  • http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574 – e4c5 Dec 11 '16 at 09:00
  • See normalisation.... – Strawberry Dec 11 '16 at 09:11

1 Answers1

0

So as in the comments this could be accomplished in a well defined RDBMS structure for example: table for the quiz ID and then line item entries for each quiz foreign key back to the quiz ID table.

To answer your question you can store an array as json into a MySQL database by passing your PHP array into json_encode() then inserting that into the MySQL field. In the long run this could impact performance and flexibility when writing queries. You would have to run json_decode() back in PHP to restore the structure of the array. But this would solve the requirements of the question.

lemoney
  • 161
  • 1
  • 10