Jump to content

msqli error


gmc1103

Recommended Posts

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?

 

Link to comment
Share on other sites

 

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 by Ch0cu3r
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.