Help with SQL Query

cxam

Hi!

I'm facing an issue with a SQL query and I'm not able to create the query that I want (I can't figure it out). I need the query to do this:

It needs to select * from the table "usuarios" where the "Foto" and "FotoHuella" are null values and (here comes the problems) the user has to be able to filter the search so I have a few QComboBox and QLineEdits so the query should be like:

query.prepare("SELECT * FROM usuarios WHERE Foto IS NULL AND FotoHuella IS NULL AND Curso=:curso "
                  "AND Grupo=:grupo AND Nombre=:nombre AND Apellido1=:apellido1 AND Apellido2=:apellido2");

But it doesn't work because the filtering doesn't work (it always displays all the rows where "Foto=NULL" so ALL the rows are displayed.

Thanks in advance.

SGaist

Hi,

IIRC binding values are only available for INSERT query and stored procedure.

You should build your SELECT query e.g. QString's arg function.

cxam

@SGaist No I think you're wrong I used binding values with select a lot of times in my code, that's not the problem. The problem is the query doesn't filter as I want to be.

Thanks in advance.

SGaist

Good, I'll fix my memory then :)

Did you check what lastQuery returns ?

How are you using that query ?

cxam

@SGaist Query last is true so I think the problem is the query itself. I mean I don't know how to SELECT the table as I want, I need to SELECT from the table "usuarios" where the fields "Foto" and "FotoUser" are null but then I have to be able to filter it so I set "Nombre=:nombre1" OR "Curso=:curso1" but that ending statement makes the "WHERE NULL" unuseful because every row that has the binding values as I want will be displayed. Not only the ones with NULL values but ALL the rows

Paul Colby

@cxam said:

but then I have to be able to filter it so I set "Nombre=:nombre1" OR "Curso=:curso1"

In the top post, you have AND between each term, but here you say OR. OR would explain the issue.

If you're using OR, you probably want some braces, like:

SELECT * FROM usuarios WHERE Foto IS NULL AND FotoHuella IS NULL
AND (Curso=:curso OR Grupo=:grupo OR Nombre=:nombre OR Apellido1=:apellido1 OR Apellido2=:apellido2)

SGaist

lastQuery should return the actually query that ran against the database, that one would be interesting to see.

Good point of @paul-colby