msqli error

[gmc1103]

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 =1
AND id_sala =6
AND id_tempo =1

 

 

+ Opções

 

COUNT(id_dia)   1

 

 

And from my php file i have the response 0

 

Whats wrong?

 

[Ch0cu3r]

 

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

[Barand]

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

[gmc1103]

Hi Barand

 

Thank you, really it was the problem.

 

Thanks again

 

Regards