generate unique array values

Question

hi all. I have a such problem which I couldn't solve in a good manner. My solution needs too much time and loop uses catastrophically big memory. So I need a help.

I have 3 mysql tables.

Gorups

• group_id
• group_name

games

• game_id
• game_name

questions

• question_id
• game_id
• question_name
• question_text

question_groups

• question_id
• group_id
• order

The problem is following.

The questions are asked in following manner. One after another. The problem is that I need to make a shufle funnction in php in such way, that I will assign each question to each group in that way, that for example the first question for each group will be unique, and no 2 or 3 groups will get the same question, the same for second, third, .....tenth question.

What is the most optimal solution for this?

Should I use somehow mysql function for that or php?

Answer 1

The easiest way to solve this issue is to define a unique constraint/index for the question_id column in QUESTIONS_GROUPS. This constrains the data so that the value can only occur once in the entire table - a user would receive a unique constraint error if they attempted to add a duplicate question_id value.

Here's the statement you'd need to use to define the constraint in MySQL:

CREATE UNIQUE INDEX question_idx BTREE ON QUESTION_GROUPS(question_id)

Edit:

If you need to support a question being asked only once per round, I'm assuming that the QUESTION_GROUPS.order column is what the round value is. Assuming that's correct, add the order column to the unique constraint/index to ensure unique pairs of values:

CREATE UNIQUE INDEX question_idx BTREE ON QUESTION_GROUPS(question_id, order)

If you made the group_id and question_id columns to be the composite primary key for the table, it won't work for your requirements because it would allow the question_id column value to be duplicated.

I would also define that the QUESTION_GROUPS.group_id not be allowed to be NULL, otherwise you could assign a question to a NULL (non-existent) group and would have to update the existing record or delete & recreate it with a valid group.

Reference:

• CREATE INDEX