Problem with mounting SQL query

Asked

Viewed 129 times

2

I have a table of "disciplines", a table of "students" and a table of "students" where these students may be enrolled in one or more disciplines. These disciplines are shown as checkboxes on the search screen to serve as a filter. Now I need to list all the values of the "students" table according to the selected disciplines, but I’m not getting.

This is the Query I’m doing:

    public function buscaAvancada ($Busca, $Condicao)
    {
        $oConexao = new conexaoclass();
        $oConexao -> abrirConexao();
        $sql = "SELECT * FROM Alunos 
            INNER JOIN Disciplinas
            WHERE Alunos.Nome LIKE '%$Busca%'  
            AND Disciplinas.Nome = '$Condicao';";

        $this -> resultado = mysql_query($sql, $oConexao -> getConn());
    }

On the search screen I’m doing the following test:

    if (empty($_POST['chkDisciplina'])) {
        $Busca = $_POST['txtbusca'];
        $_POST['chkDisciplina'] = '1';

        $oAluno = new alunosclass();
        $oAluno -> listar($Busca);
    }
    else{
        $Busca = $_POST['txtbusca'];
        $arrayFiltro = $_POST['chkDisciplina'];

        $separador = ' AND ';
        $Condicao = implode( $separador, $arrayFiltro );

        $oAluno = new alunosclass();
        $oAluno -> buscaAvancada($Busca, $Condicao);
    }

Now the problem is in the filter part, because if I just type a txt in the search with all checkboxes cleared it works perfectly.

Would anyone have any idea?

php mysql query

  • The relationship between Students and Disciplines is 1.. N ?

  • No, it’s N...N for students to have as many subjects as they want and vice versa

1 answer

2


Your condition separator is not properly separating the string passed via POST, which results in a query with no results.

Example by selecting two checkboxs (Portuguese and math) with the student 'Juquinha'

SELECT * FROM Alunos 
INNER JOIN Disciplinas
WHERE Alunos.Nome LIKE '%juquinha%'  
AND Disciplinas.Nome = 'portugues AND matematica';

Change your tab to $separador = "' AND '"; and you will have the following result:

SELECT * FROM Alunos 
INNER JOIN Disciplinas
WHERE Alunos.Nome LIKE '%juquinha%'  
AND Disciplinas.Nome = 'portugues' AND 'matematica';

OBS: After a quick test, I believe we also need to repeat the Disciplinas.Nome in every condition:

$separador = "' AND Disciplinas.Nome = '";

Resulting in the query:

SELECT * FROM Alunos 
INNER JOIN Disciplinas
WHERE Alunos.Nome LIKE '%juquinha%'  
AND Disciplinas.Nome = 'portugues' AND Disciplinas.Nome = 'matematica';

A good way to debug your querys is by making a echo variable with the generated query and copying this code in the program you use to manage your database (phpMyAdmin or Mysql Workbench for example)

Browser other questions tagged php mysql query

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