CodeIgniter Save Multiple Value to Database

Now I will try to share how to save multiple data value to database (example I use PostgreSQL). I’ve previously explained how to create input type with add field using jquery in https://imron02.wordpress.com/2013/08/16/jquery-button-add-field-from-input.

  1. The first we must create database:
    aim@crunchbang:~$ psql -h localhost postgres postgres
    
  2. And then we must create database (name databae is learn) with privileges to another user, example user imron;
    postgres=# CREATE DATABASE learn OWNER imron;
    
  3. And the next step, we must create example table:
    aim@crunchbang:~$ psql -h localhost -U imron learn;
    

    Then:

    learn=> CREATE TABLE value(
    learn(> one VARCHAR(50),
    learn(> two VARCHAR(50));
    CREATE TABLE
    learn=> 
    
  4. JQuery File. Download from here http://jquery.com/download/. Example I use JQuery version 2.0.3.
    And then save to folder assets/js (previously make a folder js and assets) like this picture:
    assets


For coding v_add.php for views:

<html>
<head>
    <title>Test</title>
    <script type="text/javascript" src="<?=base_url("assets/js/jquery-2.0.3.min.js");?>"></script>
    <script type="text/javascript">
        function addField() {
            $($('#template').html()).appendTo('#field_grid tbody');
        }

        function removeField(e) {
            if($('input[type=text]').parents('tbody').find('tr').length <= 2) {
                return false;
            }
            var row = $(e).closest('tr').remove();
        }

        $(function() {
            $('#field_grid').on('click', '.btn-remove', function(e) {
                removeField(e.target);
                return e.preventDefault();
            });

            $('#add_field').click(function(e) {
                addField();
                return e.preventDefault();
            });

            $('#field_grid').on('click', '.btn-up', function(e) {
                var current = $(this).parents('tr');
                
                if(current.prevAll().length == 1) {
                    return e.preventDefault();
                }
                current.prev().before(current);
                return e.preventDefault();
            });

            $('#field_grid').on('click', '.btn-down', function(e) {
                var current = $(this).parents('tr');
                current.next().after(current);
                return e.preventDefault();
            });
        });
    </script>
</head>
<body>
<fieldset>
    <legend>Test</legend>
    <div id="field">
        <table id="field_grid">
            <tr>
                <th>One</th>
                <th>Two</th>
                <th>Three</th>
            </tr>
            <tr>
                <td>
                    <input type="text" value=""  name="one[]" placeholder="One" />
                </td>
                <td>
                    <input type="text" value=""  name="two[]" placeholder="Two" />
                </td>
                <td>
                    <a href="#" class="btn-remove">Delete</a>
                    <a href="#" class="btn-up">Up</a>
                    <a href="#" class="btn-down">Down</a>
                </td>
            </tr>
        </table>
    </div>
    <div class="action-buttons btn-group">
        <input type="submit" class="btn btn-primary" />
        <button id="add_field">Add Field</button>
    </div>
</fieldset>
<script type="text/template" id="template">
     <tr>
        <td>
            <input type="text" value=""  name="one[]" placeholder="One" />
        </td>
        <td>
            <input type="text" value=""  name="two[]" placeholder="Two" />
        </td>
        <td>
            <a href="#" class="btn-remove">Delete</a>
            <a href="#" class="btn-up">Up</a>
            <a href="#" class="btn-down">Down</a>
        </td>
    </tr>
</script>
</body>
</html>

See the highlight code. Why I use name with []?
I Use name with tag [] because if the data is more then one value, we must save data value to array type. So if we click submit button, we can get such as:

Array
(
    [one] => Array
        (
            [0] => dfd
            [1] => adaf
        )

    [two] => Array
        (
            [0] => fdfa
            [1] => dfadf
        )

)

code c_add.php for Controllers

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

class C_add extends CI_Controller {
    public function index() {
        $this->load->helper('url');
    	if($_POST) {
    		$this->load->model('m_database');

    		$data = array(
    			'one' => $_POST['one'],
    			'two' => $_POST['two']
    			);
    		$this->m_database->add($data);
    		redirect('/c_add/', 'refresh');
    	}
        $this->load->view('v_add');
    }
}

/* End of file c_add.php */
/* Location: ./application/controllers/c_add.php */

code m_database.php for Models

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

class m_database extends CI_Model {
    public function add($data) {
    	$this->load->database();
    	$cdata = count($data['one']);
        
        //get count values
        $rValue = ($cdata  ? $cdata - 1 : 0);
        $cValue = "(?,?)".str_repeat(",(?,?)", $rValue);
        
        $sql = "INSERT INTO value (one, two) VALUES $cValue";
        $q = $this->db->conn_id->prepare($sql);
        //function binding
        $a = 1; $b = 2;
        for($i=0; $i < $cdata; $i++) {
            $q->bindValue($a, $data['one'][$i], PDO::PARAM_STR);
            $q->bindValue($b, $data['two'][$i], PDO::PARAM_STR);
            $a+=2; $b+=2;
        }
        $q->execute();
    }
}

/* End of file m_database.php */
/* Location: ./application/models/m_database */

Example result:

And now if we click submit and check database using SQL, we will get that data has been entered into the database.

learn=> SELECT * FROM value;
  one  |   two    
-------+----------
 Imron | Rosdiana
 Asih  | Restari
 Test  | Test
(3 rows)

learn=> 

3 comments

      1. I am new for codeigniter. I am sorry to say that, this tutorial is not working properly. Data are not post to database. Maybe I messed something. If is it possible please help me. Actually I really need this kind of tutorial.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s