Join table columns where id equals variable

Question

Join table columns where id equals variable

Navigation

#1 by (1 votes)

0

I have 4 tables they already have foreign key.

aluno:
id
matricula
username
curso_id          // referencia a id_cursos da tabela cursos, coluna 
id_cursos.

cursos: 
id_cursos
nome_cursos

modulos:
id_modulos
nome_modulos
cursos_id         //faz referencia a tabela cursos, coluna id_cursos.

materias:
id_materias
nome_materias
modulos_id       // faz referencia a tabela modulos, coluna id_modulos.

I need to return the columns of 1 user:

    <?php
    function get_user_data(){
      $user = $_SESSION["Login"];
      $pass = $_SESSION["Password"];
      $pdo = cnx();
        $select = $pdo->query("select * from aluno where matricula = '$user' and password ='$pass' ");
        $select->execute();
        $rowCount = $select->rowCount();
        if ($rowCount == 0):
            header("Location:index.php?out=1");
        else:
            return $select;
        endif;
    }

    function stats_session(){
      session_start();
      if (isset($_SESSION['Login']) and isset($_SESSION['Password'])) {
        $data = get_user_data();
        $data->execute();
        foreach ($data as $key) {
          $curso_id = $key['id_curso'];
          $pdo = cnx();
          $select = $pdo->query("SELECT C.*, A.*, MO.*, MA.* FROM aluno A JOIN cursos C on C.id_cursos = '$curso_id' JOIN modulos MO on MO.id_modulos = '$curso_id' JOIN materias MA on MA.id_materias = MO.id_modulos");
          $select->execute();
          return $select;
        }
      } else {
        header('Location:index.php');
      }
    }
    $query = stats_session();
    $query->execute();
    foreach ($query as $key) {
        var_dump(json_encode($key));
    }
    ?>

is returning this:

C:\wamp64\www\frequencia.php:6:string '{"id_cursos":"1","0":"1","nome_curso":"Assistente Administrativo","1":"Assistente Administrativo","id":"1","2":"1","matricula":"1234","3":"1234","password":"1234","4":"1234","username":"2626","5":"2626","id_curso":"1","6":"1","id_modulos":"1","7":"1","nome_modulos":"modulo basico","8":"modulo basico","cursos_id":"1","9":"1","id_materias":"1","10":"1","nome_materias":"Introdu\u00e7\u00e3o a adm","11":"Introdu\u00e7\u00e3o a adm","modulos_id":"1","12":"1"}' (length=458)

C:\wamp64\www\frequencia.php:6:string '{"id_cursos":"1","0":"1","nome_curso":"Assistente Administrativo","1":"Assistente Administrativo","id":"2","2":"2","matricula":"4321","3":"4321","password":"4321","4":"4321","username":"Usu\u00e1rio 2","5":"Usu\u00e1rio 2","id_curso":"2","6":"2","id_modulos":"1","7":"1","nome_modulos":"modulo basico","8":"modulo basico","cursos_id":"1","9":"1","id_materias":"1","10":"1","nome_materias":"Introdu\u00e7\u00e3o a adm","11":"Introdu\u00e7\u00e3o a adm","modulos_id":"1","12":"1"}' (length=478)

Only the first one is correct, it is returning the data of the 2 users that I have in the student table, how do I return only what is in the variable $ course_id of the table column column course_id?

php mysql sql

asked by anonymous 04.11.2017 / 17:15

1 answer

Datatable Jquery Print Specific Elements Check changes in a table html

score 1 · Accepted Answer

Problem in sql query of function below:

function stats_session(){
      session_start();
      if (isset($_SESSION['Login']) and isset($_SESSION['Password'])) {
        $data = get_user_data();
        $data->execute();
        foreach ($data as $key) {
          $curso_id = $key['id_curso'];
          $pdo = cnx();
          $select = $pdo->query("SELECT C.*, A.*, MO.*, MA.* 
FROM aluno A JOIN cursos C on C.id_cursos = A.curso_id and A.curso_id = '$curso_id' 
JOIN modulos MO on MO.id_modulos = '$curso_id' 
JOIN materias MA on MA.id_materias = MO.id_modulos");
          $select->execute();
          return $select;
        }
      } else {
        header('Location:index.php');
      }
    }
}

As can be seen, only the C.id_cursos = A.curso_id and A.curso_id = '$curso_id' strand has been changed to relate the student and course tables, and to filter through the foreign key present in the student.