Take data from a table through an N:N relation

Asked

Viewed 89 times

1

I have a system made in PHP and Mysql where I have two types of registration, teacher and student.

On this platform, the teacher can add a student as "friend" and the student can also add a teacher as "friend". In addition, the teacher can also add content in which ONLY STUDENTS WHO HAVE A RELATIONSHIP (added as a friend) WITH THIS TEACHER will be able to see when they are logged in.

To do this, I have the tables students and teachers, with their respective data, and I also have an N:N table called student teacher, which contains the student and teacher id (since the student may have several teachers added and the teacher may have several students added).

Also, I have a table called content, containing the teacher id that added this content. When logged in as a student, I need to get the CONTENTS that have been added by the teachers that have relationship with this student. How do I do this using table N:N?

php mysql sql database

  • Is your question about how to build an SQL query? Or do you use a ORM? What have you tried? Be more specific and if possible include the code of what you tried to do.

  • It is an SQL query.

1 answer

0


So when the student logs in, you’ll pick up in this table N:N student teacher where his id appears, and the respective teacher ids, with that you’ve already picked up his friend list, so you’ll select in the table content, the contents that have the ids of the teachers that were returned in the first select. Try to do something similar to the example below. 

SELECT  t1.id, t2.id_professor, t3.conteudo
        FROM (select * from alunos where id= $iduser)
        t1
        inner join (select * from alunos_professores)
        t2
        on t1.id = t2.id_aluno
        inner join(select * from conteudos)
        t3
        on t2.id_professor = t3.id_professor

  • In this case, what would be the tables t1, t2 and T3? And it works to put a SELECT after a FROM?

  • t1, t2, T3 are names I gave to the selects , equal to the select * from students Where id= $iduser is the t1. select * from student_teachersis t2, and select * from student_teachersis T3, and in the first row where I place t1.id, t2.id_teacher, T3.content will be what will appear as a result of that entire query, if you want the title of the content to appear for example that is in T3, at the beginning you have to do so: t1.id, t2.id_professor, T3.conteudo, T3.titulo, and so on for all fields you want to display

  • select after the from works yes, sql understood q vc will search something in the result of that select

Browser other questions tagged php mysql sql database

You are not signed in. Login or sign up in order to post.