INSERTING one to many, many to one

[thomy30]

Hi guys.. I’am facing som sql problem here.

 

I’am trying to create  query there a teacher send message.. it can be homework or test.

 

Just say  there is 15 students in class 4A , 10 students in class 5A etc

 

I have some tables such as

 

Class table

 

Class_id  class_name

1                  4A

2                  5A

 

 

And staff table

 

Staff_id      class_id    firstname

1             1             

2             1               

3             2

4             2  etc

 

 

Homework table

 

Homework_id    staff_id  class_id  title

 

 

And the last one

 

Homework_response table

 

Response_id  , homework_id, question, response , old_id

 

Now , when the teacher create a content.. let say homework or test to class 4A then it creats only one id, right? And all students in class 4A get the same question with the same id number. I mean the same homework_id. Say homework_id in this case is 3

 

But when they response their homework_id 3 , they cant just create query to update only the response column I mean to homework_response.response , if they do, then  they will replace with eachother. I mean the last student who response will get his or her value to save. So what I understand is they have create INSERT quary so they get their own id. Right?

Now I want separate and show those responsed homeworks and non responsed homework in two different blocks.

First I created one column in Homework_response table with name old_id (the red one)  and when I INSERT I take with me the value of  homework_id and put it there so that column will not be NULL and by that I can get those responsed homework by queary

I

 

 

“SELECT h.homework_id, h.staff_id, h.subject,  hr.question,  hr.response, c.class_id, c.class_name 

FROM homework h ".

INNER JOIN homework_response hr ON h.homework_id = hr.homework_id ".

……..

……..

WHERE ……………. AND  h.old_id IS NOT NULL

ORDER BY …………. DESC “

 

So, when old.id is not null when there is a value in that column so it means it’s a response row.

Almost like that.

And those are together in one block. Just say block 1.

 

And in block 2 I want to show only non-responsed homeworks.

 

 

But I have problem to create queary the same to non responsed homeworks. Remember.. ONLY NON RESPONSED homeworks. I want them in block 2.

When I try to create quary then it shows non responsed and responsed together in block 2. Even those who is in block 1 they are in block 2 too.

They are there as non responsed homworks and students can response again and again to the same homework.

I want them to disappear from the list (block 2) when I response the homework.

I realy apricate even if you come with another suggestion, if there is easy way to handle this problem…… maybe by creating automatic different homework_id’s to the hole class. I mean all in class 4A get the same homework with different id’s, then it would be easy to seperat answered and non answered homeworks by putting response in the same row as they get from teachers. Please tell me if there is any solution...

The solution may could be “RDBMS many-to-many relationships” och save all responsed id’s in array and compare them in SQL selection.. but I have not such good enough knowlage about that.

 

I hope you understand my problem and I’am very new to PHP so please take easy when you explane.

Thank you all of you guys in advance for your time.