MySQL Query to list hotel rooms as subgroup

Question

This is my first post in here and I hope that you can give me an idea how to resolve an issue. I have a hotel room database and i would like to list them on a grid. Each room has x number of beds and they are stored in the database in this format:

101A
101B
101C
102A
102B
103A

First three numbers represent the room number while A,B,C... the beds.

Im using the following query to list all the rooms:

$stmt = $db->prepare("SELECT PokojID, Symbol, Opis, Lozka, Active 
                        FROM pokoje 
                        WHERE Lozka = :Lozka 
                        OR :Lozka = '0' 
                        AND Active = '1' 
                        ORDER BY Symbol;");`

If i would like only the first room to be displayed i found out that this query works:

$stmt = $db->prepare("SELECT PokojID, 
                            Left(Symbol,3) as Symbol, 
                            Opis, count(Lozka) as Lozka, 
                            Active  
                        FROM pokoje 
                        WHERE Lozka = :Lozka 
                        OR :Lozka = '0' 
                        AND Active = '1' 
                        GROUP BY Left(Symbol, 3) 
                        ORDER BY Symbol;");

I am trying to return the room list as array in the following formar:

dp.resources = [
    { 
        name: "Room 101A", id: "1", expanded: true, children:[
            { name : "Room 101B", id : "2" },
            { name : "Room 101C", id : "3" }
        ] 
    },
    { 
        name: "Room 102A", id: "4", expanded: true, children:[
            {name : "Room 102B", id : "4" },
        ] 
    },
    { name: "Room 103a", id: "5" }
];

This is my php code:

$json = file_get_contents('php://input');
$params = json_decode($json);

$Lozka = isset($params->Lozka) ? $params->Lozka : '0';
//Hostel Mode
$stmt = $db->prepare("SELECT PokojID, Symbol, Opis, Lozka, Active 
                        FROM pokoje     
                        WHERE Lozka = :Lozka 
                        OR :Lozka = '0' 
                        AND Active = '1' 
                        ORDER BY Symbol;");
// Hotel Mode
//$stmt = $db->prepare("SELECT PokojID, Left(Symbol,3) as Symbol, Opis, count(Lozka) as Lozka, Active  FROM pokoje WHERE Lozka = :Lozka OR :Lozka = '0' AND Active = '1' GROUP BY Left(Symbol, 3) ORDER BY Symbol;");

$stmt->bindParam(':Lozka', $Lozka); 
$stmt->execute();
$rooms = $stmt->fetchAll();

class Room {}

$result = array();

foreach($rooms as $room) {
    $r = new Room();
    $r->id = $room['PokojID'];
    $r->name = $room['Symbol'];
    $r->capacity = intval($room['Lozka']);
    $r->status = $room['Opis'];
    $result[] = $r;
}

header('Content-Type: application/json');
echo json_encode($result);

Thank you in advance.

Why not just have a column called `bads` type int and give it a value. Why be so cryptic — RiggsFolly, Mar 05 '23 at 23:26
Also if you are using OR and AND in the same WHERE then you need to use parenthesis to get correct results. SO `WHERE Lozka = :Lozka OR :Lozka = '0' AND Active = '1'` Should be `WHERE (Lozka = :Lozka OR Lozka = '0') AND Active = '1'` — RiggsFolly, Mar 05 '23 at 23:34

score 0 · Answer 1 · answered Mar 06 '23 at 11:07

The answer referenced by RiggsFolly is a good one and worth reading, but not relevant to your issue.

I understand your query, but the confusion caused on here for those who do not understand it, is a good example of why doing things the "standard" way is a good idea.

You would almost certainly benefit from normalizing the relationship between rooms and beds. Storing beds in a table called rooms (pokoje) has a bit of a nasty smell eminating from it.

I have aliased the columns in the query to avoid reassigning them after the fact. All the columns in the SELECT list will be included in the room objects in the final JSON output. If you don't want the data in output, remove it from the SELECT list.

$json = file_get_contents('php://input');
$params = json_decode($json);

$Lozka = isset($params->Lozka) ? (int)$params->Lozka : 0;

// initialize array to hold our SQL params
$sqlParams = [];

// Alias the columns to whatever you want them to be, instead of reassigning them later
$sql = 'SELECT PokojID AS id, Symbol AS name, Opis AS status, Lozka AS capacity, Active
        FROM pokoje
        WHERE Active = 1';

// Let's only add the `Lozka` criterion when we want it to be applied
if ($Lozka > 0) {
    $sql .= ' AND Lozka = :Lozka';
    $sqlParams['Lozka'] = $Lozka;
}
$sql .= ' ORDER BY Symbol';

// Prepare, execute and fetch
$stmt = $db->prepare($sql);
$stmt->execute($sqlParams);
$rooms = $stmt->fetchAll(PDO::FETCH_OBJ);

$prevRoom = null;

foreach ($rooms as $i => &$room) {
    if ($prevRoom && (int)$room->name === (int)$prevRoom->name) {
        $prevRoom->expanded = true;
        $prevRoom->children[] = $room;
        unset($rooms[$i]);
    } else {
        $prevRoom = $room;
    }
}

header('Content-Type: application/json');
// the use of array_values() here is just to re-index the array so the keys are
// not included in the output from json_encode()
echo json_encode(array_values($rooms));

Works perfectly... amazing. Sorry i couldn't reply you earlier. That FOREACH was all i needed, and as well nice reference for future. — Trim Qemali, Mar 07 '23 at 11:42

MySQL Query to list hotel rooms as subgroup

1 Answers1