Problem with SQL query assembly

Question

Problem with SQL query assembly

Navigation

#1 by (2 votes)

2

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

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());
    }

In 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);
    }

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

Would anyone have any ideas?

php mysql query

asked by anonymous 08.06.2014 / 20:53

1 answer

Calculate date difference and compare with integer field in lambda expression Use property R1C1

score 2 · Accepted Answer

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

Example selecting two checkboxes (Portuguese and mathematics) 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 get 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 you also have to repeat Disciplinas.Nome in each condition:

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

Resulting from 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 to make a echo of the variable with the generated query and copying this code into the program you use to manage your database (phpMyAdmin or MySQL Workbench for example)