Sql query problem

[gmc1103]

Hi

 

I'm looking to build an effective query but i'm having a problem.

Barand suggest me this query

SELECT COUNT(*) AS total 
FROM `ebspma_paad_ebspma`.`req_material_reserva` 
WHERE `idequipamento` = $equip 
    AND `idsala` = $sala
    AND `idtempoInicio` < $fim 
    AND `idTempoFim` > $inicio
    AND `data` = $data

But i'm having this problem

 

 

 

But with this query

SELECT COUNT( * ) AS total FROM  `ebspma_paad_ebspma`.`req_material_reserva`  WHERE  `idequipamento` IS NULL OR  `idequipamento` =12
AND  `idsala` =13 AND  `idtempoInicio` <2 AND  `idTempoFim` >2 AND  `data` =  "2015-06-12"

I still have one result, but i don't have any "idtempoInicio" with 2 and any "idTempoFim" with 2

 

So the this query gives wrong result

 

Any help

[Psycho]

I think you misunderstand how a COUNT() query will work. I am assuming you are counting the number of records in the result set - which will always be 1. You will ALWAYS get a result - that result will contain the number of the COUNT() calculation. Using the example data and query you have above you should get a result with one record. That one record will contain a value of 0 - because there were no matching records. You need to extract the value from that result to get the number you are after.

[gmc1103]

Hi Psycho

 

Thanks for your answer

Yes, i know i must always a value (0,1,2,3,etc) but in this case it should be 0 and i get 1

 

Comando SQL: SELECT COUNT(*) AS total FROM `ebspma_paad_ebspma`.`req_material_reserva` WHERE `idequipamento` is null or `idequipamento` = 12 AND `idsala` = 13 AND `idtempoInicio` < 2 AND `idTempoFim` > 2 AND `data` = "2015-06-12";
Registos: 1

total   1
 

 

And in my database i don't have  i don't have any "idtempoInicio" with 2 and any "idTempoFim" with 2

[Psycho]

Ah,

 

The problem is the OR condition. Operators have a process in how they are interpreted. For AND/OR operators that are interpreted from left to right. For an OR operator, if the condition(s) to the left are True or if the condition(s) to the right are True - then the result is True. Your conditions are being interpreted as this

 

 

If `idequipamento` IS NULL
 
OR
 
`idequipamento` =12
 AND  `idsala` =13
 AND  `idtempoInicio` <2
 AND  `idTempoFim` >2
 AND  `data` =  "2015-06-12"

 

The first record matches the condition because idequipamento is NULL.

 

You need to use parenthesis to group conditions to be interpreted how you wish them to be interpreted

 

 

SELECT COUNT( * ) AS total
 
FROM  `ebspma_paad_ebspma`.`req_material_reserva` 
 
WHERE  (`idequipamento` IS NULL OR  `idequipamento` =12)
  AND  `idsala` = 13
  AND  `idtempoInicio` <2
  AND  `idTempoFim` >2
  AND  `data` = "2015-06-12"

[gmc1103]

Thank you Psycho

 

I didn't realise that i had to use of parenthesis 

I'm guessing i'm learning everyday with this forum.

 

Thanks again

[Psycho]

 

I didn't realise that i had to use of parenthesis 

 

It's a lot like order of operations in math. If I want to add 2 and 3 and then multiple the result by 4, this will not work:

 

2 + 3 * 4

 

In math, multiplication and division are performed first. So the result of that would be

 

2 + 3 * 4  =  2 + 12  =  14

 

To get the intended result, you would have to enclose the addition in parenthesis

 

(2 + 3) * 4  =  5 * 4  =  20