Showing posts with label Mysql. Show all posts
Showing posts with label Mysql. Show all posts

Friday 31 January 2020

Form with multiple steps php

1. If you want to create a multiple step form you can follow below step , i am using codeigniter as my framwork php , index function is a single function which will handle your request and process your form , $form_process_id,$form_id,$step_id_u,$is_previous_action is the main variable other variable are my custom variable which i am passing , you can assign your variable as per your requirement.

2. fpi short means form process id, multiple if else in index function is for like when you in step1 include only step1 data , if you in step 2 include only step2 data by fetching records from mysql.

3. if you see the screenshot there is a table which contain post_values,post_values_front fields in mysql , post_values fields store form data as serialize at user end and post_values_front store the html which you can be save from your admin panel.

4. $data['main'] in index function just get the in which step you are and fetch accordingly. you have to create a directory stepforms , where step1.php , step2.php.... etc file will be created as per multi form you want to create , Please see below what html they will containstep1.php ...etc. i have included my custom variable also for query to database you can remove those. I commented the $save_step_data in else statement because for front end user update query always fire not new record will be added because from your admin panel step data , html already saved, for admin panel you have to enable those line , because admin side iif fpi found then update if not then insert/save will goes accordinlgy. you can also include js/jquery in step1.php or step2.php etc. you can use below function for any kind of multiple user registration or where you need any kind of multiple form submission.

class MultiStep extends MY_Controller {
    public function index() {
        $search_gstin_id = (int) isset($_GET['sid']) ? trim($this->input->get('sid', TRUE)) 
: trim($this->input->post('sid', TRUE));
      $financial_year = isset($_GET['fy']) ? $this->input->get('fy', TRUE) : $this->input->post('fy', TRUE);
      $form_process_id = (int) $this->input->get('fpi');
      $is_previous_action = $this->input->get('p');
      $form_id = $this->uri->segment(3, 0);
      $step_id_u = $this->uri->segment(4, 1);
      $step_id_p = isset($_POST['step_id']) ? (int) $step_id_u : $step_id_u;
      $this->load->model('AuditCustomForms');
      $total_steps = $this->AuditCustomForms->get_total_form($form_id);
      $form_name_info = $this->AuditCustomForms->get_form_name($form_id);
      if ($step_id_p != '') {
          if ($is_previous_action != '') {
              $field_id = (int) $step_id_p;
          } else {
              $field_id = (int) $step_id_p;
          }
      } else {
          $field_id = $step_id_u;
      }
      $form_info = $this->AuditCustomForms->get_step_from($form_id, $field_id);
      $form_data = $this->AuditCustomForms->get_from_data($form_process_id, $field_id);
      $save_id = '';
      if (!empty($_POST) && is_array($_POST)) {
          $sdata = $this->input->post();
          unset($sdata['step_id']);
          unset($sdata['form_id']);
          if ($this->input->get('isf') == 'yes') {
              if (!empty($form_data)) {
                $save_step_data = array('post_values_front' => serialize($sdata), 'status' => 1,
 'search_gstin_id' => $search_gstin_id, 'gst_financial_year' => $_POST['fy']);
                $save_id = $this->AuditCustomForms->do_update_records('audit_post_values',
 $save_step_data, array('form_process_id' => $form_process_id, 'field_id' => $step_id_u));
              } else {
               //$save_step_data = array('status' => 1, 'form_process_id' => $form_process_id
, 'user_id' => $this->user_id, 'form_id' => $form_id, 'field_id' => $step_id_u,
 'post_values_front' => serialize($sdata), 
'created_date' => $this->today, 'search_gstin_id' => $_POST['sid'], 'gst_financial_year' => $_POST['fy']);
                //$save_id = $this->AuditCustomForms->do_save_records('audit_post_values', $save_step_data);
              }
            $_SESSION['markfront'] = alertmessage($message_type = 'alert-success', 
$message = 'TaxAudit have been saved successfully.');
              $this->session->mark_as_flash('markfront');
              redirect(base_url() . '/dashboard/');
              exit;
           } else {
           if (!empty($form_data)) {
              $save_step_data = array('status' => 1, 'post_values_front' => serialize($sdata),
 'search_gstin_id' => $search_gstin_id, 'gst_financial_year' => $_POST['fy']);
             $save_id = $this->AuditCustomForms->do_update_records('audit_post_values'
, $save_step_data, array('form_process_id' => $form_process_id, 'field_id' => $step_id_u - 1));
           } else {
            //$save_step_data = array('status' => 1, 
'form_process_id' => $form_process_id, 'user_id' => $this->user_id, 'form_id' => $form_id,
'field_id' => $step_id_u, 'post_values_front' => serialize($sdata),
'created_date' => $this->today, 'search_gstin_id' => $_POST['sid'], 'gst_financial_year' => $_POST['fy']);
            //$save_id = $this->AuditCustomForms->do_save_records('audit_post_values', $save_step_data);
           }
          }
        }
        if ($step_id_u == 2) {
        } else if ($step_id_u == 3) {
        } else if ($step_id_u == 4) {
        } else if ($step_id_u == 5) {
        } else if ($step_id_u == 6) {
        } else if ($step_id_u == 7) {
        } else if ($step_id_u == 8) {
        } else if ($step_id_u == 9) {
        } else if ($step_id_u == 10) {
        } else if ($step_id_u == 11) {
        } else if ($step_id_u == 12) {
        } else {
        }
        $data['search_gstin_id'] = $search_gstin_id;
        $data['financial_year'] = $financial_year;
        $data['form_process_id'] = $form_process_id;
        $data['form_info'] = $form_info;
        $data['form_data'] = $form_data;
        $data['form_id'] = $form_id;
        $data['next_step'] = count($form_info) > 0 ? $form_info[0]->field_id : '';
        $data['actual_step_id_p'] = $step_id_p;
        $data['post_id'] = $save_id;
        $data['form_name_info'] = $form_name_info;
        $data['header'] = 'dashboard/header';
        $data['main'] = 'stepforms/' . $form_id . '/' . $form_info[0]->step;
        $data['footer'] = 'front/footer';
        $this->load->vars($data);
        $this->load->view($this->user_dashboard);
    }
}

5. Below is the model which contain function for saving and updating form data ,generateFormToken function will generate a unique fpi for your form. model is just fetching records from mysql database.

class AuditCustomForms extends CI_Model {
    function __construct() {
        parent::__construct();
        $this->load->database();
    }
    function generateFormToken() {
        $query = $this->db->query('SELECT LPAD(FLOOR(RAND()*99999), 5, 0) AS random_num FROM 
        audit_post_values WHERE "random_num" NOT IN (SELECT post_id FROM audit_post_values) LIMIT 1');
        $row = $query->row();
        if (isset($row)) {
            return $row->random_num;
        } else {
            $query = $this->db->query('SELECT LPAD(FLOOR(RAND()*99999), 5, 0) AS random_num');
            $row = $query->row();
            return $row->random_num;
        }
    }
    function get_step_from($form_id, $field_id) {
        $this->db->select('ff.*');
        $this->db->from('audit_form_fields ff');
        $this->db->where('ff.form_id', $form_id);
        $this->db->where('ff.field_id', $field_id);
        $query = $this->db->get();
        return $query->result();
    }
    function do_update_records($table, $data, $where) {
        $this->db->where($where);
        if ($this->db->update($table, $data)) {
            $query_id = $this->db->select('post_id')->where($where)->get($table);
            return $query_id;
        } else {
            return NULL;
        }
    }
    function do_save_records($table, $data) {
        if ($this->db->insert($table, $data)) {
            return $this->db->insert_id();
        } else {
            return NULL;
        }
    }
    function get_from_data($form_process_id, $field_id) {
        $this->db->select('*');
        $this->db->from('audit_post_values pv');
        $this->db->where('pv.form_process_id', $form_process_id);
        $this->db->where('pv.field_id', $field_id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return NULL;
        }
    }
}

6. Below is the first step form , either you can include html directly to below view file or if you want to fetch html from database for example you have a admin panel where you manage content , you can also make or use same function for your admin panel which store only html in post_values fields as serialised and them you can use it like below or replace "//your html element , form will go here step 1" by below line , so what ever html you saved from your admin panel it will display that html for step1 , step2.....etc.

$str_template=!empty($form_html['auditforms']) ? html_entity_decode($form_html['auditforms']) : '';
<?php
if (!empty($form_data)) {
    $form_html = unserialize($form_data[0]->post_values);
    $post_values_front = unserialize($form_data[0]->post_values_front);
}
?><section class="section novi-background sec_nw">
    <div class="container">
        <div class="section-sm-41 row justify-content-sm-center align-items-sm-center">
            <div class="card section-34">
                <?php echo form_open(base_url() . 'MultiStep/index/
' . $form_id . '/2/?fpi=' . $form_process_id . '&sid=' . $search_gstin_id . '&fy=' . $financial_year . ''
, array('class' => "", 'autocomplete' => "on", 'id' => 'form1', 'name' => 'form1', 'method' => 'post')); ?>
                <input type="hidden" name="form_id" id="form_id" 
value="<?php echo!empty($form_info) ? $form_info[0]->form_id : ''; ?>"> 
                <input type="hidden" name="step_id" id="step_id" value="<?php echo $next_step; ?>"> 
                <input type="hidden" name="sid" id="sid" value="<?php echo $search_gstin_id; ?>"> 
                <input type="hidden" name="fy" id="fy" value="<?php echo $financial_year; ?>"> 
                //your html element , form will go here step 1
                <input type="submit" class="btn bg-orange btn-flat pull-right" value="Next"/>
                <?php echo form_close(); ?> 
            </div>
        </div>
    </div>
</section>

7. Below is the step2 so previous and next value assign accordingly. you can set previous and next value or step_id, only your html part will be dynamic from mysql.

<section>
    <div class="container profile-edit">
        <div class="section-sm-41 row justify-content-sm-center align-items-sm-center">
            <div class="card section-34">
                <?php echo form_open(base_url() . 'MultiStep/index/
' . $form_id . '/3?fpi=' . $form_process_id . '&sid=' . $search_gstin_id . '&fy=' . $financial_year . ''
, array('class' => "", 'autocomplete' => "on", 
'enctype' => "multipart/form-data", 'id' => 'step1', 'name' => 'step1', 'method' => 'post')); ?>
                <input type="hidden" name="form_id" id="form_id" 
value="<?php echo!empty($form_info) ? $form_info[0]->form_id : ''; ?>"> 
                <input type="hidden" name="step_id" id="step_id" value="<?php echo $next_step; ?>">
                <input type="hidden" name="sid" id="sid" value="<?php echo $search_gstin_id; ?>"> 
                <input type="hidden" name="fy" id="fy" value="<?php echo $financial_year; ?>"> 
                //your html element , form will go here step 2
                <div class="col-offset-10">
                    <a href="<?php echo base_url() . 'MultiStep/index/
' . $form_info[0]->form_id . '/1/?sid=' . $search_gstin_id . 
'&fpi=' . $form_process_id . '&p=' . $actual_step_id_p . '&audit_form_id=' . $form_id . 
'&fy=' . $financial_year; ?>" class="btn bg-orange btn-flat pull-left">Prev</a>
                    <input type="submit" class="btn bg-orange btn-flat pull-right" value="Next"/>
                </div>
                <?php echo form_close(); ?> 
            </div>
        </div>
    </div>
</section>

8.In my project there are 24 steps so that is my last step , may in your case there is less step then mine , if you are making your last step so &isf=yes should be added in rest of step isf will not add to recognise last we are in last request.

<section class="section novi-background sec_tab">
    <div class="container profile-edit">
        <div class="section-sm-41 row justify-content-sm-center align-items-sm-center">
            <div class="card section-34 ">
                <?php echo form_open(base_url() . 'TaxAudit/index/
' . $form_id . '/24/?fpi=' . $form_process_id . '&isf=yes', array('class' => "", 
'autocomplete' => "on", 'id' => 'form1', 'name' => 'form1', 'method' => 'post')); ?>
                <input type="hidden" name="form_id" id="form_id" 
value="<?php echo!empty($form_info) ? $form_info[0]->form_id : ''; ?>"> 
         <input type="hidden" name="step_id" id="step_id" value="<?php echo $next_step; ?>"> 
         <input type="hidden" name="sid" id="sid" value="<?php echo $search_gstin_id; ?>"> 
         <input type="hidden" name="fy" id="fy" value="<?php echo $financial_year; ?>"> 
          //your html element , form will go here step finished
        <div class="col-offset-10">
        <a href="<?php echo base_url() . 'TaxAudit/index/
' . $form_info[0]->form_id . '/23/?sid=' . $search_gstin_id . '&fpi=
' . $form_process_id . '&p=' . $actual_step_id_p.'&
audit_form_id=' . $form_id . '&fy=' . $financial_year; ?>" class="btn bg-orange btn-flat pull-left">Prev</a>
           <input type="submit" class="btn bg-orange btn-flat pull-right" value="Finished"/>
           </div>
           <?php echo form_close(); ?> 
     </div>
     </div>
    </div>
</section>

9. The screenshot will show you the table structure to manange multi step form.


Tuesday 28 January 2020

User roles permission menu treeview

1. If you want to build a user role permission and dynamic side bar menu in your web application using Codeigniter and Mysql , In this senario all menu and submenu will show like a tree using jstree.Below is the js you will required

#link https://cdnjs.cloudflare.com/ajax/libs/jstree/3.2.1/themes/default/style.min.css
#https://cdnjs.cloudflare.com/ajax/libs/jstree/3.2.1/jstree.min.js

2. Below is the part of html you required to display all menu items (Menu Permission) , you can just copy and paste it in any html or where you want to show that menus for admin panel or user , you can customise accordingly. AdminMenu is the model , you can place this line in any of your view file it will return html of ul, li and if you want not directly placing in view you can call controller function then parse data in view file.

echo this->AdminMenu->get_menu($role_id);

3. Below script goes in your view file or javascript file. showalert_mpop is a javascript function , you can see my other post to check this function it simply alert , display message with setTimeOut.

<script>
    $(function () {
        var tree = $("#treeview2");
        tree.jstree({
            plugins: ["checkbox", 'json_data'],
            core: {
                "themes": {
                    "icons": false,
                    "variant": "large",
                    "multiple": false,
                    "check_callback": true,
                }
            },
            "checkbox": {
                "keep_selected_style": false,
                "two_state": true,
            }
        });
        tree.jstree(true).open_all();
        $('li[data-checkstate="checked"]').each(function () {
            tree.jstree('check_node', $(this));
        });
        tree.jstree(true).close_all();
        $(function () {
            $('#treeview2').on('select_node.jstree Event', function (e, data) {
                var checked_menu_id = '';
                var parent_menu_id = '';
                if (data.node.children.length > 0) {
                    $.each(data.node.children, function (key, value) {
                        checked_menu_id += $("#" + value).attr('name') + ',';
                    }).join(',');
                    parent_menu_id = $("#" + data.node.id).attr('name');
                } else {
                    checked_menu_id = $("#" + data.node.id).attr('name');
                    parent_menu_id = $("#" + data.node.parent).attr('name');
                }
                var rid_c = "";
                $.ajax({
                url: base_url+'Settings/do_save_menu_per',
                type: 'POST',
                dataType: 'json',
                data: {mid: checked_menu_id, rid: rid_c, status: 'checked', 'parent': parent_menu_id},
                beforeSend: function () {
                   $(".faster_ajax_loader").css('display', 'block');
                },
                complete: function () {
                   $(".faster_ajax_loader").css('display', 'none');
                },
                success: function (response) {
                     showalert_mpop(response.msg, 'gobal_msg');
                 }
                });
            });
        });
        $('.minimal').on('click', function(event){
            var is_chk_unch='';
            if(this.checked) {
                 is_chk_unch='y';
            }else{
               is_chk_unch='n';
            }            
            $.ajax({
             url: base_url + 'Settings/do_save_module_per',
             type: 'POST',
             dataType: 'json',
             data: {is_chk: is_chk_unch, id: $(this).prop('id')},
             beforeSend: function () {
                 $(".faster_ajax_loader").css('display', 'block');
             },
             complete: function () {
                 $(".faster_ajax_loader").css('display', 'none');
             },
             success: function (response) {
                    showalert_mpop(response.msg, 'gobal_msg');
             }
            });
        });
   });
</script>

4. Table schema you can view and download from below link and admin module you can also view see link

#https://github.com/boy108zon/User-Roles-Permission-Menu-TreeView/blob/master/table%20structure.txt
https://github.com/boy108zon/User-Roles-Permission-Menu-TreeView/blob/master/AdminMenu.php

5. The screenshot will show you the final output like side bar menu , module and permission with checkbox , you can easily assign permission based on your roles.



Friday 7 July 2017

php mysql mysqli quries class

Below class file handle mysql and mysqli connection & quries. you can select all records as well as can place where clause also saving records , removing records , executing direct query with Mysql OR MySqli as defined in config.php file.
1. Your config.php file will be as below , you can change it and placed values accordingly as your mysql database. resource_type Mysql OR MySqli
$dbconfig['db_server'] = 'localhost';
$dbconfig['db_port'] = ':3306';
$dbconfig['db_username'] = 'root';
$dbconfig['db_password'] = '';
$dbconfig['db_name'] = 'test';
$resource_type='MySqli';
2. Below is MysqlDatabaseMysqli.php class which has functions , you can execute Queries with MySql or MySqli.
require_once 'config.php';
Class MysqlDatabaseMysqli {
    protected $connection = NULL;
    protected $connection_string = NULL;
    protected $associate_type = NULL;
    function __construct() {
        global $resource_type;
        $this->resource_type_string = strtolower($resource_type);
        switch ($resource_type) {
            case 'MySql':
                $this->associate_type = MYSQL_ASSOC;
                $this->msl_connect();
                break;
            case 'MySqli':
                $this->associate_type = MYSQLI_ASSOC;
                $this->msli_connect();
                break;
            default:
                echo "No resource type define";
                exit;
                break;
        }
    }
    /*
     *  msl->mysql
     */
    function msl_connect() {
        global $dbconfig;
        try {
            if ($link_identifier = @mysql_connect($dbconfig['db_server'], 
                $dbconfig['db_username'], $dbconfig['db_password'])) {
                $select_db = @mysql_select_db($dbconfig['db_name'], $link_identifier);
                if (!$select_db) {
                    throw new Exception(mysql_errno() . ' ' . mysql_error());
                }
                $this->connection = $link_identifier;
            } else {
                throw new Exception(mysql_errno() . ' ' . mysql_error());
            }
        } catch (Exception $e) {
            echo $e->getMessage();
        }
    }
    /*
     *  msli->mysqli
     */
    function msli_connect() {
        global $dbconfig;
        try {
            if ($link_identifier = @mysqli_connect($dbconfig['db_server']
                , $dbconfig['db_username'], $dbconfig['db_password'])) {
                $select_db = @mysqli_select_db($link_identifier, $dbconfig['db_name']);
                if (!$select_db) {
                    throw new Exception(mysqli_connect_errno() . ' Unable to select db');
                }
                $this->connection = $link_identifier;
            } else {
                throw new Exception(mysqli_connect_errno() . ' Unable to connect');
            }
        } catch (Exception $e) {
            echo $e->getMessage();
        }
    }
    /*
     *  Created By: Boy108zon
     *  @table_name: string
     *  @where: Array
     */
    function get_records($table_name, $where=NULL) {
        $connect = $this->resource_type_string . '_query';
        $num_rows = $this->resource_type_string . '_num_rows';
        $connect_close = $this->resource_type_string . '_close';
        /* if where included */
        $qwhere = '';
        if (is_array($where)) {
            $counter = 0;
            foreach ($where as $key => $value) {
                if ($counter > 0) {
                    $qwhere .= ' AND ';
                }
                $qwhere .= "$key = '$value'";
                $counter++;
            }
            $qwhere = 'WHERE ' . $qwhere;
        }
        $query = "SELECT * from $table_name $qwhere";
        if ($this->resource_type_string == 'mysqli') {
            $result_resource = $connect($this->connection, $query);
        } else {
            $result_resource = $connect($query, $this->connection);
        }
        $noOfrows = $num_rows($result_resource);
        if ($noOfrows > 0) {
            return $this->db_to_array($result_resource);
        } else {
            return NULL;
        }
    }
    /*
     *  Created By: Boy108zon
     *  @table_name: string
     *  @where: Array
     */
    function remove_records($table_name, $where=NULL) {
        $connect = $this->resource_type_string . '_query';
        $affected_rows = $this->resource_type_string . '_affected_rows';
        $connect_close = $this->resource_type_string . '_close';
        /* if where included */
        $qwhere = '';
        if (is_array($where)) {
            $counter = 0;
            foreach ($where as $key => $value) {
                if ($counter > 0) {
                    $qwhere .= ' AND ';
                }
                $qwhere .= "$key = '$value'";
                $counter++;
            }
            $qwhere = 'WHERE ' . $qwhere;
        }
        $query = "DELETE from $table_name $qwhere";
        if ($this->resource_type_string == 'mysqli') {
            $result_resource = $connect($this->connection, $query);
        } else {
            $result_resource = $connect($query, $this->connection);
        }
        $idds = $affected_rows($this->connection);
        //close connection
        @$connect_close($this->connection);
        if ($idds > 0) {
            return $idds;
        } else {
            return NULL;
        }
    }
    /*
     *  Created By: Boy108zon
     *  @result->mysql OR mysqli
     */
    function db_to_array($result) {
        $connection_string = $this->resource_type_string . '_fetch_array';
        $rows = array();
        while ($row = $connection_string($result, $this->associate_type)) {
            $rows[] = $row;
        }
        //close connection
        @$connect_close($this->connection);
        return $rows;
    }
    /*
     *  Created By: Boy108zon
     *  @table_name: String
     *  @post_array:Array
     *  @where:Array
     *  @mode:'save' OR edit
     *  Based on mode return type affected rows and latest insert id
     */
    function save_records($table_name, $post_array, $where=NULL, $mode='save') {
        $connect = $this->resource_type_string . '_query';
        $insert_id = $this->resource_type_string . '_insert_id';
        $affected_rows = $this->resource_type_string . '_affected_rows';
        $connect_close = $this->resource_type_string . '_close';
        $count = 0;
        $fields = '';
        foreach ($post_array as $col => $val) {
            if ($count > 0) {
                $fields .= ', ';
            }
            $fields .= "$col = '$val'";
            $count++;
        }
        $qwhere = '';
        if (is_array($where)) {
            $counter = 0;
            foreach ($where as $key => $value) {
                if ($counter > 0) {
                    $qwhere .= ' AND ';
                }
                $qwhere .= "$key = '$value'";
                $counter++;
            }
            $qwhere = 'WHERE ' . $qwhere;
        }
        if ($mode == 'edit') {
            $query = "UPDATE " . $table_name . " SET $fields $qwhere;";
        } else {
            $query = "INSERT INTO " . $table_name . " SET $fields;";
        }
        if ($this->resource_type_string == 'mysqli') {
            $result_resource = $connect($this->connection, $query);
        } else {
            $result_resource = $connect($query, $this->connection);
        }
        if ($mode == 'edit') {
            $idds = $affected_rows($this->connection);
        } else {
            $idds = $insert_id($this->connection);
        }
        //close connection
        @$connect_close($this->connection); 
        if ($idds > 0) {
            return $idds;
        } else {
            return NULL;
        }
    }
    /*
     *  Created By: Boy108zon
     *  @query: full query including inner , where
     *  if required we can also use it.
     */
    function get_direct_query_records($query) {
        $connect = $this->resource_type_string . '_query';
        $num_rows = $this->resource_type_string . '_num_rows';
        $connect_close = $this->resource_type_string . '_close';
        if ($this->resource_type_string == 'mysqli') {
            $result_resource = $connect($this->connection, $query);
        } else {
            $result_resource = $connect($query, $this->connection);
        }
        $noOfrows = $num_rows($result_resource);
        //close connection
        @$connect_close($this->connection); 
        if ($noOfrows > 0) {
            return $this->db_to_array($result_resource);
        } else {
            return NULL;
        }
    }
}
3. You can use it like below
require_once 'MysqlDatabaseMysqli.php'; 
$dbfun=new MysqlDatabaseMysqli(); 

$result=$dbfun->get_records('employee',array('name'=>'amit'));
//For Retrive records from employee table. 
$result=$dbfun->get_records('your_table_name');
//edit 
$result=$dbfun->save_records('your_table_name',$post_array,$where,$mode='Edit');
//save 
$result=$dbfun->save_records('your_table_name',$post_array);
//remove 
 $result=$dbfun->remove_records('your_table_name',array('id'=>6));
// direct query 
$result=$dbfun->get_direct_query_records($query='select * from employee');
4. You can download class from below
https://github.com/boy108zon/SingelMysqlMysqli

Saturday 26 December 2015

Codeigniter Categories sub categories dictionary

Some times you want categoriesOr i can say that a dictionary which needs to be fetch from mysql , there for all links , content will be fetch from database mysql by creating a singel view file with support of jQuery for processing menus. There will be your Administration OR Admin panelwho will create these categories , sub categories.
 
1.Your Controller functions will goes like below.
defined('BASEPATH') OR exit('No direct script access allowed');
class School extends MY_Controller {
public function __construct() {
parent::__construct();
}
public function get_dictionary() {
$this->load->model('schools');
$this->load->model('trading_tools');
$data['meta_title'] = 'School';
$data['meta_keywords'] = 'School';
$data['meta_desc'] = 'School';
$data['signup_signin_model'] = 'root/signin_up_model_html';
$parent_slug = trim($this->uri->segment(2));
$child_slug = trim($this->uri->segment(3));
if (isset($child_slug) && $child_slug != '') {
$data = array($child_slug);
$where = 'slug_name';
$data['school_info'] = $this->schools->get_records_where_in('schools_category', $where, $data);
} else {
$data['school_info'] = $this->schools->get_school_info($parent_slug);
}
$menu = $this->trading_tools->get_items();
$data['side_bar_menu'] = $this->buildMenu(0, $menu);
$data['loginheader'] = '';
$data['top_header'] = 'root/top_header';
$data['top_menu'] = 'root/top_menu';
$data['parent_slug_name'] = $parent_slug;
$data['child_slug_name'] = $child_slug;
if ($parent_slug != 'glossary') {
$data['main'] = 'root/school_child';
} else {
$data['main'] = 'root/school_glossary';
}
$data['footer'] = 'root/footer';
$this->load->vars($data);
$this->load->view($this->template);
}
public function buildMenu($parent, $menu) {
$html = "";
if (isset($menu['parent_menus'][$parent])) {
if ($parent <= 0)
$html .= "<ul class='nav nav-pills nav-stacked' style='background:#E9E9E9;'>";
else
$html.= '<ul class="dropdown-menu">';
$countfor_active = 0;
foreach ($menu['parent_menus'][$parent] as $menu_id) {
$li_parentclass = '';
$ul_inside_li = '<ul class="dropdown-menu ' . $menu['menus'][$menu_id]['slug_name'] . '>';
if ($countfor_active == 0) {
$li_parentclass = 'dropdown ' . $menu['menus'][$menu_id]['slug_name'] . '';
}
if (!isset($menu['parent_menus'][$menu_id])) {
$html .= "<li class='" . $li_parentclass . "'>
<a href='" . $menu['menus'][$menu_id]['page_link'] . "'>
" . $menu['menus'][$menu_id]['category_name'] . "</a></li>";
}
if (isset($menu['parent_menus'][$menu_id])) {
$html .= "<li class='" . $li_parentclass . "'>
<a href='" . $menu['menus'][$menu_id]['page_link'] . "'> <i
class='fa fa-angle-right pull-right'></i>
" . $menu['menus'][$menu_id]['category_name'] . "</a>";
$html .= $this->buildMenu($menu_id, $menu);
$html .= "</li>";
}
}
$html .= "</ul>";
}
return $html;
}
}
2. Basically i am using two views filesbecause layout OR html is different for both. One view file will contain Dictionary (school_glossary) and sencond view (school_child) file will contain simple content to display.
 
A) school_glossary View
<div class="l-main-container">
<div class="b-breadcrumbs f-breadcrumbs">
<div class="container">
<ul>
<li class="dropdown"> <a class="dropdown-toggle" data-toggle="dropdown"
href="#"><i class="fa fa-home"></i>Home</a></li>
<li><i class="fa fa-angle-right"></i><a href="#">Forex Tools</a></li>
<li><i class="fa fa-angle-right"></i><span>Forex School</span></li>
</ul>
</div>
</div>
<div class="l-inner-page-container">
<div class="container">
<div class="row">
<div class="col-md-9 col-md-push-3">
<div class="b-shortcode-example">
<div class="b-tabs f-tabs j-tabs b-tabs-reset ui-tabs ui-widget ui-widget-content ui-corner-all">
<ul class="ui-tabs-nav ui-helper-reset ui-helper-clearfix
ui-widget-header ui-corner-all" role="tablist">
<?php if (count($school_info) > 0) { ?>
<?php
$alphabet = array('A'=>'glossary-a','B'=>'glossary-b','C'=>'glossary-c','D'=>'glossary-d',
'E'=>'glossary-e','F'=>'glossary-f','G'=>'glossary-g','H'=>
'glossary-h','I'=>'glossary-i','J'=>'glossary-j','K'=>'glossary-k',
'L'=>'glossary-l','M'=>'glossary-m','N'=>'glossary-n','O'=>
'glossary-o','P'=>'glossary-p','Q'=>'glossary-q','R'=>'glossary-r','S'=>'glossary-s'
,'T'=>'glossary-t','U'=>'glossary-u','V'=>'glossary-v','W'=>'glossary-w',
'X'=>'glossary-x','Y'=>'glossary-y','Z'=>'glossary-z');
foreach ($school_info as $key => $value) {?>
<li class="ui-state-default ui-corner-top" role="tab" tabindex="-1"
aria-controls="tabs-21" aria-labelledby="ui-id-1" aria-selected="false" aria-expanded="false">
<a href="#tabs-<?php echo $school_info[$key]->s_category_id;?>"
class="ui-tabs-anchor" role="presentation" tabindex="-1"
id="ui-id-<?php echo $school_info[$key]->s_category_id;?>">
<?php foreach ($alphabet as $al => $alp) {?>
<?php if($alp==$school_info[$key]->slug_name){ ?>
<?php echo $al; ?>
<?php }?>
<?php }?>
</a>
</li>
<?php } ?>               
<?php }?>
</ul>
<div class="b-tabs__content">
<?php if (count($school_info) > 0) { ?> 
<?php foreach ($school_info as $key => $value) {?>
<div id="tabs-<?php echo $school_info[$key]->s_category_id;?>"
aria-labelledby="ui-id-<?php echo $school_info[$key]->s_category_id;?>"
class="ui-tabs-panel ui-widget-content ui-corner-bottom" role="tabpanel" 
aria-hidden="true" style="display: none;">
<?php echo html_entity_decode($school_info[$key]->page_body); ?>
</div>
<?php }?>
<?php }?> 
</div>
</div>
</div>
</div>
<div class="visible-xs-block visible-sm-block b-hr"></div>
<div class="col-md-3 col-md-pull-9">
<?php if (isset($side_bar_menu)) { ?>
<?php echo $side_bar_menu; ?>
<?php } ?>
</div>
</div>
</div>
</div>
</div>
<script>
$(document).ready(function () {
var p_slug_name=".<?php echo $parent_slug_name;?>";
$(".nav").find(p_slug_name).addClass('active');
});
</script>
B) school_child View
<div class="l-main-container">
<div class="b-breadcrumbs f-breadcrumbs">
<div class="container">
<ul>
<li class="dropdown"> <a class="dropdown-toggle" data-toggle="dropdown"
href="#"><i class="fa fa-home"></i>Home</a></li>
<li><i class="fa fa-angle-right"></i><a href="#">Forex Tools</a></li>
<li><i class="fa fa-angle-right"></i><span>Forex School</span></li>
</ul>
</div>
</div>
<div class="l-inner-page-container">
<div class="container">
<div class="row">  
<?php if (count($school_info) > 0) { ?>
<div class="col-md-9 pull-right"><h4><?php echo $school_info[0]->category_name;?></h4></div>
<?php } ?>
<div class="col-md-9 col-md-push-3">
<div class="b-blog-listing__block">
<div class="b-blog-listing__block-top"> </div>
<div class="b-infoblock-with-icon b-blog-listing__infoblock">
<div class="b-infoblock-with-icon__info f-infoblock-with-icon__info">
<?php if (count($school_info) > 0) { ?>
<?php echo html_entity_decode($school_info[0]->page_body); ?>
<?php } ?>
</div>
</div>
</div>
</div>
<div class="visible-xs-block visible-sm-block b-hr"></div>
<div class="col-md-3 col-md-pull-9">
<?php if (isset($side_bar_menu)) { ?>
<?php echo $side_bar_menu; ?>
<?php } ?> 
</div>
</div>
</div>
</div>
</div>
<script>
$(document).ready(function () {
var p_slug_name=".<?php echo $parent_slug_name;?>";
$(".nav").find(p_slug_name).addClass('active');
});
</script>
3. Your model function will goes like below
class Schools extends CI_Model {
function __construct() {
// Call the Model constructor
parent::__construct();
$this->load->library('session');
$this->load->database();
}
function get_school_info($parent_slug){
$q_school="select * from cbf_schools_category where parentid =(select s_category_id
from cbf_schools_category where slug_name='".$parent_slug."')";
$query=$this->db->query($q_school);
if ($query->num_rows() > 0)
return $query->result();
else
$q_get_main="select * from cbf_schools_category where slug_name='".$parent_slug."'";
$query1=$this->db->query($q_get_main);
return $query1->result();
}
function get_records_where_in($table, $where, $data) {
$this->db->where_in($where, $data);
$query = $this->db->get($table);
if ($query->num_rows() > 0)
return $query->result();
else
return NULL;
}
}
4. You will need to define a routes in your routes.php
$route['school/(:any)'] = 'school/get_dictionary/$1';
5. Your Mysql table goes like below which will contain parent , child relationship in single table.

Saturday 28 November 2015

Carousel slider codeigniter mysql

You may also see the post here http://yourlearn.in/posts/view/1/bootstrap-carousel-thumbnail-slider and know and read much more about it.

 
 
 
If you want to implement Bootstrap Carousel Thumbnail Slider with Codeigniter, follow the steps
1.Your html file or you can say your view file will goes like below.
  <!DOCTYPE html>
  <html lang="en">
  <head>
   <title>Bootstrap Example</title>
   <meta charset="utf-8">
   <meta name="viewport" content="width=device-width, initial-scale=1">
   <link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
   <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
   <script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
   <style>
    .carousel-inner > .item > img,
    .carousel-inner > .item > a > img {
     width: 70%;
     margin: auto;
    }
   </style>
  </head>
 <body>
 <div id="container"> 
   <div id="slider-property" class="carousel slide" data-ride="carousel">
    <!-- Indicators -->
    <ol class="carousel-indicators">
      <?php echo $indicators; ?>
    </ol>

   <!-- Wrapper for slides -->
   <div class="carousel-inner" role="listbox">
      <?php echo $slides; ?>
   </div>

  <!-- Left and right controls -->
   <a class="left carousel-control" href="#slider-property" role="button" data-slide="prev">
    <span class="glyphicon glyphicon-chevron-left" aria-hidden="true"></span>
    <span class="sr-only">Previous</span>
   </a>
   <a class="right carousel-control" href="#slider-property" role="button" data-slide="next">
    <span class="glyphicon glyphicon-chevron-right" aria-hidden="true"></span>
    <span class="sr-only">Next</span>
   </a>
  </div>
  </div>
   </div>
 </body>
 </html>
2. Your controller function will goes like below
  class Welcome extends CI_Controller {
        public function index(){
         }    
    public function get_all_images(){  
      $this->load->model('carsol'); 
      $query = $this->carsol->get_all_images();
     $count = count($query);
     $indicators = '';
      $slides = '';
      $counter = 0;  
      foreach($query AS $key => $value){
          $image = $query[$key]['theme_thumb'];
          $title = $query[$key]['theme_title'];
          if ($counter == 0) {
            $indicators .= '<li data-target="#slider-property" data-slide-to="' . $counter . '" 
            class="active"></li>';
            $slides .= '<div class="item active">
            <img src="http://localhost/CI2.1.4/images/thumbnail/' . $image . '" alt="' . $title . '"/>
            </div>';
          } else {
            $indicators .= '<li data-target="#slider-property" 
            data-slide-to="' . $counter . '"></li>';
            $slides .= '<div class="item">
            <img src="http://localhost/CI2.1.4/images/thumbnail/' . $image . '" alt="' . $title . '"/>
            </div>';
          }
          $counter=$counter+1;
      }
      $data['indicators'] = $indicators;
      $data['slides'] = $slides;
      $this->load->view('carsol', $data);   
    }       
 }
3. Your model function will goes like below
  class Carsol extends CI_Model {
   function __construct() {
     // Call the Model constructor
      parent::__construct();
     $this->load->database();
   }
   function get_all_images(){
     $query = $this->db->query("SELECT * FROM theme_themes where category_id =3 limit 4");
     return $query->result_array();
 }
}

Tuesday 24 November 2015

Multiple records delete datatables ajax

If you want to implement add bulk action like Export CSV , Removing all , single records , Export seleted records to excel and adding filters like days filter to data tables listings using ajax approch 1. You need to include required jQuery for datatables , ajax processing.
<script src="<?php echo base_url(); ?>assets/js/jquery-1.11.0.min.js"></script>
<script src="<?php echo base_url(); ?>assets/js/jquery.dataTables.min.js"></script>
<script src="<?php echo base_url(); ?>assets/js/dataTables.bootstrap.js"></script>
<script src="<?php echo base_url();?>assets/js/datatables/responsive/js/datatables.responsive.js">
</script>
<script src="<?php echo base_url(); ?>assets/js/datatables/jquery.dataTables.columnFilter.js">
</script>
2. Your html source file or you can say your view file will goes like below.
<h3 class="page-title">Accounts</h3>
<div class="panel panel-primary" data-collapsed="0">
<div class="col-sm-12">
<div class="panel-body">
<form id="Gexcel" name="Gexcel" method="POST" action="<?php echo base_url(); ?
>accounts/create_document">
<input type="hidden" name="aidds" id="aidds" ><input type="hidden" name="atype" id="atype" value="da" >
</form>
<table id="demo_account_listing" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th class="no-sort"></th>
<th class="trigger_reload">Sr No</th>
<th>ID</th>
<th>Date</th>
<th class="no-sort">Days Left</th>
<th>Name</th>
<th>Email Address</th>
<th>Country</th>
<th>Phone</th>
<th>Mobile</th>
<th>Photo</th>
<th>Source</th>
<th class="no-sort"> Action</th>
</tr>
</thead>
</table>
</div>
</div>
</div>
<script type="text/javascript">
jQuery(window).load(function () {
var $ = jQuery;
var oAllLinksTable = $("#demo_account_listing").dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": baseurl + 'accounts/get_account_listing',
"sPaginationType": "bootstrap",
"oLanguage": {
"sEmptyTable": "No account found.",
"sProcessing": "<img src='<?php echo base_url(); ?>/assets/global/img/loading-spinner-blue.gif'>"
},
"columnDefs": [{
"targets": 'no-sort',
"orderable": false
}],
"aoColumnDefs": [{"bVisible": true, "aTargets": [0]}],
"order": [[0, "desc"]],
processing: true,
"fnServerParams": function (aoData) {
var last_days = $('input[name=days_filter]:checked').val();
aoData.push({name: "days_filter", value: last_days});
},
"fnRowCallback": function (nRow, aData, iDisplayIndex) {
var oSettings = oAllLinksTable.fnSettings();
$("td:nth-child(2)", nRow).html(oSettings._iDisplayStart + iDisplayIndex + 1);
$(nRow).attr("id", 'tr_' + aData[0]);
$("td:nth-child(1)", nRow).html("
<input type='checkbox' name='contacts[]' class='mail-checkbox check_' value='" + aData[0] + "'>");
return nRow;
}
});
var action_radio = '<div class="pull-right check_filter" id="filter_group_radio"><span>
<input type="radio" name="days_filter" id="dsv" class="form-control days_filter" value="7"> 
Last7 Days</span>&nbsp;<span>
<input type="radio" id="df" name="days_filter" class="form-control days_filter" value="14">
Last 14 Days &nbsp;</span><span>
<input type="radio" id="dthi" name="days_filter" class="form-control days_filter" value="30">
Last 30 Days&nbsp;</span><span>
<input type="radio" id="dsx" name="days_filter" class="form-control days_filter" value="60">
Last 60 Days</span></div>';
var extra_action = action_radio + '<div class="pull-right">
<a href="<?php echo base_url(); ?>accounts/new_account">
<button class="btn purple-plum pull-right" type="button">Open New Demo Account</button>
</a>&nbsp;</div>';
$custome_search_filter = extra_action + ' <span class="btn blue" style="height:34px;">
<input type="checkbox" class="checkallbox" id="select_all_"></span><div class="pull-right">
<div class="btn-group myremove"><button data-toggle="dropdown" 
class="btn blue dropdown-toggle dropup" aria-expanded="false">
Bulk Action<i class="fa fa-angle-down"></i></button><ul role="menu" 
class="dropdown-menu pull-right">
<li role="presentation"><a data-toggle="modal" class="deleteRecord" 
href="" tabindex="-1" role="menuitem">
Delete</a></li><li role="presentation">
<a data-toggle="modal" class="exportExcel" href="" tabindex="-1"
role="menuitem">Export to excel</a></li></ul></div></div>';
$($custome_search_filter).appendTo("#demo_account_listing_length > label");
$("#demo_account_listing_length").parent().removeClass('col-md-6').addClass('col-md-10');
$("#demo_account_listing_filter").parent().removeClass('col-md-6').removeClass('col-sm-12');
$(function () {
$(document).on('change', '.days_filter', function (event) {
event.preventDefault();
oAllLinksTable.fnFilter(this.value, 12);
$(this).find('#filter_group_radio input:radio').prop('checked', true);
});
});
$(document).on('click', '.checkallbox', function () {
if ($('#select_all_').is(':checked')) {
$(".check_").prop("checked", "checked");
} else {
$(".check_").removeAttr('checked');
}
});
$(document).on('click', '.check_', function () {
var $this = $(this);
// $this will contain a reference to the checkbox
var numberOfChecked = $('.check_:checked').length;
var totalCheckboxes = $('.check_').length;
var numberNotChecked = totalCheckboxes - numberOfChecked;
if (numberOfChecked == totalCheckboxes) {
$('#select_all_').prop('checked', 'checked');
}
else {
$('#select_all_').removeAttr('checked');
}
});
$(document).on('click', '.deleteRecord', function (e) {
e.preventDefault();
var allVals = [];
$('.check_').each(function () {
if ($(this).is(':checked')) {
allVals.push($(this).val());
}
});
if ($('.check_').is(':checked')) {
bootbox.confirm("Are you sure you want to delete?", function (result) {
if (result == true) {
$.ajax({
url: baseurl + 'accounts/removeAccounts?time=' + timestamp,
type: 'post',
data: 'atype=la&rids=' + allVals,
dataType: 'json',
success: function (response) {
$("#showmessage_jscbf").html("");
switch (response.Mstatus) {
case 'success':
$.each(response.idds.split(','), function () {
$("#tr_" + this).hide();
});
$(".trigger_reload").trigger('click');
break;
case 'error':
$("#showmessage_jscbf").html(response.msg);
break;
}
}
});
}
});
return false;
} else {
alert("Please select one record to delete.")
}
});
$(document).on('click', '.exportExcel', function (e) {
e.preventDefault();
var allVals = [];
$('.check_').each(function () {
if ($(this).is(':checked')) {
allVals.push($(this).val());
}
});
if ($('.check_').is(':checked')) {
$("#aidds").val(allVals);
document.Gexcel.submit();
} else {
alert("Please select one records to export.")
}
});
});
$(function () {
$(document).on('click', '.removeRcords', function (event) {
event.preventDefault();
var idss = $(this).attr('id');
var rid = idss.split("_")[1];
do_remove_ajax('account_type_remove', rid);
});
});
</script>
Notes:
I. Gexcel is a form where all selected ids will be store in hidden id aidds textbox.
II. demo_account_listing_length , you can just open your firebug and can get your id from there.you can see in above html that my id is demo_account_listing so datatable attaching his event to this id.
III. action_radio , $custome_search_filter , extra_action is the filter html , we have to inject to datatables.
IV. fnRowCallback function is a datatable function , i have to put s.no there also $("td:nth-child(1)", nRow) is checkbox which will inject with primary key of your table in mysql.
V. bootbox.confirm is alert box , you can replace simple confim alert box of javascript if you don't need.
VI. $.each(response.idds.split(','), function () { $("#tr_" + this).hide();}); from ajax response comma seperated ids will be revert back from your controller.
3. Your controller function will go like below
public function get_account_listing() {
$this->load->model('common');
$aColumns = array('id', 'id', 'account_no', 'regdate', 'daysleft', 'name', 'email', 
'country', 'mobile', 'leverage', 'balance', 'lead_source');
$aColumnsWhere = array('a.id', 'a.account_no', 'a.date_time', 'a.email', 'a.leverage', 'a.balance',
'da.firstname', 'da.lastname', 'da.regdate', 'da.mobile', 'c.country_name', 'da.lead_source');
$sIndexColumn = "id";
$sTable = "account";
//custome filter
$days_filter = (int) $this->input->get('days_filter', TRUE);
$iDisplayStart = $this->input->get('iDisplayStart', true);
$iDisplayLength = $this->input->get('iDisplayLength', true);
$iSortCol_0 = $this->input->get('iSortCol_0', true);
$iSortingCols = $this->input->get('iSortingCols', true);
$sLimit = "";
if (isset($iDisplayStart) && $iDisplayLength != '-1') {
$sLimit = "LIMIT " . $iDisplayStart . ", " .
$iDisplayLength;
}
if (isset($iSortCol_0)) {
$sOrder = "ORDER BY ";
for ($i = 0; $i < intval($iSortingCols); $i++) {
if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i])] == "true") {
$sOrder .= $aColumns[intval($_GET['iSortCol_' . $i])] . "
" . trim($_GET['sSortDir_' . $i]) . ", ";
}
}
$sOrder = substr_replace($sOrder, "", -2);
if ($sOrder == "ORDER BY") {
$sOrder = "";
}
}
$sSearch = $this->input->get('sSearch', true);
$sWhere = "";
if ($sSearch != "") {
$sWhere = "AND (";
for ($i = 0; $i < count($aColumnsWhere); $i++) {
$sWhere .= $aColumnsWhere[$i] . " LIKE '%" . $sSearch . "%' OR ";
}
$sWhere = substr_replace($sWhere, "", -3);
$sWhere .= ')';
}
if ($days_filter != '') {
$sWhere .= "AND (";
$sWhere .= '' . $aColumnsWhere[2] . ' BETWEEN DATE_SUB(NOW(),INTERVAL ' . $days_filter . ' DAY) AND 
NOW()';
//$sWhere .= 'DATE('.$aColumnsWhere[12].')' . " BETWEEN 
'" . DATE('Y-m-d',strtotime($start_date)) . "' AND '".DATE('Y-m-d',strtotime($end_date))."'";
$sWhere .= ')';
}
/* Individual column filtering */
for ($i = 0; $i < count($aColumns); $i++) {
if ($_GET['bSearchable_' . $i] == "true" && $_GET['sSearch_' . $i] != '') {
if ($sWhere == "") {
$sWhere = " WHERE ";
} else {
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i] . " LIKE '%" . trim($_GET['sSearch_' . $i]) . "%'";
}
}
$sEcho = $this->input->get('sEcho');
$result = $this->common->demo_account_listing($sTable, $sWhere, $sOrder, $sLimit, 
$aColumns, $sIndexColumn, $sEcho);
echo json_encode($result);
}
4. Your model code goes like below
function demo_account_listing($sTable, $sWhere, $sOrder,$sLimit, $aColumns, $sIndexColumn, $sEcho){
$sJoin_q_fields = "SELECT SQL_CALC_FOUND_ROWS a.id,a.account_no,a.date_time,
a.email,a.leverage,a.balance, da.regdate, da.firstname as name,
da.mobile, c.country_name as country, da.lead_source";
$sJoin_q = " FROM (account a)";
$sJoin_q .= ' INNER JOIN demo_register da ON a.email = da.email';
$sJoin_q .= ' INNER JOIN country c ON da.country = c.id';
$sJoin_q .= ' WHERE a.act_type = "ABC" ';
$qJoin = $sJoin_q_fields . $sJoin_q . ' ' . $sWhere . ' ' . $sOrder . ' ' . $sLimit;
$rResult = $this->db->query($qJoin);
$rResult_array = $rResult->result_array();
$iFilteredTotal = count($rResult_array);
/* Total data set length */
$sJoin_q_count = "SELECT COUNT(" . $sIndexColumn . ") AS TotalRecords";
$sQuery_TR = $sJoin_q_count . $sJoin_q.$sWhere;
$rResult_TR = $this->db->query($sQuery_TR);
$rResult_array_TR = $rResult_TR->result_array();
$iTotal = $rResult_array_TR[0]['TotalRecords'];
$output = array(
"sEcho" => intval($sEcho),
"iTotalRecords" => intval($iTotal),
"iTotalDisplayRecords" => intval($iTotal), //$iFilteredTotal,
"aaData" => array()
);
$i = 0;
$last = intval($iTotal);
$seclast = intval($iTotal) - 1;
foreach ($rResult_array as $aRow) {
$row = array();
foreach ($aColumns as $col) {
if ($col == 'daysleft') {
$rem = $this->ref_time - strtotime($aRow['regdate']);
$row[] = 30 - floor($rem / 86400);
} else if ($aRow[$col] == 'D') {
$row[] = '<a href="javascript:void(0)" id="E_' . $aRow['id'] . '" name=""
class="btn btn-success btn-sm "><i class="entypo-check"></i> Enable</a>';
} else if ($aRow[$col] == 'E') {
$row[] = '<a href="javascript:void(0)" id="D_' . $aRow['id'] . '"
name="broker_account_status_request_ed" class="btn btn-danger btn-sm ">
<i class="entypo-minus"></i> Disable</a>';
} else {
$row[] = $aRow[$col];
}
}
$i++;
if ($i == $last || $i == $seclast) {
$classaction = 'dropup';
} else {
$classaction = '';
}
array_push($row, '<div class="btn-group ' . $classaction . ' ">
<button data-toggle="dropdown" class="btn red dropdown-toggle" aria-expanded="false">Action
<i class="fa fa-angle-down"></i>
</button>
<ul role="menu" class="dropdown-menu pull-right">
<li role="presentation">
<a href="' . base_url() . 'accounts/change_mt4_pw/' . $aRow['id'] . '" tabindex="-1" role="menuitem">
Change Password
</a>
</li>
<li role="presentation">
<a href="' . base_url() . 'accounts/delete_account/' . $aRow['id'] . '" tabindex="-1" role="menuitem">
Delete Account
</a>
</li>
<li role="presentation">
<a href="' . base_url() . 'accounts/link_account/' . $aRow['id'] . '" tabindex="-1" role="menuitem">
Link Account
</a>
</li>
</ul>
</div>');
$output['aaData'][] = $row;
}
return $output;
}
5. Your view load function will goes like below
public function index() {
$data['meta_title'] = 'Accounts';
$data['meta_keywords'] = 'Accounts';
$data['meta_desc'] = 'Accounts';
$data['top_mobile_menu'] = 'admin/dashboard/top_mobile_menu';
$data['top_menu'] = 'admin/dashboard/top_menu';
$data['main'] = 'admin/dashboard/accounts/account_listing';
$data['footer'] = 'admin/dashboard/footer';
$this->load->vars($data);
$this->load->view($this->admin_dashboard);
}
6. Your function for exporting i am using dompdf library for codeigniter. you can get it after googling.
public function create_document() {
$this->load->helper('dompdf');
$this->load->helper('file');
$this->load->model('prints');
$account_ids = $this->input->post('aidds');
$account_type = $this->input->post('atype');
$result = $this->prints->print_account_listing(format_in_clause($account_ids));
if (!empty($result)) {
$this->load->library('excel');
$this->excel->to_excel($result, "Aaccount-Listing");
exit;
} else {
$this->session->set_flashdata('msg',
alertmessage($message_type = 'alert-danger', $message = 'Unable to process your requiest.'));
redirect(base_url() . 'accounts');
}
}
7. Your function for removing records.
public function removeAccounts() {
$this->load->model('common');
$id = trim($this->input->post('rids'));
$account_type = trim($this->input->post('atype'));
if (isset($id)) {
$thread_ids = explode(",", $id);
foreach ($thread_ids as $values) {
$this->common->do_remove_records('account', array('id' => $values, 'act_type' => 'DEMO'));
}
echo json_encode(array('Mstatus' => 'success', 'idds' => $id,
'msg' => alertmessage($message_type = 'alert-success', $message = 'Accounts has been removed.')));
} else {
echo json_encode(array('Mstatus' => 'error',
'msg' => alertmessage($message_type = 'alert-danger', $message = 'Error in process. Try later.')));
}
}
8. alertmessage is a helper function for generating bootstrap message.
function alertmessage($message_type, $message) {
$output = '';
if (!empty($message_type) && !empty($message)) {
$output.="<div class='alert showmessage_jscbf " . $message_type . "'>" . $message . "</div>";
return $output;
} else {
return $output;
}
}
9. format_in_clause is a helper function.
function format_in_clause($string) {
if (isset($string)) {
$realArray = explode(',', $string);
if (is_array($realArray)) {
$stringForIn = "'" . implode("','", $realArray) . "'";
return $stringForIn;
} else {
return $string;
}
} else {
return NULL;
}
}

Saturday 30 May 2015

Datatables ajax bootstrap pagination with codeigniter

If you want to implement datatables with ajax using codeigniter and you can also use code if you are using core php stuff in your application.

1. You need to include required jQuery for datatables , ajax processing.
<script src="<?php echo base_url(); ?>assets/js/jquery-1.11.0.min.js"></script>
<script src="<?php echo base_url(); ?>assets/js/jquery.dataTables.min.js"></script>
<script src="<?php echo base_url(); ?>assets/js/dataTables.bootstrap.js"></script>
<script src="<?php echo base_url(); ?>assets/js/datatables/responsive/js/datatables.responsive.js">
</script>
<script src="<?php echo base_url(); ?>assets/js/datatables/jquery.dataTables.columnFilter.js">
</script>
        
2. Your html source file or you can say your view file will goes like below.
<div class="main-content">
<div class="row"><h2>Brokers Listing</h2>
<div class="panel panel-primary" data-collapsed="0">
<div class="panel-body">
<table class="table table-bordered table-striped datatable" id="broker_listing">
<thead><tr>
<th class="no-sort">#</th>
<th>Broker Name</th>
<th>Email</th>
<th>Created Date</th>
<th>Status</th>
<th class="no-sort">Actions</th>
</tr></thead><tbody></tbody>
</table>
</div>
</div>
        
<script type="text/javascript">
jQuery(window).load(function(){
var $ = jQuery;
$("#broker_listing").dataTable({
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": baseurl+'broker/get_broker_listing',
"sPaginationType": "bootstrap",
"columnDefs": [ {
"targets": 'no-sort',
"orderable": false,
}],
"fnRowCallback": function(nRow, aData, iDisplayIndex) {
nRow.setAttribute('id',"tr_"+aData[0]);
}
});
});
$(function() {
$(document).on('click', '.removeRcords', function(event) {
event.preventDefault();
var idss=$(this).attr('id');
var rid=idss.split("_")[1];
do_remove_ajax('broker_remove',rid);
});
});
</script>
        
3. Your controller function will go like below
public function get_broker_listing() {
$this->load->model('common');
$aColumns = array('broker_id' ,'name', 'email', 'created_date', 'status');
$sIndexColumn = "broker_id";
$sTable = "cbf_broker_registration";
$iDisplayStart=$this->input->get('iDisplayStart',true);
$iDisplayLength=$this->input->get('iDisplayLength',true);
$iSortCol_0=$this->input->get('iSortCol_0',true);
$iSortingCols=$this->input->get('iSortingCols',true);
$sLimit = "";
if ( isset($iDisplayStart) && $iDisplayLength != '-1' )
{
$sLimit = "LIMIT ". $iDisplayStart.", ".$iDisplayLength;
}
if ( isset($iSortCol_0))
{
$sOrder = "ORDER BY ";
for($i=0 ; $i<intval( $iSortingCols ) ; $i++ ){
if($_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
{
$sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
}
}
$sOrder = substr_replace( $sOrder, "", -2 );
if( $sOrder == "ORDER BY" ){
$sOrder = "";
}
}
$sSearch=$this->input->get('sSearch',true);
$sWhere = "";
if ( $sSearch != "" ){
$sWhere = "WHERE (";
for ( $i=0 ; $i<count($aColumns) ; $i++ ){
$sWhere .= $aColumns[$i]." LIKE '%".$sSearch."%' OR ";
}
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';
}
/* Individual column filtering */
for($i=0 ; $i<count($aColumns) ; $i++){
if($_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != ''){
if($sWhere == ""){
$sWhere = "WHERE ";
}else{
$sWhere .= " AND ";
}
$sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%'";
}
}
$sEcho=$_GET['sEcho'];
$result=$this->common->broker_listing($sTable, $sWhere, $sOrder, $sLimit, $aColumns,$sIndexColumn,
$sEcho);
echo json_encode($result);
}
        
4. Your model function will goes like below. you can also give edit and remove functionality for that just returning Edit Link and class " removeRcords ". you can put other links as your requirement.
function broker_listing($sTable, $sWhere, $sOrder, $sLimit, $aColumns,$sIndexColumn,$sEcho) {
$sQuery = "SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable $sWhere $sOrder $sLimit";
$rResult = $this->db->query($sQuery);
$rResult_array=$rResult->result_array();
$iFilteredTotal = count($rResult_array);
/* Total data set length */
$sQuery_TR = "SELECT COUNT(".$sIndexColumn.") AS TotalRecords FROM $sTable";
$rResult_TR = $this->db->query($sQuery_TR);
$rResult_array_TR=$rResult_TR->result_array();
$iTotal = $rResult_array_TR[0]['TotalRecords'];
$output = array("sEcho" => intval($sEcho),"iTotalRecords" => intval($iTotal),
"iTotalDisplayRecords" => intval($iTotal), //$iFilteredTotal,
"aaData" => array()
);
foreach($rResult_array as $aRow){
$row = array();
foreach($aColumns as $col){
if($aRow[$col]=='D'){
$row[] = 'Disable';
}else{
$row[] = $aRow[$col];
}
}
array_push($row, '<a href="'.base_url().'admin/broker/edit_broker/'.$aRow['broker_id'].'" 
class="editRcords btn btn-default btn-sm btn-icon icon-left"><i class="entypo-pencil"></i> 
Edit</a> <a href="javascript:void(0)" id="brkr_'.$aRow['broker_id'].'" 
class="removeRcords btn btn-danger btn-sm btn-icon icon-left">
<i class="entypo-cancel"></i> Remove</a>');
$output['aaData'][] = $row;
}
return $output;
}
        
5. You can setup jQuery function through out your project OR Application. you have to pass two param action_key and id for removing records.
function do_remove_ajax(action_key,id){
$.ajax({
url baseurl+'controller/do_remove',
type 'POST',
data{idid,action_keyaction_key},
dataType 'json',
beforeSend function() {
},
complete function() {
},
success function(response) {
switch (response.Mstatus) {
case 'success'
$("#"+response.process_id).hide();
break;
case 'error'
showalert(response.msg,'showmessage_jscbf');
break;
default
break;
}
}
});
}
        
6. Your view load function will goes like below
public function view_broker() {
$data['meta_title'] = 'Broker Listing';
$data['meta_keywords'] = 'Broker Listing';
$data['meta_desc'] = 'Broker Listing';
$data['sidebarmenu'] = 'sidebar_menu';
$data['top_menu'] = 'top_menu';
$data['main'] = 'broker_listing';
$data['footer'] = 'footer';
$this->load->vars($data);
$this->load->view($this->admin_dashboard);
}
        
NOTE: Flow goes like below sidebar menu link click--->view_broker() function call---> call ajax function get_broker_listing() ---> call model function broker_listing--->controller return json formate required for datatables.

Thursday 14 May 2015

Creating store procedure Mysql Insert

Showing you some examples of creating store procedure in mysql.
1. "At The Rate" sign used for local variable declare.
2. For calling a store procedure use function below. i am using Codeigniter.
 public function AddBooking(){
  $sql="call usp_AddBooking()";
 $parameters=array();
 $query = $this->db->query($sql,$parameters);
 return $query->result();
 }
 DROP PROCEDURE IF EXISTS `usp_AddBooking` $$
 CREATE PROCEDURE `usp_AddBooking`(
 IN iVendorId INT,
 IN iCustomerId INT, 
 IN iServiceId INT,
 IN dServiceBookingTime DATETIME,
 IN dArrivalTime DATETIME
 )
BEGIN
 DECLARE iBookingId INT default 0;
 BEGIN
 -- ERROR
 set ErrorCode = -999;
 rollback;
 END;
 DECLARE exit handler for sqlwarning
 BEGIN
 -- WARNING
 set ErrorCode = -888;
 rollback;
 END;
 START TRANSACTION;
 INSERT INTO skin_booking 
 (iVendorId 
 ,iCustomerId 
 ,iServiceId
 ,dServiceBookingTime 
 ,dArrivalTime
  ,eStatus
 ,dCreatedDate 
 ,iBookingId 
 ) 
 VALUES 
 (iVendorId 
 ,iCustomerId 
 ,iServiceId 
 ,dServiceBookingTime 
 ,dArrivalTime
 ,'1'
 ,NOW()
 ,iBookingId 
 );
 SET @iBookingId = LAST_INSERT_ID();
 SELECT @iBookingId as BookingId;
 COMMIT;
 END$$

Register users and login through store procedure mysql

1. Signup a user through store procedure.
2. "mysecret" here you can use your key. password will be encrypted using AES
3. You can call store procedure like below
CALL`AddCustomerDetails`('David Warnor','david@gmail.com','12','male','2377.34','34343.343');
CREATE PROCEDURE `AddCustomerDetails`(
 IN `vCustomerName` VARCHAR(50),
 IN `vEmail` VARCHAR(50),
 IN `vPassword` VARCHAR(50),
 IN `eGender` VARCHAR(10),
 IN `dLatitude` FLOAT,
 IN `dLongitude` FLOAT
)
BEGIN
 DECLARE ResultCount INT default 0;
 DECLARE skey VARCHAR(15) default 'mysecret';
 DECLARE eStatus INT default 1;
 DECLARE iCustomerId INT;

 SET @ResultCount = (SELECT COUNT(iCustomerId) FROM table_name WHERE vEmail = vEmail);
 IF (@ResultCount > 0) THEN
 SET @iCustomerId = 0;
 SELECT @iCustomerId;
 ELSE
 INSERT INTO table_name
 (vCustomerName
 ,vEmail
 ,vPassword
 ,eGender
 ,dLatitude
 ,dLongitude
 ,eStatus
 ,dCreatedDate
 )
 VALUES
 (vCustomerName
 ,vEmail
 ,AES_ENCRYPT(vPassword,skey)
 ,eGender
 ,dLatitude
 ,dLongitude
 ,eStatus
 ,NOW()
 );
 SET @iCustomerId = LAST_INSERT_ID();
 SELECT @iCustomerId as CustomerId;
 END IF;
END
1. Now above will add a entry to your mysql table let create a store procedure which will validate and check credentials.
ALL `CheckLogin`('david@gmail.com', '123123', '0');
CREATE PROCEDURE `CheckLogin`(
 IN `Email` VARCHAR(255),
 IN `Psw` VARCHAR(255),
 IN `UserType` INT(11)
)
BEGIN
 DECLARE ResultCount INT;
 DECLARE ResultMessage varchar(15);
 DECLARE skey VARCHAR(15) default 'secrets';
 IF (UserType = 0) THEN
 SET @ResultCount = (SELECT COUNT(C.iCustomerId) FROM customerdetails C 
 WHERE C.vEmail = Email AND C.vPassword = AES_ENCRYPT(Psw,skey) AND C.eStatus = 1);
  IF (@ResultCount > 0) THEN #For Customer
  SELECT * from customerdetails C WHERE C.vEmail = Email AND C.vPassword = AES_ENCRYPT(Psw,skey) 
  AND C.eStatus = 1;
  ELSE
 SET @ResultMessage='User not found.';
  SELECT @ResultMessage;
  END IF;
 ELSE
 SET @ResultCount = (SELECT COUNT(V.iVendorId) FROM vendor V WHERE V.vEmail = Email 
 AND V.vPassword = AES_ENCRYPT(Psw,skey) AND V.eStatus = 1);
 IF (@ResultCount > 0) THEN
  SELECT * from vendor V WHERE V.vEmail = Email AND V.vPassword = AES_ENCRYPT(Psw,skey) 
  AND V.eStatus = 1;
 ELSE
 SET @ResultMessage='vendor not found.';
  SELECT @ResultMessage;
 END IF;
 END IF;
END