Homework questions like this will generally not elicit answers with code. Stack Exchange is not a place where you can come to get others to do your homework for you. However you have only asked for pointers of where to look, so to that end, I will inform you that what you are looking for is called a many-to-many relationship, and requires a third table that maps table 1 to table 2. Usually, this third table just has two columns, each containing the primary key of one of the main tables, and the primary key of this table covers both columns.
Read up:
In your case, temporary tables could prove to be a challenge.
Specifically, temporary tables (created by CREATE TEMPORARY TABLE ...
) are bound to the current database connection, which can be difficult to maintain with PHP - a single web session could use one or many database connections.
I would suggest creating a static table in your database with extra columns, for example:
Temporary student list
web_user_id, matriculation_number, first_name, last_name
This one would allow each user to have a temporary student list, which would persist across database connections and would behave nicely with PHP. The data could exist between user logins if that was useful.
Temporary student list
web_session_id, matriculation_number, first_name, last_name, created_date
This might be more useful if you don't want to attach the list to a particular user. PHP's session handle should be more persistent than a database connection(s), and the created_date would be useful to allow you to do some periodic or automatic cleanups (e.g. get rid of anything older than a day).
Alternatively, you could store nothing in the database. You could create your query entirely within in PHP, and pass the student IDs inline:
SELECT columns FROM STUDENT INNER JOIN EXAM ON STUDENT.ID = EXAM.STUDENT_ID WHERE STUDENT.MATRICULATION_NUMBER IN (123, 234, 345, 456, 567...)
Best Answer
TL;DR - eventually figured out how to do this in a single pass - that answer's at the end. I left the original answer in place in order for people here to maybe learn from my own (creaking ;-) ) thought processes!
Update: there's an alternative answer provided below.
This proved to be quite tricky. To solve this, I did the following (see fiddle here)
I created tables:
the text of the questions doesn't really matter for the purposes of this demonstration:
Then the assigned question part:
Then, to generate a random list of student_question combinations, i.e. filling the assigned_question table, I used two
RECURSIVE WITH
s (orCTE
s):The important bit here is the
RAND()
function which randomly orders the question part of the result set. You assign a student a random student number and then tell them to select from the master table according to their id number.Now, I can't guarantee that every student will have a completely different set of questions, but the majority of them should have a different list of questions - if you don't allow them to speak to each other and tell them to answer all questions, in order, even if the answer is blank, then you have a reasonable way of checking that they were answering their own set of questions. The few students who do have matching question lists won't be able to communicate anyway!
then, join this assigned_question table to the test_question table thus:
Result (sample, see fiddle for full results):
So, we can see that student 1 will have to answer questions 2 and 5 first whereas student 2 will be answering questions 4 and 3 first (full results below).
This is probably the best you can do in pure MySQL.
Update - single pass query:
I've "fiddled" with this a bit more (pardon the pun! :-) ) and it can in fact be done in one pass, as follows:
(Aside: also see this more elegant fiddle which more closely resembles my second answer).
Result:
So, student 1 will answer questions 3 and 2 first and student 2 will answer 4 and 3 first. On inspection, the results I generated with this last fiddle show that every one of the 6 students has the first 2 questions different, so it appears to be working well. Remember however that the
RAND()
function does not guarantee this and there may be students with the same question order - but with 30 students and 20 questions, this is highly unlikely! HTH! p.s. welcome to the forum! :-)Alternative Answer:
What you can do is "stagger" (see explanation of results below) the questions asked of the students. All the necessary DDL table construction and DML data is at the bottom of the post - I'll just go through the "core" of the SQL below. Fiddle available here.
The key bit here is the modulus (%) operator and adding the s_id and q_id. The whole
CONCAT
bit to get the ordinals is just showing off! :-) Ordinal snippet found here.Results:
So, as we can see, the first student's first question is question_id 1 which is question_1_text. "So what?", you might say, that's not what the original question was aksing!
But, when we look at the 2nd student's questions we get this:
Now, the 2nd student's first question is question_id 2, i.e. question 2 which is not the same as his neighbour's 1st question - which is what the OP asked for!
The cool thing is, if we look at the "interface" between, say students 4 and 5, we get this:
The last questions answered by student 4 are 2 & 3 - i.e. the questions have "wrapped around" - so no student misses any question.
This was really just an intellectual exercise. For (close to) true randomness, my first answer is better - the only advantage I can think of is that it would make distributing the papers easier - all the invigilators (people who supervise exams) would have to do is move one sheet to the back at each new desk. Hope you enjoyed! :-)
=============================== DDL AND DML ================