Undefined variable with prepared statement

[gmc1103]

Hi

 

I need some help resolving this issue

 

This is my 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_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
}
$id = intval($_REQUEST['id']);
$dia = $_POST['dia'];
$sala = $_POST['sala'];
$hora = $_POST['hora'];
$query = "SELECT `id_dia` FROM  `semana_dias` where dia = ? " or trigger_error($mysqli->error);

if ($stmt = $mysqli->prepare($query)) {
    $stmt->bind_param('i', $dia);
    $stmt->execute();
    $stmt->bind_result($iddia);
    $stmt->fetch();
    printf("Error: %d.\n", $stmt->errno);
    $stmt->close();
}

$query1 = "SELECT `idsala` FROM `req_material_sala` WHERE sala = ? " or trigger_error($mysqli->error);
if ($stmt = $mysqli->prepare($query1)) {
    $stmt->bind_param('i', $sala);
    $stmt->execute();
    $stmt->bind_result($idsala);
    $stmt->fetch();
    printf("Error: %d.\n", $stmt->errno);
    $stmt->close();
}
echo "Dados";
echo "-----";
echo $iddia;
echo "-----";
echo $idsala;
echo "-----";
echo $hora;
echo "-----";

$query2 = "SELECT COUNT(id_dia) FROM sala_ocupacao WHERE id_dia = ? AND id_sala= ? AND id_tempo = ? " or trigger_error($mysqli->error); 
$result = $mysqli->prepare($query2);
$result->bind_param('sss', $iddia, $idsala, $hora);
$result->execute(); 
$numberofrows = $result->fetchColumn(); 
echo $numberofrows;
    


if ($numberofrows != 0) {
    echo json_encode(array('status' => 'error', 'message' => 'Este dia e tempo já estão registados na base de dados'));
} else {
    $query3 = "update sala_ocupacao set id_dia = ?, id_sala= ?, id_tempo= ? where id_ocup = '$id'" or trigger_error($mysqli->error);
    if ($stmt = $mysqli->prepare($query3)) {
        $stmt->bind_param('iii', $iddia, $idsala, $hora);
        if (!$stmt->execute()) {
            echo json_encode(array('status' => 'error', 'message' => 'Opppss...Os Registo(s) não foram gravado(s)'));
        }     
        $stmt->close();
        echo json_encode(array('status' => 'success', 'message' => 'O Registo foi atulaizado corretamente'));
    }
}

It's to update some records into the database but que queries are not keeping the result (iddia, idsala) to the last query

 

Notice: Undefined variable: iddia in /home/ebspma/public_html/gestaosalas/update_sala_ocup.php on line 37

Notice: Undefined variable: idsala in /home/ebspma/public_html/gestaosalas/update_sala_ocup.php on line 39

Fatal error: Call to a member function bind_param() on a non-object in /home/ebspma/public_html/gestaosalas/update_sala_ocup.php on line 46

 

 

The post are this one

POST http://ebspma.edu.pt/gestaosalas/update_sala_ocup.php?id=61 HTTP/1.1
Host: ebspma.edu.pt
Connection: keep-alive
Content-Length: 26
Cache-Control: max-age=0
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8
Origin: http://ebspma.edu.pt
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/42.0.2311.135 Safari/537.36
Content-Type: application/x-www-form-urlencoded
Referer: http://ebspma.edu.pt/gestaosalas/docCheckSalas.php
Accept-Encoding: gzip, deflate
Accept-Language: pt-PT,pt;q=0.8,en-US;q=0.6,en;q=0.4,es;q=0.2,fr;q=0.2

dia=Segunda&sala=1&hora=11

Any help please?

 

Thanks

[Muddy_Funster]

I use PDO myself, but looking up examples of mysqli->fetch() everything I found shows it being used in conjunction with a while loop, you could try modifying your code to follow that pattern and see if it helps.

[mac_gyver]

your prepare() statements are failing, probably because you haven't selected a database, and your code isn't doing anything when a prepare fails. your code keeps running and tries to use the result from the queries that have failed.

 

your if(){} conditional test for the first two prepare() statements needs to do something when there's a failure, such as reporting that there is an error, prevent the rest of the code from running, and during development display all the information there is about the error.

 

the third prepare() statement doesn't even have any logic around it to make sure it is working before trying to call the bind_param() method.

 

the or trigger_error($mysqli->error); statement you have on the end of the $query = "..." string isn't doing anything (a string cannot produce a mysqli error.) perhaps you meant to have that in an else {} clause for the if(){} conditional test around the prepare() statements?

[gmc1103]

Thanks mac_gyver

 

I have modified my code

<?php

header('Content-Type: application/json');
error_reporting(E_ALL | E_NOTICE);
ini_set('display_errors', '1');
$mysqli = new mysqli('xxxxx', 'xxxxx', 'xxxxx');
if (mysqli_connect_errno()) {
    trigger_error('Database connection failed: ' . mysqli_connect_error(), E_USER_ERROR);
}
$id = intval($_REQUEST['id']);
$dia = $_POST['dia'];
$sala = $_POST['sala'];
$hora = $_POST['hora'];
$sql = "SELECT `id_dia` FROM  `ebspma_paad_ebspma`.`semana_dias` where dia = ? ";
$stmt = $mysqli->prepare($sql);
if ($stmt === false) {
    trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $mysqli->error, E_USER_ERROR);
}
$stmt->bind_param('i', $dia);
$stmt->execute();
$stmt->bind_result($iddia);
$stmt->fetch();
$stmt->close();
echo ("Este é o idDia: " + $iddia);


$sql = "SELECT `idsala` FROM `ebspma_paad_ebspma`.`req_material_sala` WHERE sala = ? ";
$stmt = $mysqli->prepare($sql);
if ($stmt === false) {
      trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $mysqli->error, E_USER_ERROR);
}
$stmt->bind_param('i', $sala);
$stmt->execute();
$stmt->bind_result($idsala);
$stmt->fetch();
$stmt->close();
echo ("Este é o $idsala: " + $idsala);

$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', $iddia, $idsala, $hora);
$stmt->execute();
$stmt->bind_result($existe);
$stmt->fetch();
$stmt->close();
echo ("Este é o existe: " + $existe);


if ($existe != 0) {
    echo json_encode(array('status' => 'error', 'message' => 'Este dia e tempo já estão registados na base de dados'));
} else {

    $sql = "update `ebspma_paad_ebspma`. `sala_ocupacao` set id_dia = ?, id_sala= ?, id_tempo= ? where id_ocup = ?";
    $stmt = $mysqli->prepare($sql);
    if ($stmt === false) {
          trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $mysqli->error, E_USER_ERROR);
    }
    $stmt->bind_param('iiii', $iddia, $idsala, $hora, $id);
    if (!$stmt->execute()) {
        echo json_encode(array('status' => 'error', 'message' => 'Opppss...O Registo não foi atualizado'));
    }
    $stmt->close();
    echo json_encode(array('status' => 'success', 'message' => 'O Registo foi atualizado corretamente'));
} 

and now it does work

 

Thank you again