Datagrid editing Archives | phpGrid - PHP Datagrid Create PHP grids in minutes, not hours. Sat, 30 Nov 2024 00:08:19 +0000 en-US hourly 1 CodeProjecthttps://wordpress.org/?v=7.0.3 129966043 CELL edit with Add, Delete and Export buttons https://phpgrid.com/example/cell-edit-with-add-new-and-delete-buttons/ Tue, 21 May 2019 22:55:07 +0000 http://phpgrid.com/?p=9524 phpGrid has a less known edit feature called “CELL”. It’s similar to INLINE edit, but with only a single editable cell when selected. This is not a fully-fledged feature from jqGrid itself, especially it missed add and delete out of the box. However, we have hacked (but works) Javascript to included the add and delete […]

The post CELL edit with Add, Delete and Export buttons appeared first on phpGrid - PHP Datagrid.

]]> phpGrid has a less known edit feature called “CELL”. It’s similar to INLINE edit, but with only a single editable cell when selected. This is not a fully-fledged feature from jqGrid itself, especially it missed add and delete out of the box. However, we have hacked (but works) Javascript to included the add and delete functions through custom javascript.

Pay close attention to the “url” property. Be sure it points to a valid edit.php address.

1
2
3
4
5
6
7
8
// Always include namespace and conf.php on TOP of the script.
use phpCtrl\C_DataGrid;
require_once("/file/path/to/conf.php");  


$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");
$dg->enable_edit('CELL');
$dg -> display();

ADD & DELETE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
$(function() {
   
    $('#addNew').on('click', function(){
        $("#orders").jqGrid('editGridRow', "new", {url:'/phpGrid/edit.php?dt=json&gn=orders&oper=add'} );
    });

    $('#delRow').on('click', function(){
        var selRowId = $("#orders").jqGrid('getGridParam', 'selrow');
        $.ajax({
                url: '/phpGrid/edit.php?dt=json&gn=orders',
                data: {'id': selRowId, 'oper': 'del'},
                type: 'POST',
                dataType: 'JSON'
        });
        $('#orders').trigger( 'reloadGrid' );
    });
})
<button id="addNew">Add New</button>
<button id="delRow">Delete Selected</button>

See Live Example!

EXPORT (new)

To add Export buttons to the bottom toolbar, insert the following BEFORE $dg->$display(); Be sure to replace /phpGrid/ with the actual phpGrid absolute file path on your web server

1
2
3
4
5
6
7
8
9
10
11
12
$exportDropdown =<<< EXPORTDROPDOWN
$('#orders_pager1_left').append (`<div style=padding-right: 16px;>Export:
    <select onchange="document.location.href=this.options[this.selectedIndex].value;">
        <option>---</option>
        <option value='/phpGridx/export.php?gn=orders&export_type=excel'>Excel</option>
        <option value='/phpGridx/export.php?gn=orders&export_type=pdf'>PDF</option>
        <option value='/phpGridx/export.php?gn=orders&export_type=html'>HTML</option>
        <option value='/phpGridx/export.php?gn=orders&export_type=csv'>CSV</option>
        <option value='/phpGridx/export.php?gn=orders&export_type=excelxml'>ExcelXML</option>
    </select></div>`);
EXPORTDROPDOWN;
$dg->before_script_end = $exportDropdown;

The post CELL edit with Add, Delete and Export buttons appeared first on phpGrid - PHP Datagrid.

]]> 9524 Excel Editing https://phpgrid.com/example/excel-editing/ Fri, 17 Nov 2017 05:40:15 +0000 https://phpgrid.com/?p=9386 Set the edit type to CELL enables datagrid to behave like an Excel spreadsheet. It is advisable to also enable keyboard navigation with enable_kb_nav(). * Use keyboard arrow keys to navigate the table cell like Excel. * Press Enter key to edit a cell. Enter again to save. * While editing, press Tab key to […]

The post Excel Editing appeared first on phpGrid - PHP Datagrid.

]]> Set the edit type to CELL enables datagrid to behave like an Excel spreadsheet. It is advisable to also enable keyboard navigation with enable_kb_nav().

  • * Use keyboard arrow keys to navigate the table cell like Excel.
  • * Press Enter key to edit a cell. Enter again to save.
  • * While editing, press Tab key to move the next adjacent cell. Any changes are automatically saved.
1
2
3
4
5
6
7
8
9
10
11
// Always include namespace and conf.php on TOP of the script.
use phpCtrl\C_DataGrid;
require_once("/file/path/to/conf.php");  

$dg = new C_DataGrid("SELECT * FROM orders", "orderNumber", "orders");
$dg->enable_autowidth(true)->enable_autoheight(true);
$dg->set_pagesize(100); // needs to be a large number
$dg->set_scroll(true);
$dg->enable_kb_nav(true);
$dg->enable_edit('CELL');
$dg -> display();


Live Demo!

The post Excel Editing appeared first on phpGrid - PHP Datagrid.

]]> 9386 Tagging with autocomplete https://phpgrid.com/example/tagging-with-autocomplete/ Mon, 02 Oct 2017 04:19:30 +0000 https://phpgrid.com/?p=9375

To enable tagging, you can set the type of grid edit control to autocomplete with set_col_edittype(), and set the LAST parameter to true. In the demo below, edit Office Code in the pop-up form to see the tagging feature. 123456789// Always include namespace and conf.php on TOP of the script. use phpCtrl\C_DataGrid; require_once("/file/path/to/conf.php");   $dg […]

The post Tagging with autocomplete appeared first on phpGrid - PHP Datagrid.

]]>

To enable tagging, you can set the type of grid edit control to autocomplete with set_col_edittype(), and set the LAST parameter to true.

In the demo below, edit Office Code in the pop-up form to see the tagging feature.

1
2
3
4
5
6
7
8
9
// Always include namespace and conf.php on TOP of the script.
use phpCtrl\C_DataGrid;
require_once("/file/path/to/conf.php");  

$dg = new C_DataGrid("select employeeNumber, lastName, firstName, isActive, officeCode, extension from employees", "employeeNumber", "employees");
$dg -> enable_edit("FORM", "CRUD");
$dg -> set_col_edittype("officeCode", "autocomplete", "Select officeCode,city from offices",true);
$dg -> enable_search(true);
$dg -> display();

Live Demo

The post Tagging with autocomplete appeared first on phpGrid - PHP Datagrid.

]]> 9375 Show Remaining Characters in Textarea/Input https://phpgrid.com/example/show-remaining-characters-textareainput/ Tue, 25 Apr 2017 03:54:50 +0000 http://phpgrid.com/?p=9282

In a recent user support request, the user would like to see the remaining characters allowed for data input in a text input. This is not a phpGrid standard feature. How can it be achieved? Custom event comes to rescue! First of all, we must insert a new html node for our counter right after […]

The post Show Remaining Characters in Textarea/Input appeared first on phpGrid - PHP Datagrid.

]]>

In a recent user support request, the user would like to see the remaining characters allowed for data input in a text input. This is not a phpGrid standard feature. How can it be achieved?

Custom event comes to rescue!

First of all, we must insert a new html node for our counter right after the text input. In our demo, we use the “status” field. The new counter will display the text of remaining allowed characters for data entry.

1
<span id='counter'></span>

We bind a new “onkeyup” JavaScript event to the “status” text input. The phpGrid event you should use is “jqGridAddEditAfterShowForm” so that the counter is only displayed AFTER the form is shown.

1
2
3
4
5
6
7
8
$afterShowForm = <<<AFTERSHOWFORM
function ()
{
    $("#tr_status > td.CaptionTD + td.DataTD").append("<span id='counter'></span>");   
    $("#status").attr("onkeyup","updateCounter()");
}
AFTERSHOWFORM;
$dg->add_event('jqGridAddEditAfterShowForm', $afterShowForm);

We use the CSS selector to style our counter. Most importantly, we make sure it is displayed in the right place (at the end of the text input box).

1
2
3
4
5
6
7
8
9
/* move the counter atop */
#counter{
    font-size: 8px;
    position: relative;
    top: -15px;
    float: right;
    padding-right: 25px;
    display:inline-block;
}

Lastly, insert the javascript function below that updates our counter during each key press.

1
2
3
4
5
6
7
8
// show how much characters in "status" field input. Max is 10
function updateCounter() {
  var maxChar = 10;
  var pCount = 0;
  var pVal = $("#tr_status > td.CaptionTD + td.DataTD  input").val();
  pCount = pVal.length;
  $("#counter").text(pCount + '/' + maxChar);
}

Run Live Demo! (Click “Status” input box, and start typing)

The post Show Remaining Characters in Textarea/Input appeared first on phpGrid - PHP Datagrid.

]]> 9282 Drag & Drop Rows Between Grids https://phpgrid.com/example/drag-drop-rows-grids/ Wed, 22 Mar 2017 06:58:32 +0000 http://phpgrid.com/?p=9270 You can drag and drop rows between two or more grids using a mouse. In the demo, we have both orders and employees datagrids on the same page. 123456use phpCtrl\C_DataGrid; require_once("/file/path/to/conf.php");   $dg = new C_DataGrid("select * from employees", "employeeNumber", "employees"); $dg->enable_edit("FORM", "CRUD"); $dg->display(); We add the following Javascript to enable drag and drop between […]

The post Drag & Drop Rows Between Grids appeared first on phpGrid - PHP Datagrid.

]]> dnd-phpgrid

You can drag and drop rows between two or more grids using a mouse.

In the demo, we have both orders and employees datagrids on the same page.

1
2
3
4
5
6
use phpCtrl\C_DataGrid;
require_once("/file/path/to/conf.php");  

$dg = new C_DataGrid("select * from employees", "employeeNumber", "employees");
$dg->enable_edit("FORM", "CRUD");
$dg->display();

We add the following Javascript to enable drag and drop between the two php grids.

The Ondrop event posts the row data to another url “save_dropped_row.php”. You must implement this server side script to handle how you would like the row data to be saved.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<script>
$(function(){
  jQuery("#orders").jqGrid("sortableRows", {});
  jQuery("#employees").jqGrid("sortableRows", {});

  jQuery("#orders").jqGrid('gridDnD',{
    connectWith:'#employees',
    drop_opts:{
      hoverClass: "ui-state-active",
    },
    ondrop: function(evt, obj, data){
      $.ajax({
          method: "POST",
          url: "save_dropped_row.php",
          data: data,
        }).done(function( msg ) {
            console.log( "Data Saved: " + msg );
        })
    },

  });  
})
</script>

Complete Drag and drog API documentation can be found on jQuery UI draggable and droppable widgets.

Run Live Demo!

The post Drag & Drop Rows Between Grids appeared first on phpGrid - PHP Datagrid.

]]> 9270 Bulk Edit, Select Multiple Rows https://phpgrid.com/example/select-multiple-records/ Fri, 17 Feb 2017 01:07:22 +0000 http://64.38.236.7/?p=549

You can select multiple records with set_multiselect() method. When multiselect is enabled, a checkbox is shown to the left of each row. Use multiselect feature to for bulk edit such as delete multiple records and save multiple selected rows. Version 6.7.10 Update Set the 2nd parameter in set_multiselect() to true to persist row selection following […]

The post Bulk Edit, Select Multiple Rows appeared first on phpGrid - PHP Datagrid.

]]>

You can select multiple records with set_multiselect() method. When multiselect is enabled, a checkbox is shown to the left of each row. Use multiselect feature to for bulk edit such as delete multiple records and save multiple selected rows.

Version 6.7.10 Update

Set the 2nd parameter in set_multiselect() to true to persist row selection following pagination. Before the selection is lost after going to another page.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// Always include namespace and conf.php on TOP of the script.
use phpCtrl\C_DataGrid;
require_once("/file/path/to/conf.php");  

$dg = new C_DataGrid("SELECT * FROM Orders", "orderNumber", "Orders");

// change column titles
$dg -> set_col_title("orderNumber", "Order No.");
$dg -> set_col_title("orderDate", "Order Date");
$dg -> set_col_title("shippedDate", "Shipped Date");
$dg -> set_col_title("customerNumber", "Customer No.");
 
// enable edit
$dg -> enable_edit("INLINE", "CRUD");

// hide a column
$dg -> set_col_hidden("requiredDate");

// read only columns, one or more columns delimited by comma
$dg -> set_col_readonly("orderDate, customerNumber");

// required fields
$dg -> set_col_required("orderNumber, customerNumber");

// multiple selection. The 2nd true signals grid not lose selections following pagination
$dg -> set_multiselect(true, true);
 
$dg -> display();

Bonus: send selected rows back to server

We can also easily obtain the selected row information and send captured rows back to server side for additional processing via AJAX. Below we got some working code snippets so you can get jump started. Check out the live example to see them in action!

Retrieve selected rows (Javascript)

1
2
3
4
5
function ShowSelectedRows(){
    var rows = getSelRows();
    // replace your own javascript here
    alert(rows);
}

Retrieve selected row ids and posted to a remote URL via $.ajax in JSON format (Javascript)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
function saveSelectedRowIds() {
    var rows = getSelRows();
    if (rows == "") {
        alert("no rows selected");
        return;
    } else {
        $.ajax({
          url: 'http://example.com/save_selected_rowids.php',
          data: {selectedRows: rows},
          type: 'POST',
          dataType: 'JSON'
        });
        alert(rows + ' row Ids were posted to a remote URL via $.ajax');
    }
    // window.location = "index.php#ajax/save_selected_row.php?refresh=1&rows="+rows;
}

Retrieve selected rows objects and posted to a remote URL via $.ajax in JSON format (Javascript)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
function saveSelectedRows(){
    gdata = $('#orders').jqGrid('getRowData');
    rows = getSelRows();
    if (rows == "") {
        alert("no rows selected");
        return;
    } else {
        selIndices = [];  // get index selected
        $.each(gdata, function(index, value){
            if($.inArray(value["orderNumber"], rows) != -1){
                selIndices.push(index);
            }
        });
        selRows = [];   // get row object from each index selected
        $.each(gdata, function(index, value){
            if($.inArray(index, selIndices) != -1){
                selRows.push(gdata[index]);
            }
        })
        $.ajax({
          url: 'http://example.com/save_selected_rows.php',
          data: {selectedRows: selRows},
          type: 'POST',
          dataType: 'JSON'
        });
        alert(selRows + ' with row ids ' + rows + ' were posted to a remote URL via $.ajax');
    }
}

Bonus 2: Code Snippets

To obtain row id of a SINGLE selected row

1
var row_id = $('#TABLE_NAME').jqGrid ('getGridParam', 'selrow')

Get ALL selected rows in an array (with multiple select enabled)

1
var rows = jQuery("#TABLE_NAME").jqGrid("getGridParam","selarrrow");

To get value from a cell of the currently selected row:

1
var cell_value = $('#TABLE_NAME').jqGrid('getCell',row_id,'COLUMN NAME');

See Live Example!

The post Bulk Edit, Select Multiple Rows appeared first on phpGrid - PHP Datagrid.

]]> 549 Enhance Edit Form https://phpgrid.com/example/enhance-form-group-header-tooltips/ Fri, 14 Oct 2016 00:33:58 +0000 http://phpgrid.com/?p=9045

There are many ways to enhance the edit form in regular datagrid + form, or just in form-only mode. In version 7.0, there are two new functions at your disposal, add_form_group_header() and add_form_tooltip(). Each provides a simple way to include additional text to annotate the form. In form only mode, the form will remain on […]

The post Enhance Edit Form appeared first on phpGrid - PHP Datagrid.

]]>

There are many ways to enhance the edit form in regular datagrid + form, or just in form-only mode. In version 7.0, there are two new functions at your disposal, add_form_group_header() and add_form_tooltip(). Each provides a simple way to include additional text to annotate the form.

In form only mode, the form will remain on the screen after each submit.

1
2
3
4
$dg->enable_edit('FORM')->form_only()
  ->add_form_group_header('employeeNumber', 'Employee Details')
  ->add_form_group_header('email', 'Other Info')
  ->add_form_tooltip('email', 'Got mail?');

Live demo!

The post Enhance Edit Form appeared first on phpGrid - PHP Datagrid.

]]> 9045 WYSIWYG editor with font colorpicker https://phpgrid.com/example/wysiwyg/ Tue, 30 Aug 2016 03:16:57 +0000 http://phpgrid.com/?p=9020 Font colorpicker is now built right into the phpGrid Wysiwyg editor for both inline and form edit mode. By default, a text field is a simple plain textarea. Call set_col_wysiwyg() to enable the Wysiwyg feature. Note that the field must be a text data type. You can use set_col_edittype() function to change the edit type […]

The post WYSIWYG editor with font colorpicker appeared first on phpGrid - PHP Datagrid.

]]>

Font colorpicker is now built right into the phpGrid Wysiwyg editor for both inline and form edit mode. By default, a text field is a simple plain textarea. Call set_col_wysiwyg() to enable the Wysiwyg feature.

Note that the field must be a text data type. You can use set_col_edittype() function to change the edit type to “textarea” first.

1
$dg -> set_col_wysiwyg('comments');

Run Demo

The post WYSIWYG editor with font colorpicker appeared first on phpGrid - PHP Datagrid.

]]> 9020 Copy Row https://phpgrid.com/example/copy-row/ Sat, 28 May 2016 09:12:48 +0000 http://phpgrid.com/?p=8990   You can now clone a row by simply calling “enable_copyrow()” function. Once it’s enabled, select a row and then in “Copy Row” button in the footer. It will duplicate the selected row in the database table and even return the auto-incremented ID. 123// Always include namespace and conf.php on TOP of the script. use […]

The post Copy Row appeared first on phpGrid - PHP Datagrid.

]]> phpgrid_copyrow

 

You can now clone a row by simply calling “enable_copyrow()” function. Once it’s enabled, select a row and then in “Copy Row” button in the footer. It will duplicate the selected row in the database table and even return the auto-incremented ID.

1
2
3
// Always include namespace and conf.php on TOP of the script.
use phpCtrl\C_DataGrid;
require_once("/file/path/to/conf.php");

Note that this feature requires the primary key is an auto-increment type.

1
2
3
4
$dg = new C_DataGrid("SELECT `orderNumber`, `orderDate`, `requiredDate`, `shippedDate`, `status`, `comments`, `customerNumber` FROM `orders`", "`orderNumber`", "orders");
$dg -> enable_edit("FORM", "CRUD");
$dg -> enable_copyrow(true);
$dg -> display();

Launch Copy Row Demo!

The post Copy Row appeared first on phpGrid - PHP Datagrid.

]]> 8990 Working with Complex Query https://phpgrid.com/example/complex-query/ Tue, 12 Apr 2016 13:27:16 +0000 http://phpgrid.com/?p=8796 Keep in mind that any direct editing is always limited to the primary table. For example, in the following complex query with joins, it’s possible to edit the primary table “suppliers“. however, any updates to table “supplierproductlines” and “products” will be ignored. It’s recommended to set any non-primary table fields to readonly to avoid any […]

The post Working with Complex Query appeared first on phpGrid - PHP Datagrid.

]]> Keep in mind that any direct editing is always limited to the primary table. For example, in the following complex query with joins, it’s possible to edit the primary table “suppliers“. however, any updates to table “supplierproductlines” and “products” will be ignored. It’s recommended to set any non-primary table fields to readonly to avoid any confusion from your application end-users.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
// Always include namespace and conf.php on TOP of the script.
use phpCtrl\C_DataGrid;
use phpCtrl\C_DataBase;
require_once("/file/path/to/conf.php");  

$sql = 'select
        s.supplierCode, s.supplierZip, s.supplierPhonenumber,
        spl.productLineNo, spl.productLine,
        p.productName, p.MSRP
        from suppliers s
        inner join supplierproductlines spl on s.supplierName = spl.supplierName
        inner join products p on s.supplierZip = p.supplierZip';
$dg = new C_DataGrid($sql, "supplierCode", "suppliers");
$dg->enable_edit('INLINE');
$dg->set_col_readonly("productLineNo, productLine, productName, MSRP");
$dg->display();

The followings are steps to edit complex query based datagrid using the array. The application developer must provide his own implementation of “save_local_array.php”.

1. Generate PHP array from your query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
$sql = 'select
        s.supplierCode, s.supplierZip, s.supplierPhonenumber,
        spl.productLineNo, spl.productLine,
        p.productName, p.MSRP
        from suppliers s
        inner join supplierproductlines spl on s.supplierName = spl.supplierName
        inner join products p on s.supplierZip = p.supplierZip';

$db = new C_DataBase(PHPGRID_DB_HOSTNAME, PHPGRID_DB_USERNAME, PHPGRID_DB_PASSWORD, PHPGRID_DB_NAME, PHPGRID_DB_TYPE,PHPGRID_DB_CHARSET);

$results = $db->db_query($sql);
$data1 = array();
$count = 0;
while($row = $db->fetch_array_assoc($results)) {
 $data_row = array();
    for($i = 0; $i < $db->num_fields($results); $i++) {
        $col_name = $db->field_name($results, $i);
        $data1[$count][$col_name] = $row[$col_name];
    }
    $count++;
}

2. Pass above generated array “data1” to phpGrid.

1
2
3
$dg = new C_DataGrid($data1, "id", "data1");
$dg->enable_edit('INLINE');
$dg->display();

Optional: Additional code snippet to submit data back to server. Users must provide their own save routine.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
<script src="http://malsup.github.com/jquery.form.js"></script>
<form id="admin_form">
    <div>
        <input type="submit" value="Submit Local Changes">
    </div>
</form>

<script>
    $(function() {
        // bind to the form's submit event
        $('#admin_form').submit(function(event) {
            $(this).ajaxSubmit({
                type: 'post',
                dataType:'json',
                url:'save_local_array.php',
                data:{
                    langArray:[] //leave as empty array here
                },
                beforeSubmit: function(arr, $form, options){
                    options.langArray = $('#data1').jqGrid('getRowData'); // get most current
                    console.log(JSON.stringify(options.langArray));
                    // return false; // here to prevent submit
                },
                success: function(){
                    // add routine here when success
                }
            });

            // return false to prevent normal browser submit and page navigation
            return false;
        });
    });
</script>

The post Working with Complex Query appeared first on phpGrid - PHP Datagrid.

]]> 8796