gmc1103 Posted June 9, 2015 Share Posted June 9, 2015 Hi I have this php file with some queries but is acting strange because when the values are passed from a form everything is correct and the Select count should be 1 and gives 0 This the code <?php header('Content-Type: application/json'); error_reporting(E_ALL | E_NOTICE); ini_set('display_errors', '1'); $mysqli = new mysqli('localhost', 'xxxxxx', 'xxxxx'); if (mysqli_connect_errno()) { trigger_error('Database connection failed: ' . mysqli_connect_error(), E_USER_ERROR); } $iduser = filter_input(INPUT_POST, 'iduser'); $inicio = filter_input(INPUT_POST, 'inicio'); $fim = filter_input(INPUT_POST, 'fim'); $data = filter_input(INPUT_POST, 'data'); $equip = filter_input(INPUT_POST, 'equip'); $dia = filter_input(INPUT_POST, 'dia'); $sala = filter_input(INPUT_POST, 'sala'); $sql = "SELECT COUNT(id_dia) FROM `ebspma_paad_ebspma`. `sala_ocupacao` WHERE id_dia = ? AND id_sala= ? AND id_tempo = ? "; $stmt = $mysqli->prepare($sql); if ($stmt === false) { trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $mysqli->error, E_USER_ERROR); } $stmt->bind_param('iii', $dia, $sala, $inicio); $stmt->execute(); $stmt->bind_result($existe); $stmt->fetch(); $stmt->close(); if ($existe != 0) { echo json_encode(array('status' => 'error', 'message' => 'Oppss....Sala indisponível')); } else { $sql = "SELECT COUNT(*) AS total FROM `ebspma_paad_ebspma`.`req_material_reserva` WHERE `req_material_reserva`.`idequipamento` = ? AND `ebspma_paad_ebspma`.`req_material_reserva`.`idsala` = ? AND (((`req_material_reserva`.`idtempoInicio` BETWEEN ? AND ?) AND (`req_material_reserva`.`idTempoFim` BETWEEN ? AND ?)) OR (`req_material_reserva`.`idtempoInicio` <= ? AND `req_material_reserva`.`idTempoFim` >= ?)) AND `req_material_reserva`.`data` = ?"; $stmt = $mysqli->prepare($sql); if ($stmt === false) { trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $mysqli->error, E_USER_ERROR); } $stmt->bind_param('iiiiiiiis', $equip, $sala, $inicio, $fim, $inicio, $fim, $inicio, $fim, $data); $stmt->execute(); $stmt->bind_result($total); $stmt->fetch(); $stmt->close(); if ($total != 0) { echo json_encode(array('status' => 'error', 'message' => 'Oppss....equipamento ou sala indisponível')); } else{ echo json_encode(array('status' => 'success', 'message' => 'O equipamento está disponível')); } } The first query run with this values checkEquipLivre.php?iduser=1261&dia=1&sala=6&equip=16&inicio=1&fim=2&data=2015-06-15 And from my database, the values dia = 1, and sala = 6 and inicio = 1 gives me 1 SELECT COUNT( id_dia ) FROM `ebspma_paad_ebspma`.`sala_ocupacao` WHERE id_dia =1AND id_sala =6AND id_tempo =1 + Opções COUNT(id_dia) 1 And from my php file i have the response 0 Whats wrong? Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted June 9, 2015 Share Posted June 9, 2015 (edited) The first query run with this values checkEquipLivre.php?iduser=1261&dia=1&sala=6&equip=16&inicio=1&fim=2&data=2015-06-15 If those values are from the url then you should be using INPUT_GET not INPUT_POST for those values in filter_input Edited June 9, 2015 by Ch0cu3r Quote Link to comment Share on other sites More sharing options...
Barand Posted June 9, 2015 Share Posted June 9, 2015 When selecting from a single table there is no need to qualify all the column names with a table prefix. If you are looking for those existing events that overlap the range $inicio - $fim then you can simplify your 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 Quote Link to comment Share on other sites More sharing options...
gmc1103 Posted June 9, 2015 Author Share Posted June 9, 2015 Hi Barand Thank you, really it was the problem. Thanks again Regards 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.