0

I have database that store items of seats numbers in single column.How do i retrieve these seats numbers in json format from given data which are sent as parameters $tripIdNo , $fleetRegNo , $newSeats ... I have tried this one but not succeed

private function checkBooking($tripIdNo = null, $fleetRegNo = null, $newSeats = null)
    {
        //---------------fleet seats----------------
        $fleetSeats = $this->db->select("
                total_seat, seat_numbers, fleet_facilities
            ")->from("fleet_registration")
            ->where('reg_no', $fleetRegNo)
            ->get()
            ->row();

        $seatArray = array();
        $seatArray = array_map('trim', explode(',', $fleetSeats->seat_numbers));

        //-----------------booked seats-------------------
        $bookedSeats = $this->db->select("
                tb.trip_id_no,
                SUM(tb.total_seat) AS booked_seats, 
                GROUP_CONCAT(tb.seat_numbers SEPARATOR ', ') AS booked_serial 
            ")
            ->from('tkt_booking AS tb')
            ->where('tb.trip_id_no', $tripIdNo)
            ->group_start()
                ->where("tb.tkt_refund_id IS NULL", null, false)
                ->or_where("tb.tkt_refund_id", 0)
                ->or_where("tb.tkt_refund_id", null)
            ->group_end()
            ->get()
            ->row();

        $bookArray = array();
        $bookArray = array_map('trim', explode(',', $bookedSeats->booked_serial));


        //-----------------booked seats-------------------
        $newSeatArray = array();
        $newSeatArray = array_map('trim', explode(',', $newSeats));

        if (sizeof($newSeatArray) > 0) {

            foreach ($newSeatArray as $seat) {

                if (!empty($seat)) {
                    if (in_array($seat, $bookArray)) {
                        return false; 
                    } else if (!in_array($seat, $seatArray)) {
                        return false; 
                    }    
                     } 

            }  
            return true;
        } else {
            return false;
        } 
    }

I have attached an image of my database..From that two tables which are trip_booking and fleet_registration.Any suggestion on this issue.

enter image description here

enter image description here

Coeng
  • 59
  • 15
  • Try: `json_encode(explode(',', $bookedSeats->booked_serial));` – Brett Gregson Sep 11 '18 at 18:31
  • I would suggest reading https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Nigel Ren Sep 11 '18 at 18:50
  • when you store the seat numbers you don't seem to be taking much care into properly formatting them. for example in `tkt_booking` they have a useless `,` at the end, and in fleet registration they have a space before the `,`. I agree with @NigelRen your db schema makes it difficult to perform simple tasks. In any case, your code seems to check to make sure the seat exists in the first table, and isn't already chosen by someone in the second table thus it is a validation function. My question is: what exactly do you want to return if you are showing us this? – Alex Sep 12 '18 at 00:32

0 Answers0