Problem trying to add multiple choose to mysql

I’m facing a big problem here. My hotel company purchased a Booking system made with PHP from a South African company, their contract expired now they are now charging a lot of money to update the contract and add new functions that we need, so now all the burden is left for me… etc. So I have this table:

Root Table

and I want to add a multiple type of room at each room. E.g.: Room 1, Type: Simple, Two Beds, Three Beds Table coode:

<div class="table-responsive">
        <table id="datatable-fixed-header" class="table datatable-show-all table-striped table-bordered">
            <thead>
                <tr>
                    <th>Nº</th>
                    <th>Floar</th>
                    <th>Room No.</th>
                    <th>Price per Night (&nbsp;In&nbsp;<?php echo $currency_symbol; ?>&nbsp;)</th>
                    <th>Price per Week (&nbsp;In&nbsp;<?php echo $currency_symbol; ?>&nbsp;)</th>
                    <th>Type</th>
                    <th>State</th>
                    <th>Action</th>
                </tr>
            </thead>
            <tbody>
                <?php
                if (count($room_details) > 0) {
                    $s_no = 1;
                    foreach ($room_details as $room) {


                        echo'<tr>
                                    <td>' . $s_no . '</td>
                                    <td>' . $room["property_name"] . '</th>
                                    <td>' . $room["room_number"] . '</th>
                                    <td class="text-right format_currency_td">' . $room["room_standard_night_rate"] . '</td>
                                    <td class="text-right format_currency_td">' . $room["room_standard_weekly_rate"] . '</td>
                                    <td>' . $room["room_type_name"] . '</td>';

                        if ($room["room_status"] == "AVAILABLE")
                            echo'<td><span class="label label-success">' . $room["room_status"] . '</span></td>';
                        else if ($room["room_status"] == "OCUPADO")
                            echo'<td><span class="label label-info">' . $room["room_status"] . '</span></td>';
                        else if ($room["room_status"] == "MANUNTEINCE")
                            echo'<td><span class="label label-warning">' . $room["room_status"] . '</span></th>';
                        else if ($room["room_status"] == "OUT OF SERVICE")
                            echo'<td><span class="label label-danger">' . $room["room_status"] . '</span></td>';
                        echo'<td>';

                        if ($room["room_status"] != "OUT OF SERVICE")
                            echo'<div class="th_options_kit">
                                            <span class="th_options btn bg-teal-400 btn-icon btn-rounded btn_edit_room" data-room="' . $room["room_id"] . '" data-popup="tooltip" title="Edit Room" data-placement="left"><i class="icon-pen"></i></span>
                                            <span class="btn bg-danger-400 btn-icon btn-rounded btn_delete_room" data-room="' . $room["room_id"] . '" data-popup="tooltip" title="Delete Room" data-placement="left"><i class="icon-bin"></i></span>
                                        </div>';
                        else if ($room["room_status"] == "OUT OF SERVICE")
                            echo'<div class="th_options_kit">
                                            <span class="th_options btn bg-teal-400 btn-icon btn-rounded btn_edit_room" data-room="' . $room["room_id"] . '" data-popup="tooltip" title="Room is out of service" data-placement="left"><i class="icon-pen"></i></span>

                                        </div>';
                        echo'</td>
                                </tr>';

                        $s_no++;
                    }
                }else {

                }
                ?>
            </tbody>
        </table>
    </div>

Php Code:

public function getRoom($where = FALSE) {

     $this->db->select('room.`room_id`, '
             . 'room.`property_id`, '
             . 'room.`room_number`, '
             . 'room.`room_type`,'
             . 'room.`room_standard_night_rate`,'
             . 'room.`room_standard_weekly_rate`,'
             . 'room.`room_status`,'
             . 'prop.`property_name`,'
             . 'room_type.`room_type_name`'
     );
     $this->db->from('hm_room room');
     $this->db->join('hm_room_type room_type', 'room.`room_type` IN (room_type.`room_type_id`)');
     $this->db->join('hm_property prop', 'room.`property_id` IN (prop.`property_id`)');

     if ($where !== FALSE) {
         foreach ($where as $whr) {
             $this->db->where_in($whr["column_name"], $whr["data"]);
         }
     }

     $this->db->order_by("prop.`property_name`", "ASC");
     $this->db->order_by("room.`room_number`", "ASC");

     $query = $this->db->get();
     return $query->result_array();
 }

Help me solve this please.

1 answer

  • answered 2018-12-06 09:34 The Chase

    I hope i understand your question, your table is currently just showing Room Type e.g. Simple. Is what you want it to show now "Simple, Two Bed" instead of just "Simple" ?

    Then what you need to do is concatenate your fields. I can't seem to see what the database field for "Two Bed", but assuming it's "room_beds", then you need to do the below:

    First step is to add the database field name to the query, assuming the name of the field in the database is called "room_beds"

    $this->db->select('room.`room_id`, '
    . 'room.`property_id`, '
    . 'room.`room_number`, '
    . 'room.`room_type`,'
    . 'room.`room_standard_night_rate`,'
    . 'room.`room_standard_weekly_rate`,'
    . 'room.`room_status`,'
    . 'prop.`property_name`,'
    . 'prop.`room_beds`,' -- Add the here
    . 'room_type.`room_type_name`');
    

    Then finally you need to concatenate the room_beds field to the room_type_name that you already have as below:

    <td>'. $room["room_type_name"] .' ,'. $room["room_beds"] .'</td>';