Top

Follow me and receive all the latest free scripts:

By Email:

Categories
Most Popular Posts

jQuery editable grid system tutorial

jQuery editable grid system tutorial

Published September 14, 2014 by , category jQuery

jqueryphpcss

How to create an editable HTML table with jQuery

Introduction

Are you looking for a complete and simple script which allows users to make an inline editable table using jQuery? I wrote this code in a simple way, so you'll be able to adapt it for your own needs.

This fully working code sample features:

Files architecture

index.php
database_connection.php

ajax

editable_grid.php

js

editable_grid.js

jquery

jquery-1.10.1.min.js

Live demo

Please Click into a cell to edit, then press Enter to register new content in the database.

For safety reasons and to prevent misuse, datas are not inserted in the database

IdText FieldTextarea FieldCheckbox FieldCheckbox Field 1List Field
1 Name 1 Text area sample 1 0 1 Blue
2 Name 2 Address
Street
1 0 Red
3 Name 3 California
USA
0 1 Yellow

Database structure sample

This database structure sample includes a text, checkbox, list and textarea fields

CREATE TABLE IF NOT EXISTS `editable_grid` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `text_field` varchar(255) NOT NULL,
  `checkbox_field` int(3) NOT NULL DEFAULT '0',
  `checkbox_field_1` int(3) NOT NULL DEFAULT '0',
  `list_field` varchar(255) NOT NULL,
  `textarea_field` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

HTML/PHP code

I don't include the file database_connection.php where you put the database connection parameters. You must already have it.

If you need, I wrote a tutorial about how to connect database using Object-Oriented, PHP and MySQL, the kind of connection I use for this website.

Name the page as you want, index.php in my case, if I follow the files architecture.

// index.php for example
// Add a link to your jQuery.min.js file. Example:
<script type="text/javascript" src="jquery/jquery-1.10.1.min.js"></script>
// And a link to the editable_grid.js file
<script type="text/javascript" src="js/editable_grid.js"></script>
<?PHP
echo '<p id="result"></p>'; // display message sent back after cell modification, from editable_grid.php

$fields = $bdd->getAll('SELECT * FROM editable_grid'); // Select from database

if (count($fields)!=0) {
	echo '<table class="table table-striped table-bordered table-hover" id="myTable">';  // Create table
	echo '<thead>';
		echo '<th>Id</th><th>Text Field</th><th>Textarea Field</th><th>Checkbox Field</th><th>Checkbox Field 1</th><th>List Field</th>'; // Table column name
	echo '</thead>';
	echo '<tbody>';
	foreach($fields as $field) { // Generate table lines
		echo '<tr>';
		?>
			<td><?php echo $field['id']; ?></td>
			<!-- Rel attribute with Json including fields name, fields type (4 choices: input, textarea, checkbox or select), and line Id -->
			<td rel='{"name":"text_field","type":"input","id":<?php echo $field['id']; ?>}'><?php echo $field['text_field']; ?></td>
			<td rel='{"name":"textarea_field","type":"textarea","id":<?php echo $field['id']; ?>}'><?php echo nl2br($field['textarea_field']); ?></td>
			<td rel='{"name":"checkbox_field","type":"checkbox","id":<?php echo $field['id']; ?>}'><?php echo $field['checkbox_field']; ?></td>
			<td rel='{"name":"checkbox_field_1","type":"checkbox","id":<?php echo $field['id']; ?>}'><?php echo $field['checkbox_field_1']; ?></td>
			<td rel='{"name":"list_field","type":"select","id":<?php echo $field['id']; ?>}'><?php echo $field['list_field']; ?></td>
			
		<?php
		echo '</tr>';
	}
	echo '</tbody>';
	echo '</table>';
}
?>

jQuery code

You may adapt things. Cells are edited with a simple click event (you can change for a double click). New content is updated after you press Enter.

On a <textarea>, you break line by pressing Shift Enter.

// File js/editable_grid.js
$(function () {

var colors = new Array("Blue", "Red", "Green", "Yellow", "Pink"); // A color list used as an example for the List Field. May be generated via a database, in PHP...

$("#myTable td").on('click', function editField(event) { // Edit cells from #myTable on a simple click event. Click can be replaced by dblclick for a double clicks event.
	var originalContent = $(this).text(); // Cell original content
	 
	var relData = $.parseJSON($(event.target).attr('rel')); // Get Json from rel attribute
	 
	$(event.target).off("click", editField); // Disable click event when edition
	
	if (relData.type == "input") $(this).html('<input type="text" value=\"' + originalContent + '\" />'); // Generate input type field
	if (relData.type == "textarea") $(this).html('<textarea>' + originalContent + '</textarea>'); // Generate textarea type field
	if (relData.type == "checkbox") { // Generate checkbox type field
		if (originalContent == 1) var isChecked = ' checked="checked"'; else isChecked="";
		$(this).html('<input type="checkbox" value=\"' + originalContent + '\"' + isChecked + ' />');
	}
	if (relData.type == "select") { // Generate select type field
		var buildSelect = '<select>'; // Built the list with the color Array
		for (i=0; i<colors.length; i++) {
			buildSelect += '<option value="' + colors[i] + '"';
			if (colors[i] == originalContent) buildSelect += ' selected';
			buildSelect += '>' + colors[i] + '</option>';
		}
		buildSelect += '</select>';
		$(this).html(buildSelect);
	}
	
	$(this).children().first().focus(); // Put the cursor on the generated field

	$(this).children().first().keypress(function (e) { // keypress after modification
		if (e.which == 13 && !e.shiftKey) { // the keypress is Enter, and not Shift Enter (Shift Enter is kept to go on the line for the textarea field)
			var newContent = ""; // Init newContent
			if ( (relData.type == "input") || (relData.type == "textarea") || (relData.type == "select") ) newContent = $(this).val(); // Get new content value
			if (relData.type == "checkbox") {
				if ($("input[type='checkbox']").is(":checked")){
					newContent = 1;
				} else newContent = 0;
			}
			
			var data = {}; // Init data string
			data["id"] = relData.id; // Include database row ID in the data
			//...add / change name field here
			if (relData.name == "text_field") data["text_field"] = newContent; // Include type field data
			if (relData.name == "checkbox_field") data["checkbox_field"] = newContent;
			if (relData.name == "checkbox_field_1") data["checkbox_field_1"] = newContent;
			if (relData.name == "list_field") data["list_field"] = newContent;
			if (relData.name == "textarea_field") data["textarea_field"] = newContent;
			
			$.ajax({ // jQuery Ajax
				type: 'POST',
				url: 'ajax/editable_grid.php', // URL called to the PHP file which will insert new value in the database
				data: data, // We send the data string
				timeout: 3000,
				success: function(data) {
					$('#result').html(data); },
				error: function() {
					$('#result').text('Problem'); }
			});
			
			$(this).parent().text(newContent); // New content appears in the modified cell
			$(event.target).on("click", editField); // Click event is allowed again
		}
	});
	 
	$(this).children().first().blur(function() { // If you click out of the cell, no modification is done
		$(this).parent().text(originalContent); // We put back the original content
		$(event.target).on("click", editField); // Click event is allowed again
	});
});

});

PHP code

Ajax call for database update

// File ajax/editable_grid.php
<?php
include('../db.class.php'); // Call to your database connection
$bdd = new db(); // Call to your database connection

//...add / change fields name here
if (isset($_POST['text_field'])) $qString='text_field="'.strip_tags($_POST['text_field']).'"'; // If field exists...generate query SET part with column name and new cell value
if (isset($_POST['checkbox_field'])) $qString='checkbox_field='.$_POST['checkbox_field'];
if (isset($_POST['checkbox_field_1'])) $qString='checkbox_field_1='.$_POST['checkbox_field_1'];
if (isset($_POST['list_field'])) $qString='list_field="'.$_POST['list_field'].'"';
if (isset($_POST['textarea_field'])) $qString='textarea_field="'.strip_tags($_POST['textarea_field']).'"';

$query = $bdd->execute('UPDATE editable_grid SET '.$qString.' WHERE id='.$_POST['id'].''); // Update database with the correct Id (line)

echo '<div class="alert alert-success">Grid modified successfully</div>'; // Message sent back to #result <p> node
?>

About Simon Laroche
Simon Laroche on Google+
Simon Laroche on Twitter
Simon Laroche on Facebook
Simon Laroche on Pinterest
Simon Laroche on LinkedIn
: I am a Coder, Designer, Webmaster and Expert SEO Consulting, I'm also a wise traveller and an avid amateur photographer. I created the website TipoCode and many others such as Landolia: a World of Photos...

If you need help about this script, please leave a comment below. I reply as much as I can depending of my time, you may also get help from others.
I also offer a paid support, if you are in the need to adapt or create a script...

Leave a comment

Comments (5 comments)

Roni Posted on October 07, 2016
Hi can someone give me the link to jsfiddle with this example.
maatteus Posted on January 16, 2016
like this
Simon Laroche
Simon Laroche Posted on July 02, 2015
@CRONOS - With the code sample you can add as many select as you want. Just adapt the code
CRONOS Posted on July 01, 2015
How I do if I have not only one select ?
Matheus Lopes Santos Posted on January 20, 2015
Men, you only save my life... God Bless You