0

I've this resultset from SQL: enter image description here

My PHP code:

function getMasterBookRoomsaAvailability(){
    $DisponibilitaRoom = [
        'Resource' => null,
        'IntervalTime' => [],
    ];

    $conn = new mysqli($this->servername, $this->username, $this->password,$this->MySQLDB);
    $sql = "SELECT IDRoom,Nomeroom,Dayofweek,Nome,Aperto,OrarioApertura,OrarioChiusura,Durataminuti FROM room 
    INNER JOIN giorni ON room.IDRoom = giorni.FKRoom 
    INNER JOIN durategame ON room.IDRoom = durategame.FKRoom 
    WHERE room.Statobloccato <> 1";

    $result = $conn->query($sql);

    if ($result->num_rows > 0) {

    // output data of each row/*

        while($row = $result->fetch_assoc()) {
            $IDRoom = $row['IDRoom'];
            $Nomeroom = $row['Nomeroom'];
            $Dayofweek = $row['Dayofweek'];
            $Nome = $row['Nome'];
            $Aperto = $row['Aperto'];
            $OrarioApertura = $row['OrarioApertura'];
            $OrarioChiusura = $row['OrarioChiusura'];
            $Durataminuti = $row['Durataminuti'];
            $DisponibilitaRoom['Resource'] = $Nomeroom; 
            $DisponibilitaRoom['IntervalTime'][$Nome] = $this->selectTimesOfDay($OrarioApertura,$OrarioChiusura,$Durataminuti);
        }
    }
return $DisponibilitaRoom;
}

// Funzione: prende tutti gli orari di intervallo tra due date splittate dalla durata.
// Params: Array contenente Orario Apertura della room, Orario di chiusura della room, durata prenotabile
// Resultset: restituisce un Array contenente gli orari disponibili
function selectTimesOfDay($startTime,$endTime,$duratagame) {
    $hours = null;
    //var_dump($DisponibilitaRoom);
    $open_time = strtotime($startTime);
    $close_time = strtotime($endTime);
    $duratagame = $duratagame * 60;
    $output = "";
    for( $i=$open_time; $i<$close_time; $i+=$duratagame) {
        //if( $i < $now) continue;
            $output = date("H:i",$i);
            $hours[] = date('H:i', $i);
    }
    return $hours;
}
echo json_encode($RB->getMasterBookRoomsaAvailability());

My result is this: {"Resource":"Padel 1","IntervalTime":{"Lun":["11:00"],"Mar":["11:00"],"Mer":["11:00"],"Gio":["11:00","12:00","13:00","14:00","15:00","16:00","17:00","18:00"],"Ven":["11:00"],"Sab":["11:00"],"Sun":["09:00","10:00","11:00","12:00","13:00","14:00","15:00","16:00","17:00","18:00"]}}

but there are missing the "Tennis 1" resource and all it availability hours.
I dont know the semanthic right way, do you have any idea? I'm expecting something like:

Resources
- Padel 1
-- LUN ['09:00','10:00','11:00']
-- MAR ['09:00','10:00']
...
- Tennis 1
-- LUN ['09:00','10:00','11:00']
-- MAR ['09:00','10:00']

4
  • You just overwrite the data in your loop each time. Commented Oct 13, 2019 at 13:41
  • yes, it surely is overwritten, but how push the next "resource"? there are a row for each day of week Commented Oct 13, 2019 at 13:44
  • Possible duplicate of How to build a JSON array from mysql database Commented Oct 13, 2019 at 13:48
  • nbk i've not an error to generate a JSON, but how push next resource. please read the question before. Commented Oct 13, 2019 at 13:51

2 Answers 2

1

I would try changing this:

$DisponibilitaRoom['Resource'] = $Nomeroom; 
$DisponibilitaRoom['IntervalTime'][$Nome] = $this->selectTimesOfDay($OrarioApertura,$OrarioChiusura,$Durataminuti);

to this, to append the rooms rather than replace:

$DisponibilitaRoom[$Nomeroom]['IntervalTime'][$Nome] = $this->selectTimesOfDay($OrarioApertura,$OrarioChiusura,$Durataminuti);
Sign up to request clarification or add additional context in comments.

Comments

-1

The answer below was just an illustration geared towards resolving your issue.

In normal php queries you can do something like this below to get all json data returned by php

<?php

$host = "localhost"; 
$user = "ryour username"; 
$password = "your password"; 
$dbname = "your bd name";

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
echo "cannot connect to db";
}

$return_arr = array();
$query = "SELECT IDRoom,Nomeroom,Dayofweek,Nome,Aperto,OrarioApertura,OrarioChiusura,Durataminuti FROM room 
    INNER JOIN giorni ON room.IDRoom = giorni.FKRoom 
    INNER JOIN durategame ON room.IDRoom = durategame.FKRoom 
    WHERE room.Statobloccato <> 1";
$result = mysqli_query($con,$query);

while($row = mysqli_fetch_array($result)){
           $IDRoom = $row['IDRoom'];
            $Nomeroom = $row['Nomeroom'];
            $Dayofweek = $row['Dayofweek'];
            $Nome = $row['Nome'];
            $Aperto = $row['Aperto'];
            $OrarioApertura = $row['OrarioApertura'];
            $OrarioChiusura = $row['OrarioChiusura'];
            $Durataminuti = $row['Durataminuti'];



$return_arr[] = array("IDRoom" => $IDRoom,

"Nomeroom" => $Nomeroom,
"Dayofweek" => $Dayofweek,
"Dayofweek" => $Dayofweek,
"Nome" => $Nome,
"Aperto" => $Aperto,
"OrarioChiusura" => $OrarioChiusura,
"OrarioChiusura" => $OrarioChiusura,
"Durataminuti" => $Durataminuti,



);


}

// Encoding array in JSON format
echo json_encode($return_arr);

?>

To help answer your question, you can emulated the code above hence the emulated answer below. Try and see if it helps

//function getMasterBookRoomsaAvailability(){
    $DisponibilitaRoom = [
        'Resource' => null,
        'IntervalTime' => [],
    ];

$return_arr = array();
    $conn = new mysqli($this->servername, $this->username, $this->password,$this->MySQLDB);
    $sql = "SELECT IDRoom,Nomeroom,Dayofweek,Nome,Aperto,OrarioApertura,OrarioChiusura,Durataminuti FROM room 
    INNER JOIN giorni ON room.IDRoom = giorni.FKRoom 
    INNER JOIN durategame ON room.IDRoom = durategame.FKRoom 
    WHERE room.Statobloccato <> 1";

    $result = $conn->query($sql);

    if ($result->num_rows > 0) {

    // output data of each row/*

        while($row = $result->fetch_assoc()) {
            $IDRoom = $row['IDRoom'];
            $Nomeroom = $row['Nomeroom'];
            $Dayofweek = $row['Dayofweek'];
            $Nome = $row['Nome'];
            $Aperto = $row['Aperto'];
            $OrarioApertura = $row['OrarioApertura'];
            $OrarioChiusura = $row['OrarioChiusura'];
            $Durataminuti = $row['Durataminuti'];
            $DisponibilitaRoom['Resource'] = $Nomeroom; 
            $DisponibilitaRoom['IntervalTime'][$Nome] = $this->selectTimesOfDay($OrarioApertura,$OrarioChiusura,$Durataminuti);


$return_arr[] = array("IDRoom" => $IDRoom,

"Nomeroom" => $Nomeroom,
"Dayofweek" => $Dayofweek,
"Dayofweek" => $Dayofweek,
"Nome" => $Nome,
"Aperto" => $Aperto,
"OrarioChiusura" => $OrarioChiusura,
"OrarioChiusura" => $OrarioChiusura,
"DisponibilitaRoom" => $Nomeroom,
"DisponibilitaRoom1" => $this->selectTimesOfDay($OrarioApertura,$OrarioChiusura,$Durataminuti)



);
        }
    }
return $DisponibilitaRoom;
}

// Funzione: prende tutti gli orari di intervallo tra due date splittate dalla durata.
// Params: Array contenente Orario Apertura della room, Orario di chiusura della room, durata prenotabile
// Resultset: restituisce un Array contenente gli orari disponibili
function selectTimesOfDay($startTime,$endTime,$duratagame) {
    $hours = null;
    //var_dump($DisponibilitaRoom);
    $open_time = strtotime($startTime);
    $close_time = strtotime($endTime);
    $duratagame = $duratagame * 60;
    $output = "";
    for( $i=$open_time; $i<$close_time; $i+=$duratagame) {
        //if( $i < $now) continue;
            $output = date("H:i",$i);
            $hours[] = date('H:i', $i);
    }
    return $hours;
//}
echo json_encode($return_arr);

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.