Codeigniter - 将数据插入MySQL，其中只有一个Input值是数组

Question

I am learning Codeigniter and hitting some turbulence. One of my input fields collects an array of values (from check-boxes), while the rest are single values. I would like to insert the data into MySQLi in such a way that the number of inserted rows is equal to the number of values in the array. It looks like:

My Controller:

 function request_rooms() {     
      if ($_POST) {
        //room is an array or one or more values
        $this->form_validation->set_rules('room[]', 'Select Box', 'trim|required|min_length[1]|max_length[25]'); 
        $this->form_validation->set_rules('guest_id', 'Guest ID', 'trim|required|xss_clean|min_length[4]|max_length[20]');
         $this->form_validation->set_rules('date_in', 'Check-in Date', 'trim|required|exact_length[10]'); 
        $this->form_validation->set_rules('date_out', 'Check-out Date', 'trim|required|exact_length[10]'); 

        if ($this->form_validation->run($this) == FALSE) {
            $somedata = array('errors' => validation_errors());
            $this->session->set_flashdata($somedata);
            require("includes/load_view_reservation.php");
          } else {
               //Prepare data to send to Reservation model
               $data = array( 
               'room_no'  = ?
               'guest_id' = $this->input->post('guest_id'),
               'check_in' = $this->input->post('check_in'),
               'check_in' = $this->input->post('check_in')

             $this->reservation_model->insert_reservation($data);
              }       
       }
}

The Problem: My problem is how to handle the room array. I want to create a $data array for the data to be inserted into the Database. So suppose the room array input ($this->input->post('room[]')) is as follows:

$room = array(42, 123, 16);

I would like the $data array to be like:

 $data = array
  (
  array(
  "room_no" => "42",
  "guest_id" => "2",
  "date_in" => "2018-05-20",
  "date_out" => "2018-05-27"
  ),
  array(
  "room_no" => "123",
  "guest_id" => "2",
  "date_in" => "2018-05-20",
  "date_out" => "2018-05-27" ),
  array(
  "room_no" => "16",
  "guest_id" => "2",
  "date_in" => "2018-05-20",
  "date_out" => "2018-05-27" )
  );

I have a faint idea that a loop could accomplish something like this so that the room_no part of the array has each of the values from the array while the other values remain the same, but I can't see how to proceed.

Question:

How do I create a Multidimensional array like above in Codeigniter? Secondly, This far, I have only inserted (into MySQL) a simple one-dimensional array using a Codeigniter Model. How do I insert a Multidimensional array like the one above? Please help.

Answer 1

Hope this will help you :

First method insert individual record;

//$rooms = $this->input->post('room');
$rooms = array(42, 123, 16);
if ( ! empty($rooms))
{
  foreach($rooms AS $room)
  {
    $data['room_no'] = $room;
    $data['guest_id'] = $this->input->post('guest_id');
    $data['check_in'] = $this->input->post('check_in');

    $this->reservation_model->insert_reservation($data);
  }
}

Your model method insert_reservation should be like this :

public function insert_reservation($data)
{
   return $this->db->insert('table_name',$data);
}

Second method insert multiple (batch) record;

//$rooms = $this->input->post('room');
$rooms = array(42, 123, 16);
if( ! empty($rooms))
{
   foreach($rooms AS $room)
   {
      $data[] = [
            'room_no' => $room,
            'guest_id' => $this->input->post('guest_id'),
            'check_in' => $this->input->post('check_in'),
        ];
   }
}
//print_r($data);
$this->reservation_model->insert_reservation($data);

In this case your model method insert_reservation should be like this :

public function insert_reservation($data)
{
   return $this->db->insert_batch('table_name',$data);
}

For more : https://www.codeigniter.com/user_guide/database/query_builder.html#inserting-data