Multiple Inserts, Update, Delete using Multiple Select in Codeigniter

Multiple Inserts, Update, Delete using Multiple Select in Codeigniter

This is a tutorial on how to make multiple inserts, update, and delete using multiple selected values in Codeigniter.

If you often have difficulty making multiple updates in Codeigniter, then you are in the right place.

Today, I will share Step-by-Step how to make multiple inserts, update, and delete using multiple select in Codeigniter.

Let’s dive right in.

 

Step #1. Preparation

To make multiple inserts, update, and delete using multiple select in Codeigniter, this is what you need to prepare:

1. Codeigniter

2. JQuery

3. Bootstrap

4. Bootstrap Select

In this tutorial, I use Codeigniter v3.1.x, JQuery v3.4.x, Bootstrap v4.3.x, and Bootstrap Select v1.13.9.x.

 

Step #2. Creating a Database and Table

Create a new database with the name "pos_db". If you create a database with the same name it is better.

To create a database "pos_db" on MySQL, you can do this by executing the following query:

CREATE DATABASE pos_db;

Next to creating tables.

In this case, I use a real example in the use of multiple inserts, update, and delete using multiple select in Codeigniter.

Therefore, we need 3 tables, namely: product, package, and the detail table.

Create a "product" table by executing the following query:

CREATE TABLE product(
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(100)
)ENGINE=INNODB;

Create a "package" table by executing the following query:

CREATE TABLE package(
package_id INT PRIMARY KEY AUTO_INCREMENT,
package_name VARCHAR(100),
package_created_at DATETIME
)ENGINE=INNODB;

Create a "detail" table by executing the following query:

CREATE TABLE detail(
detail_id INT PRIMARY KEY AUTO_INCREMENT,
detail_package_id INT,
detail_product_id INT
)ENGINE=INNODB;

Next, insert some data in the "product" table as reference data.

To insert data in the "product" table can be done by executing the following query:

INSERT INTO product(product_name) VALUES
('Product 1'),('Product 2'),('Product 3'),
('Product 4'),('Product 5'),('Product 6'),
('Product 7'),('Product 8'),('Product 9');

The above query will insert data into the "product" table of 9 records.

 

Step #3. Codeigniter Installation

Extract Codeigniter that you downloaded earlier in the "C:/wamp/www" directory if you are using WAMPSERVER.

Or in the "C:/xampp/htdocs" directory if you are using XAMPP.

In this tutorial, I am using Wampserver.

Then rename the Codeigniter that has been extracted to be "pos".

Look at the following picture for more details:

Project Name

Next, make the "assets" folder in the "pos" folder parallel to the application and system folders.

Like the following picture:

After that, extract the Bootstrap file that has been downloaded previously into the "assets" folder as shown below:

Next, copy the jquery file into the "assets/js" folder as shown below:

If you haven't gotten jquery, visit the following URL:

https://code.jquery.com/jquery-3.4.1.min.js

Select all the codes (CTRL + A) and copy then paste them in notepad and save them with the name jquery-3.4.1.min.js.

Next, copy the bootstrap-select.css file into the "assets/css" folder and the bootstrap-select.js file into the "assets/js" folder as shown below:

If you haven't gotten bootstrap-select, please download the following URL:

https://developer.snapappointments.com/bootstrap-select/

 

Step #4. Codeigniter Configuration

Next do the configuration in the following files:

1.  Autoload.php

Open the autoload.php file found in the "application/config" folder and find the following code:

$autoload['libraries'] = array();
$autoload['helper'] = array();

Then set it to be as follows:

$autoload['libraries'] = array('database');
$autoload['helper'] = array('url');

2.  Config.php

Open the config.php file in the "application/config" folder and find the following code:

$config['base_url'] = '';

Then set it to be as follows:

$config['base_url'] = 'http://localhost/pos/';

3.  Database.php

Open the database.php file in the "application/config" folder and find the following code:

$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost',
	'username' => '',
	'password' => '',
	'database' => '',
	'dbdriver' => 'mysqli',
	'dbprefix' => '',
	'pconnect' => FALSE,
	'db_debug' => (ENVIRONMENT !== 'production'),
	'cache_on' => FALSE,
	'cachedir' => '',
	'char_set' => 'utf8',
	'dbcollat' => 'utf8_general_ci',
	'swap_pre' => '',
	'encrypt' => FALSE,
	'compress' => FALSE,
	'stricton' => FALSE,
	'failover' => array(),
	'save_queries' => TRUE
);

Then set it to be as follows:

$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost',
	'username' => 'root',
	'password' => '',
	'database' => 'pos_db',
	'dbdriver' => 'mysqli',
	'dbprefix' => '',
	'pconnect' => FALSE,
	'db_debug' => (ENVIRONMENT !== 'production'),
	'cache_on' => FALSE,
	'cachedir' => '',
	'char_set' => 'utf8',
	'dbcollat' => 'utf8_general_ci',
	'swap_pre' => '',
	'encrypt' => FALSE,
	'compress' => FALSE,
	'stricton' => FALSE,
	'failover' => array(),
	'save_queries' => TRUE
);

 

Step #5. Controller

Create a controller named Package.php in the “application/controllers” folder.

Then type the following code:

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Package extends CI_Controller{
	
	function __construct(){
		parent::__construct();
		$this->load->model('Package_model','package_model');
	}

	// READ
	function index(){
		$data['product'] = $this->package_model->get_products();
		$data['package'] = $this->package_model->get_packages();
		$this->load->view('package_view',$data);
	}

	//CREATE
	function create(){
		$package = $this->input->post('package',TRUE);
		$product = $this->input->post('product',TRUE);
		$this->package_model->create_package($package,$product);
		redirect('package');
	}

	// GET DATA PRODUCT BY PACKAGE ID
	function get_product_by_package(){
		$package_id=$this->input->post('package_id');
    	$data=$this->package_model->get_product_by_package($package_id)->result();
    	foreach ($data as $result) {
    		$value[] = (float) $result->product_id;
    	}
    	echo json_encode($value);
	}

	//UPDATE
	function update(){
		$id = $this->input->post('edit_id',TRUE);
		$package = $this->input->post('package_edit',TRUE);
		$product = $this->input->post('product_edit',TRUE);
		$this->package_model->update_package($id,$package,$product);
		redirect('package');
	}

	// DELETE
	function delete(){
		$id = $this->input->post('delete_id',TRUE);
		$this->package_model->delete_package($id);
		redirect('package');
	}
}

 

Step #6. Model

Create a model named “Package_model.php” in the “application/models” folder.

Then type the following code:

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Package_model extends CI_Model{
	
	// GET ALL PRODUCT
	function get_products(){
		$query = $this->db->get('product');
		return $query;
	}

	//GET PRODUCT BY PACKAGE ID
	function get_product_by_package($package_id){
		$this->db->select('*');
		$this->db->from('product');
		$this->db->join('detail', 'detail_product_id=product_id');
		$this->db->join('package', 'package_id=detail_package_id');
		$this->db->where('package_id',$package_id);
		$query = $this->db->get();
		return $query;
	}

	//READ
	function get_packages(){
		$this->db->select('package.*,COUNT(product_id) AS item_product');
		$this->db->from('package');
		$this->db->join('detail', 'package_id=detail_package_id');
		$this->db->join('product', 'detail_product_id=product_id');
		$this->db->group_by('package_id');
		$query = $this->db->get();
		return $query;
	}

	// CREATE
	function create_package($package,$product){
		$this->db->trans_start();
			//INSERT TO PACKAGE
			date_default_timezone_set("Asia/Bangkok");
			$data  = array(
				'package_name' => $package,
				'package_created_at' => date('Y-m-d H:i:s') 
			);
			$this->db->insert('package', $data);
			//GET ID PACKAGE
			$package_id = $this->db->insert_id();
			$result = array();
			    foreach($product AS $key => $val){
				     $result[] = array(
				      'detail_package_id'  	=> $package_id,
				      'detail_product_id'  	=> $_POST['product'][$key]
				     );
			    }      
			//MULTIPLE INSERT TO DETAIL TABLE
			$this->db->insert_batch('detail', $result);
		$this->db->trans_complete();
	}

	
	// UPDATE
	function update_package($id,$package,$product){
		$this->db->trans_start();
			//UPDATE TO PACKAGE
			$data  = array(
				'package_name' => $package
			);
			$this->db->where('package_id',$id);
			$this->db->update('package', $data);
			
			//DELETE DETAIL PACKAGE
			$this->db->delete('detail', array('detail_package_id' => $id));

			$result = array();
			    foreach($product AS $key => $val){
				     $result[] = array(
				      'detail_package_id'  	=> $id,
				      'detail_product_id'  	=> $_POST['product_edit'][$key]
				     );
			    }      
			//MULTIPLE INSERT TO DETAIL TABLE
			$this->db->insert_batch('detail', $result);
		$this->db->trans_complete();
	}

	// DELETE
	function delete_package($id){
		$this->db->trans_start();
			$this->db->delete('detail', array('detail_package_id' => $id));
			$this->db->delete('package', array('package_id' => $id));
		$this->db->trans_complete();
	}
	
}

 

Step #7. View

Create a view named “package_view.php” in the “application/views” folder.

Then type the following code:

<!DOCTYPE html>
<html>
<head>
	<title>Package</title>
	<!--Load CSS File-->
	<link rel="stylesheet" type="text/css" href="<?php echo base_url('assets/css/bootstrap.css');?>">
	<link rel="stylesheet" type="text/css" href="<?php echo base_url('assets/css/bootstrap-select.css');?>">
</head>
<body>
	<div class="container">
		<h1>Package Lists</h1>
		<button type="button" class="btn btn-success btn-sm" data-toggle="modal" data-target="#addNewModal">Add New Package</button><br/>
		<table class="table table-striped">
			<thead>
				<tr>
					<th>#</th>
					<th>Package Name</th>
					<th>Created At</th>
					<th>Item Product</th>
					<th>Action</th>
				</tr>
			</thead>
			<tbody>
				<?php 
					$count=0;
					foreach ($package->result() as $row) :
						$count++;
				?>
				<tr>
					<td><?php echo $count;?></td>
					<td><?php echo $row->package_name;?></td>
					<td><?php echo $row->package_created_at;?></td>
					<td><?php echo $row->item_product.' Items';?></td>
					<td>
						<a href="#" class="btn btn-info btn-sm update-record" data-package_id="<?php echo $row->package_id;?>" data-package_name="<?php echo $row->package_name;?>">Edit</a>
						<a href="#" class="btn btn-danger btn-sm delete-record" data-package_id="<?php echo $row->package_id;?>">Delete</a>
					</td>
				</tr>
				<?php endforeach;?>
			</tbody>
			
		</table>
	</div>

	<!-- Add New Package Modal -->
	<form action="<?php echo site_url('package/create');?>" method="post">
		<div class="modal fade" id="addNewModal" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
		  <div class="modal-dialog" role="document">
		    <div class="modal-content">
		      <div class="modal-header">
		        <h5 class="modal-title" id="exampleModalLabel">Add New Package</h5>
		        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
		          <span aria-hidden="true">&times;</span>
		        </button>
		      </div>
		      <div class="modal-body">

				<div class="form-group row">
				    <label class="col-sm-2 col-form-label">Package</label>
				    <div class="col-sm-10">
				      <input type="text" name="package" class="form-control" placeholder="Package Name" required>
				    </div>
				</div>
				<div class="form-group row">
				    <label class="col-sm-2 col-form-label">Product</label>
				    <div class="col-sm-10">
				      	<select class="bootstrap-select" name="product[]" data-width="100%" data-live-search="true" multiple required>
				      		<?php foreach ($product->result() as $row) :?>
						  		<option value="<?php echo $row->product_id;?>"><?php echo $row->product_name;?></option>
						  	<?php endforeach;?>
						</select>
				    </div>
				</div>

		      </div>
		      <div class="modal-footer">
		        <button type="button" class="btn btn-secondary btn-sm" data-dismiss="modal">Close</button>
		        <button type="submit" class="btn btn-success btn-sm">Save</button>
		      </div>
		    </div>
		  </div>
		</div>
	</form>

	<!-- Update Package Modal-->
	<form action="<?php echo site_url('package/update');?>" method="post">
		<div class="modal fade" id="UpdateModal" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
		  <div class="modal-dialog" role="document">
		    <div class="modal-content">
		      <div class="modal-header">
		        <h5 class="modal-title" id="exampleModalLabel">Update Package</h5>
		        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
		          <span aria-hidden="true">&times;</span>
		        </button>
		      </div>
		      <div class="modal-body">

				<div class="form-group row">
				    <label class="col-sm-2 col-form-label">Package</label>
				    <div class="col-sm-10">
				      <input type="text" name="package_edit" class="form-control" placeholder="Package Name" required>
				    </div>
				</div>
				<div class="form-group row">
				    <label class="col-sm-2 col-form-label">Product</label>
				    <div class="col-sm-10">
				      	<select class="bootstrap-select strings" name="product_edit[]" data-width="100%" data-live-search="true" multiple required>
				      		<?php foreach ($product->result() as $row) :?>
						  		<option value="<?php echo $row->product_id;?>"><?php echo $row->product_name;?></option>
						  	<?php endforeach;?>
						</select>
				    </div>
				</div>

		      </div>
		      <div class="modal-footer">
		      	<input type="hidden" name="edit_id" required>
		        <button type="button" class="btn btn-secondary btn-sm" data-dismiss="modal">Close</button>
		        <button type="submit" class="btn btn-success btn-sm">Update</button>
		      </div>
		    </div>
		  </div>
		</div>
	</form>


	<!-- Delete Package Modal -->
	<form action="<?php echo site_url('package/delete');?>" method="post">
		<div class="modal fade" id="DeleteModal" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
		  <div class="modal-dialog" role="document">
		    <div class="modal-content">
		      <div class="modal-header">
		        <h5 class="modal-title" id="exampleModalLabel">Delete Package</h5>
		        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
		          <span aria-hidden="true">&times;</span>
		        </button>
		      </div>
		      <div class="modal-body">

				<h4>Are you sure to delete this package?</h4>

		      </div>
		      <div class="modal-footer">
		      	<input type="hidden" name="delete_id" required>
		        <button type="button" class="btn btn-secondary btn-sm" data-dismiss="modal">No</button>
		        <button type="submit" class="btn btn-success btn-sm">Yes</button>
		      </div>
		    </div>
		  </div>
		</div>
	</form>

	<!--Load JavaScript File-->
	<script type="text/javascript" src="<?php echo base_url('assets/js/jquery-3.4.1.min.js');?>"></script>
	<script type="text/javascript" src="<?php echo base_url('assets/js/bootstrap.bundle.js');?>"></script>
	<script type="text/javascript" src="<?php echo base_url('assets/js/bootstrap-select.js');?>"></script>
	<script type="text/javascript">
		$(document).ready(function(){
			$('.bootstrap-select').selectpicker();

			//GET UPDATE
			$('.update-record').on('click',function(){
				var package_id = $(this).data('package_id');
				var package_name = $(this).data('package_name');
				$(".strings").val('');
				$('#UpdateModal').modal('show');
				$('[name="edit_id"]').val(package_id);
				$('[name="package_edit"]').val(package_name);
                //AJAX REQUEST TO GET SELECTED PRODUCT
                $.ajax({
                    url: "<?php echo site_url('package/get_product_by_package');?>",
                    method: "POST",
                    data :{package_id:package_id},
                    cache:false,
                    success : function(data){
                        var item=data;
                        var val1=item.replace("[","");
                        var val2=val1.replace("]","");
                        var values=val2;
                        $.each(values.split(","), function(i,e){
                            $(".strings option[value='" + e + "']").prop("selected", true).trigger('change');
                            $(".strings").selectpicker('refresh');

                        });
                    }
                    
                });
                return false;
			});

			//GET CONFIRM DELETE
			$('.delete-record').on('click',function(){
				var package_id = $(this).data('package_id');
				$('#DeleteModal').modal('show');
				$('[name="delete_id"]').val(package_id);
			});

		});
	</script>
</body>
</html>

 

Step #8. Testing

To do the test, open your browser and visit the following URL:

http://localhost/pos/index.php/package

Then, it will appear as shown below:

#1. Add New (CREATE)

Click the "Add New Package" button to create a new package. Then the modal will appear as shown below:

Input "package" and select the "product" you want to add to the package.

In the picture above, I input the package name "Package I" and choose Product 1, Product 3, Product 4, and Product 5 into "Package I".

Then click the "Save” button to insert data.

If successful, the results will look like the following picture:

When inserting data, one record will be inserted in the "package" table, That is Package Name, and Created At.

And included multiple records in the "detail" table according to the number of products selected in one package.

In other words, a package has many product items in it.

 

#2. Update

Click the "Edit" button to update the record, then the modal "Update Package" will appear as shown below:

Change the "Package" and add or reduce the number of product items as desired, then click the "Update" button to update the data.

In the case above, I changed the package name to "Package I Edit" and I reduced the product items to 2 items namely Product 3 and Product 4.

If the update is successful, it will appear as shown below:

At the time of the update process, one record will be updated in the "package" table and updated multiple records in the "detail" table.

 

#3. Delete

Click the "Delete" button to delete the data, then the modal “Delete Package” will appear as shown below:

Click the "Yes" button to delete the data. If successful, the package data will be erased.

When deleted, it will delete multiple records in the "detail" table based on "package_id" and delete one record in the "package" table based on "package_id".

 

Conclusion

The discussion this time is about how to make multiple Insert, Update, and delete using multiple selected values in Codeigniter.

With a real case, a study example can make you understand well what kind of making multiple inserts, update, and delete and how it is applied.

So, what are you waiting for Let’s Coding!

Download Source Code

 

Share:



Sponsorship:


Recommended for you


Comments (0)

Leave a Comment