query with two between error

[gmc1103]

Hi

 

I'm having a strange error regarding the following query

SELECT COUNT(*) AS total 
            FROM `ebspma_paad_ebspma`.`req_material_reserva`
            WHERE `ebspma_paad_ebspma`.`req_material_reserva`.`idsala` = 61
            AND (((`ebspma_paad_ebspma`.`req_material_reserva`.`idtempoInicio` BETWEEN 3 AND 3) AND (`ebspma_paad_ebspma`.`req_material_reserva`.`idTempoFim` BETWEEN 3 AND 3))
            OR (`ebspma_paad_ebspma`.`req_material_reserva`.`idtempoInicio` <= 3 AND `ebspma_paad_ebspma`.`req_material_reserva`.`idTempoFim` >= 3))
            AND `ebspma_paad_ebspma`.`req_material_reserva`.`data` = "2015-10-23"

The count gives me one but my table is empty...

 

idreserva  idutilizador  idsala  idtempoInicio  idtempoFim  idequipamento  data        

---------  ------------  ------  -------------  ----------  -------------  ------------

 

 

Fields translation

 

id_booking, id:user, id_classroom, id_initialtime(timestamp), id_finaltime(timestamp), id_equipment, date

 

So what's wrong??

 

Thanks

Edited October 20, 2015 by gmc1103

[Barand]

 

The count gives me one but my table is empty...

 

How are you running the query and processing the result?

[gmc1103]

Hi Barand

 

I'm running the query in phpmyadmin and the result is to check if i can make a booking or not (0 - yes) (1- no)

[benanamen]

Post an sql dump of your database.

Edited October 20, 2015 by benanamen

[mac_gyver]

is the query giving you one row, which is what a count(*) will do since there's no GROUP BY in the query, with the COUNT(*) value in the row being a zero, or is the COUNT(*) value in the row a 1?

[gmc1103]

This is my sql dump

 

http://ebspma.edu.pt/database.sql

 

mac_gyver

 

I'm lost with your question now...sorry

I wnat to check if i have a booking in that classroom, and this date and time...

[Barand]

Yes, you are correct. It does return a count of 1. So changing the query to see what it is counting gives

SELECT *  
    FROM `ebspma_paad_ebspma`.`req_material_reserva`
    WHERE `ebspma_paad_ebspma`.`req_material_reserva`.`idsala` = 61
    AND (((`ebspma_paad_ebspma`.`req_material_reserva`.`idtempoInicio` BETWEEN 3 AND 3) AND (`ebspma_paad_ebspma`.`req_material_reserva`.`idTempoFim` BETWEEN 3 AND 3))
    OR (`ebspma_paad_ebspma`.`req_material_reserva`.`idtempoInicio` <= 3 AND `ebspma_paad_ebspma`.`req_material_reserva`.`idTempoFim` >= 3))
    AND `ebspma_paad_ebspma`.`req_material_reserva`.`data` = "2015-10-23"
+-----------+--------------+--------+---------------+------------+---------------+------------+
| idreserva | idutilizador | idsala | idtempoInicio | idtempoFim | idequipamento | data       |
+-----------+--------------+--------+---------------+------------+---------------+------------+
|         3 |          719 |     61 |             3 |          3 |          NULL | 2015-10-23 |
+-----------+--------------+--------+---------------+------------+---------------+------------+

which. I believe, explains the count of 1

[gmc1103]

Hi Barand

 

Thanks, the problem is with sqlyog i did a refresh in database and i had 0 booking but the query in the website was giving 1.....strange