Check available vacancies - Codeigniter

Question

Hello, I have two tables : benefits_reserve (id_benefits_reserve, data_reserve, associado_reserve); calendar (id_benefits_calendar, start, color);

I’m using fullcalendar, my problem is being in foreach, because I wanted to show the amount a single time.

public function vagas_disponiveis() 
{   
    $data = array();

    $this->db->select('*');
    $this->db->from('beneficios_calendario');
    $query=$this->db->get();
    $dia = $query->result_array();

    $this->db->select('count(*) as cnt, data_reserva');
    $this->db->from('beneficios_reserva');
    $this->db->group_by('data_reserva');
    $query = $this->db->get();
    $reservas = $query->result_array();

    $vagas = '30';
    $disp = '';

    foreach ($dia as $simi) {

        foreach ($reservas as $reserva) 
        {
            if ($simi['start'] == $reserva['data_reserva']) {
                $disp = $vagas - $reserva["cnt"];
                $data[] = array(
                    'title'   => $disp,
                    'start'   => $reserva["data_reserva"],
                    'color'   => $simi["color"]
                );
            }else{
                $data[] = array(
                    'title'   => $vagas,
                    'start'   => $simi["start"],
                    'color'   => $simi["color"]
                );
            }
        }

    }

    echo json_encode($data);

}

I have 30 vacancies per day, I want him to show only one field, for example if there is no reservation stay 30, if there is reservation he shows the remaining vacancies, if someone reserved 8 vacancies he shows only 22 remaining.

EDIT

Update of the Victor model

$this->db->select('*');
    $this->db->from('beneficios_calendario');
    $query = $this->db->get();
    $dias = $query->result_array(); // Note que alterei o nome da variável

    $this->db->select('count(*) as cnt, data_reserva');
    $this->db->from('beneficios_reserva');
    $this->db->group_by('data_reserva');
    $query = $this->db->get();
    $reservas = $query->result_array();

    $data = array();
    $limite = 30; // Limite de vagas por dia

    // Criando o array com valores padronizados
    foreach ($dias as $dia) {
        $data[] = array(
            'title' => $limite,
            'start' => $dia['start'],
            'color' => $dia['color']
        );
    }

    // Alterando a quantidade de reservas de acordo com a data
    foreach ($reservas as $reserva) {
        foreach ($data as $row) {
            if ($row['start'] == $reserva['data_reserva']) {
                $row['title'] = $row['title'] - $reserva['cnt']
            }
        }
    }

    echo json_encode($data);

Changing the second foreach

// Criando o array com valores padronizados
    foreach ($dias as $dia) {
        $data[] = array(
            'title' => $limite,
            'start' => $dia['start'],
            'color' => $dia['color']
        );
    }

    // Alterando a quantidade de reservas de acordo com a data
    foreach ($reservas as $reserva) {
        foreach ($data as $row) {
            if ($row['start'] == $reserva['data_reserva']) {
                $row['title'] = $row['title'] - $reserva['cnt'];
                $data[] = array(
                    'title' =>  $row['title'],
                    'start' => $row['start'],
                    'color' => $row['color']
                );
            }
        }
    }

    echo json_encode($data);

precise format

$data[] = array(
                    'title'   => title, //30 ou XX vagas restantes
                    'start'   => data,
                    'color'   => color
                );

Answer 1

Try this:

public function vagas_disponiveis() 
{

    $this->db->select('*');
    $this->db->from('beneficios_calendario');
    $query = $this->db->get();
    $dias = $query->result_array(); // Note que alterei o nome da variável

    $this->db->select('count(*) as cnt, data_reserva');
    $this->db->from('beneficios_reserva');
    $this->db->group_by('data_reserva');
    $query = $this->db->get();
    $reservas = $query->result_array();

    $data = array();
    $limite = 30; // Limite de vagas por dia

    // Criando o array com valores padronizados
    foreach ($dias as $dia) {
        $data[] = array(
            'title' => $limite,
            'start' => $dia['start'],
            'color' => $dia['color']
        );
    }

    // Alterando a quantidade de reservas de acordo com a data
    foreach ($reservas as $reserva) {
        foreach ($data as $row) {
            if ($row['start'] == $reserva['data_reserva']) {
                $row['title'] = $row['title'] - $reserva['cnt'];
            }
        }
    }

    echo json_encode($data);
}

Answer 2

Workaround: I made a foreach counting the booking date and then I did an update in the calendar table and then I get all of the calendar table show the data.

public function vagas_disponiveis() 
{

    $this->db->select('count(*) as cnt, data_reserva');
    $this->db->from('beneficios_reserva');
    $this->db->group_by('data_reserva');
    $query = $this->db->get();
    $reservas = $query->result_array();

    if (!empty($reservas))
    {
        foreach ($reservas as $reserva) 
        {
            $title = 30 - $reserva['cnt'];
            $new = array(
                'title' => $title,
            );

            $this->db->where('start', $reserva['data_reserva']);
            $this->db->set($new);
            $atual = $this->db->update('beneficios_calendario', $new);
        }   
    }

    $this->db->select('*');
    $this->db->from('beneficios_calendario');
    $query = $this->db->get();
    $dias = $query->result_array();

    $data = array();

    foreach ($dias as $dia) {
        $data[] = array(
            'title' => $dia['title'],
            'start' => $dia['start'],
            'color' => $dia['color']
        );
    }

    echo json_encode($data);        
}