HTML Table Filter Generator - Editable AJAX table

This demo shows how to implement an advanced editable table by using the AJAX capabilities of the jQuery library. The filters are refreshed everytime the table content is loaded.

To make the table cells editable, you need to install the ezEditTable extension, enable the editable property and configure the ezEditTable configuration object (ezEditTable_config).

Instructions: Double-click on a cell on or press Enter key to open the cell editor.

Id	Name	Email	Start Date	Salary	Active
Loading...

Imported javascript files:

Configuration object and AJAX logic:

    // filters configuration
    var config = {
      grid_layout: true,
      grid_width: '840px',
      sort: true,
      alternate_rows: true,
      rows_counter: true,
      mark_active_columns: true,
      loader: true,
      loader_html: '',
      loader_css_class: 'myLoader',
      btn_reset: true,
      status_bar: true,
      col_1: 'select',
      col_2: 'select',
      col_5: 'select',
      remember_grid_values: true,
      remember_page_number: true,
      col_width: ['50px', '270px', '210px', '80px', '80px', '60px'],
      paging: true,
      paging_length: 15,
      on_filters_loaded: function(o){
        if(o.paging){ tf_Id('paging').checked = true; }
      },

      /* Custom data delegate for Active column checkbox */
      custom_cell_data_cols: [5],
  		custom_cell_data: function(o, c, i){
  			if(i === 5){
  				var chk = c.getElementsByTagName('input')[0];
  				if(chk.checked) return 'yes';
  				else return 'no';
  			}
  		},

  		/* Sky Blue theme */
      themes: {
          name:['SkyBlueTheme'],
          src:['TableFilter/TF_Themes/SkyBlue/TF_SkyBlue.css'],
          description:['SkyBlue stylesheet']
      },

  		//Column resize feature
  		extensions: {
  			name:['ColumnsResizer'],
  			src:['TableFilter/TFExt_ColsResizer/TFExt_ColsResizer.js'],
  			description:['Columns Resizing'],
  			initialize:[function(o){o.SetColsResizer();}]
  		},
  		col_resizer_all_cells: true,

      // selection and edition require ezEditTable extension
  		selectable: true,
  		editable: true,

  		// ezEditTable extension configuration
  		ezEditTable_config: {
          loadStylesheet: true,
  		    default_selection: 'both',
  		    editor_model: 'cell',
  		    cell_editors: [
                  { type: 'none' },
                  { type: 'input', attributes: [['title', 'First name and last name']] },
                  { type: 'input', attributes: [['title', 'email address']] },
                  { type: 'none' },
                  { type: 'input', attributes: [['maxLength', 10], ['title', '10 digits max.\n numbers with 2 decimal places only']], css: 'alignRight' },
                  { type: 'boolean' }
  		    ],
  		    actions: {
  		        'update': {
  		            uri: 'php/employeeUpdate.php', submit_method: 'form', form_method: 'POST',
  		            param_names: ['id', 'name', 'email', 'startdate', 'salary', 'active'],
  		            on_after_submit: function (o) {
  		                ajax.isRefreshed = false;
  		                // selected row index is stored
  		                ajax.activeRowIndex = o.Selection.GetActiveRow().rowIndex;
                      // rows are loaded
                      ajax.loadContent();
  		            }
  		        },
  		        'insert': {
  		            uri: 'php/employeeInsert.php', submit_method: 'form', form_method: 'POST',
  		            param_names: ['id', 'name', 'email', 'startdate', 'salary', 'active'],
  		            default_record: ['', 'Employee name...', 'employee@email.com', '2011-01-01', '0.00', ''],
  		            on_after_submit: function (o) {
  		                ajax.isRefreshed = false;
  		                // selected row index is stored
  		                ajax.activeRowIndex = o.Selection.GetActiveRow().rowIndex;
                      // rows are loaded
                      ajax.loadContent();
  		            }
  		        },
  		        'delete': {
  		            uri: 'php/script.deleteEmployee.php', submit_method: 'script', bulk_delete: false,
  		            param_names: ['id'],
  		            on_after_submit: function (o) {
  		                ajax.isRefreshed = false;
                      // rows are loaded
                      ajax.loadContent();
  		            }
  		        }
  		    },

          //Callback triggered when a new row element is added
  		    on_added_dom_row: function (o) {
  		        //TF total rows nb needs to be re-calculated
  		        tf.nbRows = tf.GetRowsNb();
  		    },

  		    //Data validation delegate, the ezEditTable script does not provide data validation tools
  		    validate_modified_value: function (o, colIndex, oldVal, newVal, cell, editor) {
  		        if (colIndex == 2 && oldVal != newVal) {
  		            if (!et_ValidateEmail(newVal)) {
  		                alert('Please insert a valid email!');
  		                return false;
  		            } else return true;
  		        }
  		        else if (colIndex == 4 && oldVal != newVal) {
  		            if (!et_IsNumber(newVal)) {
  		                alert('Please insert a valid number with 2 decimal places!');
  		                return false;
  		            } else return true;
  		        }
  		        else return true;
  		    }
  		}
	}

  //jQuery AJAX
  var ajax = {
      isRefreshed: false,
      loadContent: function () {
          if (this.isRefreshed) { return; }
          // loading message
          $('#loadLabel').html('Loading data...');
          // AJAX call
          this.load('php/getEmployees.php');
      },
      load: function(uri){
          var xhr = $.ajax({
              url: uri + '?r='+ new Date().getTime(),
              context: $('#demo tbody'),
              type: 'GET',
              dataType: 'html'
           })
          .done(function (data) {
              // inject html table rows
              this.html(data);
              // loading message is cleared
              $('#loadLabel').html('');

              // filters need to be refreshed after
              // table content is loaded
              if (tf) {
                  // temp hack to make the refresh filters feature
                  // work correctly when paging is on. This will be
                  // fixed in next release
                  if(tf.paging){ tf.paging = false; }
                  // total rows number is recalculated
                  tf.nbRows = tf.GetRowsNb();
                  // filters are refreshed
                  tf.RefreshFiltersGrid();
                  // paging is reset if on
                  if(!tf.paging && tf_Id('paging').checked){
                    tf.RemovePaging();
                    tf.paging = true;
                    tf.SetPaging();
                  }
                  // reapply alternating backgrounds
                  tf.SetAlternateRows();

                  // ezEditTable created, modified, deleted rows arrays are reset
                  tf.ezEditTable.Editable.modifiedRows = [];
                  tf.ezEditTable.Editable.addedRows = [];
                  tf.ezEditTable.Editable.deletedRows = [];
                  //Selection is reapplied if any
                  if(ajax.activeRowIndex){
                    tf.ezEditTable.Selection.SelectRowByIndex(ajax.activeRowIndex);
                  }
                  //Page is also reselected
                  if(tf.paging && tf.currentPageNb > 0){
                    tf.SetPage(tf.currentPageNb);
                  }
                  ajax.isRefreshed = true;
              } else {
                  // first time rows are loaded filters are instantiated
                  tf = new TF('demo', config);
                  tf.Init();
              }
          })
          .fail(function(jqXHR, textStatus){
              $('#loadLabel').html(textStatus);
              this.html('Ouups an error occured! Data could not be loaded!');
          });
      }
    };

    //Validation functions for cell editors
    function et_ValidateEmail(email) {
        var reg = /^([A-Za-z0-9_\-\.])+\@([A-Za-z0-9_\-\.])+\.([A-Za-z]{2,4})$/;
        return reg.test(email);
    }

    function et_IsNumber(val) {
        var isNumber = /^\d+\.\d{2}$/;
        return isNumber.test(val);
    }

    var tf; //global TF instance

    $(function(){
      // table rows are loaded when page is ready
      ajax.loadContent();
    });

PHP page serving the HTML table rows:

	connect_db();
	$query = mysql_query("SELECT ID, NAME, EMAIL, STARTDATE, SALARY, ACTIVE".
		" FROM ".TBL_DEMO_EMPLOYEE." WHERE DELETED = 0".
		" ORDER BY ID DESC LIMIT 0, 30");

	while($row = mysql_fetch_row($query)){
		$id =  $row[0];
		$name = $row[1];
		$email = $row[2];
		$startdate = $row[3];
		$salary =  $row[4];
		$active =  $row[5];
		$checked = "";
		if($active == 1) $checked = "checked=\"checked\"";

		echo("\n\t");
		echo("".$id."\n\t");
		echo("".$name."\n\t");
		echo("".$email."\n\t");
		echo("".$startdate."\n\t");
		echo("".$salary."\n\t");
		echo("\n");
		echo("\n\t" );
	}

	close_db();

HTML Table Filter Generator: editable AJAX table

Imported javascript files:

Configuration object and AJAX logic:

PHP page serving the HTML table rows:

Insert, update and delete operations are served by the same server-side pages detailed in this demo: editable-grid.php