gmc1103 Posted June 10, 2015 Share Posted June 10, 2015 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 Quote Link to comment Share on other sites More sharing options...
Psycho Posted June 10, 2015 Share Posted June 10, 2015 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. Quote Link to comment Share on other sites More sharing options...
gmc1103 Posted June 10, 2015 Author Share Posted June 10, 2015 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 Quote Link to comment Share on other sites More sharing options...
Solution Psycho Posted June 10, 2015 Solution Share Posted June 10, 2015 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" Quote Link to comment Share on other sites More sharing options...
gmc1103 Posted June 10, 2015 Author Share Posted June 10, 2015 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 Quote Link to comment Share on other sites More sharing options...
Psycho Posted June 10, 2015 Share Posted June 10, 2015 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.