How to Insert Multiple Rows from Array Using Codeigniter Framework

How to insert multiple rows from array using CodeIgniter framework?

Assembling one INSERT statement with multiple rows is much faster in MySQL than one INSERT statement per row.

That said, it sounds like you might be running into string-handling problems in PHP, which is really an algorithm problem, not a language one. Basically, when working with large strings, you want to minimize unnecessary copying. Primarily, this means you want to avoid concatenation. The fastest and most memory efficient way to build a large string, such as for inserting hundreds of rows at one, is to take advantage of the implode() function and array assignment.

$sql = array(); 
foreach( $data as $row ) {
    $sql[] = '("'.mysql_real_escape_string($row['text']).'", '.$row['category_id'].')';
}
mysql_query('INSERT INTO table (text, category) VALUES '.implode(',', $sql));

The advantage of this approach is that you don't copy and re-copy the SQL statement you've so far assembled with each concatenation; instead, PHP does this once in the implode() statement. This is a big win.

If you have lots of columns to put together, and one or more are very long, you could also build an inner loop to do the same thing and use implode() to assign the values clause to the outer array.

How to insert multiple array into one row database on codeigniter?

After so many trial and error and searching many resource. Finally i came to my own solution. Hope i can help others with my code. Please feel free to add another solution for this problem...

View Page for Input Data (form-input.php)

<?php echo form_open('#', array('class'=>'form') ); ?>

   <?php 
       $i = 0;
       if ($valuation != NULL):
   ?>

       // Form field will looping through this as many as foreach result
       <?php foreach ($valuation as $value): ?>

           <input type="text" name="variable[<?php echo $x; >?]" value="<?php echo $value->category; ?>" readonly="readonly">
           <select name="score[<?php echo $x; ?>]">
               <option value=""> — Choose Score — </option>
               <option value="0"> 0 </option>
               <option value="1"> 1 </option>
               <option value="2"> 2 </option>
               <option value="3"> 3 </option>
           </select>
           <textarea name="desc[<?php echo $x; ?>]" rows="5" required="required"></textarea>

       <?php
           $x++;
           endforeach;
       ?>

   <?php
        else:
            echo 'No input field generate';
        endif;
   ?>

<?php
    echo form_button(array('name'=>'submit', 'type'=>'submit', 'content'=>'Save') );
    echo form_close();
?>

The Controller (Collection.php)

public function save()
{
   // get collection ID from URL
   $collectionID = $this->input->get('coll_id');

   //Form Validation
   $this->form_validation->set_rules('variable[]', 'Variable', 'required', array('required'=>'%s cannot empty'));
   $this->form_validation->set_rules('score[]', 'Score', 'required', array('required'=>'%s cannot empty'));
   $this->form_validation->set_rules('desc[]', 'Description', 'required', array('required'=>'%s cannot empty'));

   if ($this->form_validation->run() == FALSE)
   {
       $data = array(
           'page_title' => 'Add Significance Data for ' . $collectionID,
           'valuation' => $this->significance_db->get_value_list()
       );
       $this->load->view('form-input', $data);
   }
   else
   {
       $count = count($this->input->post('variable'));
       for ($i=0; $i<$count; $i++)
       {
           $json_data[] = array(
               'variable' => $this->input->post('variable')[$i],
               'score' => $this->input->post('score')[$i],
               'description' => $this->input->post('desc')[$i]
           );
       }
       $json_encode_data = json_encode($json_data);

       $db_data = array(
           'collection_ID' => $collectionID,
           'valuation' => $json_encode_data,
           'post_by' => $this->session->userdata('user_ID'),
           'post_datetime' => get_datetime_format()
       );

       if ($this->significance_db->save_data($db_data) == TRUE)
       {
          'some function here if TRUE';
       }
       else
       {
          'some function here if FALSE';
       }
   }
}

Model (significance_db.php)

function save_data($db_data)
{
     $this->db->insert('table_name', $db_data);
     if ($this->db->affected_rows() > 0)
     {
         return TRUE;
     }
     else
     {
         return FALSE;
     }
}

So that is how to inserting data to my database table. It will create one row with the array content (generate with json_encode) in the valuation column.

Another code below is how to view this data. I will show the example of my Controller, Model, and View.

Controller (inside the Collection.php too)

public function show()
{
    //get collection ID from URL
    $collectionID = $this->input->get('coll_id');

    $data = array(
        'page_title' => 'Show Significance Data from ' . $collectionID,
        'significance' => $this->significance_db->get_significance_data($collectionID)
    );
    $this->load->view('view-page', $data);
}

View Page for Diplaying Data (view-page.php)

<?php if ($significance != NULL: ?>

    <div class="row">
        <div class="col-sm-12">
            <div class="card">
                <div class="card-body">

                    <?php foreach ($significance as $value): ?>

                        <?php
                            $data = json_decode($value->valuation); 
                            $count = count($data);
                        ?>

                        <?php for ($i=0; $i<$count; $i++): ?>

                            <ul class="list-inline">
                                <li class="list-inline-item"><?php echo $data[$i]->variable; ?></li>
                                <li class="list-inline-item"><?php echo $data[$i]->score; ?></li>
                                <li class="list-inline-item"><?php echo $data[$i]->description; ?></li>
                            </ul>

                        <?php endfor; ?>

                    <?php endforeach; ?>

                </div>
            </div>
        </div>
    </div>

<?php else: ?>

    <strong> No Data Result. </strong>

<?php endif; ?>

If you want to know the data from json_decode($value->valuation) you can check with print_r($data) or whatever the script you prefer to showing array.

Finally. Hope this can help others. If anyone have another method which create same function/method like this, please feel free to comment or add another method.

How to insert multiple rows in mysql database with codeigniter 4 framework?

Your $data variable is not correctly set for an insert batch. Each row of the array has to correspond to one new object you're wanting to insert.

This should work if your keys are the ones by default and that every row of $tmp_data has the same length.

$data = [];
$tmp_data = [
    'customerId' => $_POST['customerId'],
    'transactionType' => $_POST['transactionType'],
    'productName' => $_POST['productName'],
    'quantity' => $_POST['quantity'],
    'unit' => $_POST['unit'],
    'price' => $_POST['price'],
    'payment' => $_POST['payment'],
    'sellDate' => $_POST['sellDate']
];
foreach ($tmp_data as $k => $v) {
    for($i = 0; $i < count($v);$i++) {
        $data[$i][$k] = $v[$i];
    }
}

And be sure to take a look at the documentation : https://codeigniter.com/user_guide/database/query_builder.html?highlight=insertbatch

Inserting Multiple rows in mysql using codeigniter : it inserts only one row and in that one row stores only one first character

try below array and remove $cp_data[] = array(....) from insCompanies function.

$cp_data = array(
                'jobmelaid' => $jobmelaid,
                'company' => $jcdata['company'][$i],
                'jobtitle' => $jcdata['jobtitle'][$i],
                'qualification' => $jcdata['qualification'][$i],
                'specialization' => $jcdata['specialization'][$i],
                'jobtype' => $jcdata['jobtype'][$i],
                'sector' => $jcdata['sector'][$i]
                );

---

Related Topics