How to use the values of a column present in Table A as the WHERE parameter of a SELECT in Table B?

Asked

Viewed 20 times

0

It is possible to make the SQL query $professores is made based on ID $row["id"] of the 5th line of the code below?

Contextualizing: this code displays a list of courses present in a BD, printing the name and ID of the course, with its respective teacher(s) (s) - if any. However, this teacher consultation should be made based on the role a teacher has within a discipline (which is done with the command $professores, but this query is only working if a manual ID value is added, and ideally all this is dynamic, so the interest in using the $row["id"] as a parameter).

Grateful from now on!

$cursos = "SELECT id, fullname FROM mdl_course WHERE visible=1";
    $resultCursos = $conn->query($cursos);
    $row = $resultCursos->fetch_assoc();
    
        while($row = $resultCursos->fetch_assoc()) {
            echo '<a href="http://meu-site.php?id='.$row["id"].'" target="_blank">';
            echo '<p class="h6">'.utf8_encode($row["fullname"]).'</p>';
            echo '<p class="professor"> Professor: ';
                            
            $professores = "SELECT firstname, lastname FROM mdl_user WHERE id IN(SELECT userid FROM mdl_role_assignments WHERE contextid IN (SELECT id FROM mdl_context WHERE 

/* instanceid='ESSE VALOR DEVE SER IGUAL AO ID DO CURSO QUE APARECE NA 5ª LINHA' */

AND contextlevel=50) AND roleid=3)";
            $resultProf = $conn->query($professores);           
            $row2 = $resultProf->fetch_assoc();                                                     
                            
                if ($resultProf->num_rows > 0) {
                    echo utf8_encode($row2["firstname"]). ' '.utf8_encode($row2["lastname"]).", ";
                } else {
                    echo "Sem professor cadastrado.";
                }
            
            echo '</p>';
            echo '<span>Acessar</span>';
            echo '</a>';                            
        }

php sql moodle

1 answer

0

I just figured out how to do it!

I set the following variable:

$iddocurso = $row["id"];

And in the SQL command, I used:

instanceid='$iddocurso'

Worked!

Browser other questions tagged php sql moodle

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