Grid edit in place with jQuery and Ajax
Update: There is a new much simpler series using jQuery/Ajax to create a grid-like CRUD interface, if the below is too much that one is a much better place to start.
This tutorial is about mimicking an Excel style interface that hooks into a database through Ajax. Most of the logic that creates the richness of the application is handled with jQuery.
The interface is a part of Dive Admin and can be seen in the promotional video on the homepage.
The reason for creating this interface is that the target group for the software is currently using Excel for pretty much everything they do. Having this replacement look as much as Excel as possible will ease the transition considerably.
First two disclaimers:
– The way things are done in this tutorial is probably not best practices, I’m sure a lot of jQuery gurus out there could have a lot to say about a lot of what is being done here, you are all welcome to do so 🙂
– The code and markup in this tutorial might not work with IE because the application is being developed for a relatively small group of people who are being forced to use Firefox. Actually most feel disgust at having used IE for so long when I show them that Firefox is so much more than just a browser, it won’t be long before ff can wipe your arse if need be. In fact, some Japanese nerd might already have created an addon that enables his personal robot to do just that. The ones who still don’t get it are being brutally beaten into submission.
Furthermore, the way id and class names work is certainly not optimized, they were just picked and extended on demand which is not good at all, better planning in this area could probably have resulted in a much slicker code. However if you do find yourself in a totally screwed situation and you need to know how you can solve various selection problems with jQuery then this tutorial is for you.
So to cap this intro: The biggest value here is that you might find a jQuery gold nugget or two, and an example of how NOT to do many things. That is what I walked away with anyway after completing this monster. The value of planning “simple” client side stuff should not be underestimated. If you find yourself using the more arcane of jQuery’s selectors – well then you might be doing something wrong, something horribly wrong.
Let’s start with the HTML (rendered with the Smarty templating engine):
<div id="customer_search" class="hidden_simple">
<table>
<tr class="tr_color1">
<td width="375" align="center">
<input id="customer_search_input" type="text" class="standard_box"/>
</td>
<td width="25">
<img src="images/delete_small.png" onclick="hideSearch()"/>
</td>
</tr>
<tr class="tr_color2">
<td colspan="2">
<div id="customer_result"></div>
</td>
</tr>
</table>
</div>
This is a “popup” to select pre existing customers, after they are selected their current values will prepopulate the sheet as much as possible.
Let’s move down:
<div id="workspace">
<table>
<tr class="tr_headline">
<td> </td>
{if empty($area_select) eq false}
<td>{#area#}</td>
{/if}
<td>{#product#}</td>
<td> </td>
{foreach from=$labels item=label}
<td>
{$label}
</td>
{/foreach}
</tr>
{foreach from=$work key=line_num item=work_row}
<tr class="{cycle values="tr_color1, tr_color2"} trrow">
<td>
{if $work_row.customer_id eq ""}
<div id="delete-{$line_num}" class="hidden_simple delete_button">
{else}
<div id="delete-{$line_num}" class="delete_button">
{/if}
<img src="images/delete_small18.png"/>
</div>
</td>
{if empty($area_select) eq false}
<td class="td-area-{$line_num}">
<div id="div-area-{$line_num}">
{html_options id="area-$line_num" name="area_select" options=$area_select selected=$work_row.area class="workspace_box area_select"}
</div>
</td>
{/if}
<td class="td-product-{$line_num}">
<div id="div-product-{$line_num}">
{if $work_row.product eq ""}
{html_options id="product-$line_num" name="product_select" options=$product_select class="workspace_box product_select"}
{else}
{$work_row.product}
{/if}
</div>
</td>
<td>
{if $work_row.customer_id eq ""}
<div id="manage-{$line_num}" class="hidden_simple manage_button">
{else}
<div id="manage-{$line_num}" class="manage_button">
{/if}
<img src="images/select_small18.png"/>
</div>
</td>
{foreach from=$work_row.data item=work_item}
<td>
{if $work_row.customer_id eq ""}
<div id="{$work_item.table}-{$work_item.cell_id}" class="div-{$line_num}">
{else}
<div id="{$work_item.table}-{$work_item.cell_id}-{$work_row.customer_id}" class="div-{$line_num}">
{/if}
{if $work_item.table eq "db_customer"}
{if $work_row.customer_id eq "" && $work_item.origin eq "yes"}
{include file="workspace_customer_input.tpl" line_num=$line_num cell_id=$work_item.cell_id}
{else}
{$work_item.value}
{/if}
{else}
{if $work_item.table eq "line_num"}
{$line_num}
{elseif empty($work_item.value)}
{else}
{$work_item.value}
{/if}
{/if}
</div>
</td>
{/foreach}
</tr>
{/foreach}
</table>
</div>
As you can see there are basically two areas (horizontally), one with preset values necessary for creating the proper server side results, at the moment they are Area and Product. The area can be switched to change the contents of the hotel drop down, if a hotel column exists. A product is required to be chosen in order for the server side logic to be able to generate a proper purchase when a customer is selected/created, more on that later. We also have two icons, a delete button and a select button. When the delete button is pressed the whole row is reseted and the purchase is removed from the database. When the select button is pressed we will open up another interface in a new tab where we can edit a whole lot more customer specific information but that is another story…
As you also can see we control the content of different tables at once, it all depends on the settings in a massive and ugly server side config file. Note the auto fill of three spaces, this is a totally ugly and lazy hack to account for the fact that if a div is completely empty it wont have an active area that can be clicked.
<br>
<input id="save_workspace" type="button" value="{#save_workspace#}" class="standard_button save_workspace">
{/if}
<div id="product_select_template" class="hidden_simple">
{html_options id="" name="product_select" options=$product_select class="workspace_box product_select"}
</div>
<div id="customer_input_template" class="hidden_simple">
{include file="workspace_customer_input.tpl"}
</div>
<div id="config" class="hidden_simple">
{foreach from=$work key=line_num item=work_row}
<div id="config-{$line_num}">
<div id="pur-{$line_num}">{$work_row.purchase}</div>
{foreach from=$work_row.data item=work_item}
<div id="conf-{$work_item.cell_id}">
{foreach from=$work_item key=conf_key item=conf_value}
<div class="{$conf_key}">{$conf_value}</div>
{/foreach}
</div>
{/foreach}
</div>
{/foreach}
</div>
<div id="templates" class="hidden_simple">
{foreach from=$templates item=template}
{$template}
{/foreach}
</div>
Here we draw a lot of content that can be traversed by jQuery in order to copy paste stuff here and there. Madness or pure genius? Hint: I don’t feel very clever about it… Anyway, it allows each cell in the sheet to have it’s own customized settings when communicating with the server. It also allows us to totally self contain this interface, there is absolutely no information at all being stored in the session.
The user can, and is encouraged to work with as many worksheets as she wants simultaneously. Without this setup I would’ve had to set up some scheme to keep track of each work sheet’s session data, plus possibly contaminating the id/class space even more. Anyway, enough thinking, what’s done is done! Let’s move on to the javascript:
<SCRIPT src="js/jquery.js"></SCRIPT>
<SCRIPT src="js/jspanserializer.js"></SCRIPT>
<script>
var curDate = '{$cur_date}';
var curFacility = '{$cur_facility}';
var curType = '{$cur_type}';
{literal}
var baseUrl = 'route.php?c='+curType+'workspace&f=';
function empty(val){
switch(typeof val){
case'string':
if(val.length != 0)
return false;
break;
case'number':
if(val != 0)
return false;
break;
case'boolean':
if(val == true)
return false;
break;
case'object':
if(val['length'] != 0)
return false;
break;
default:
return true;
break;
}
return true;
}
function hasType(el, el_type){
var result = empty(el.find(el_type));
if(result == false)
return true;
return false;
}
function getType(cid){
if(typeof cid == "object")
cid = getInId(cid, 1);
return $("#conf-"+cid+" > .type").html();
}
function getFields(cid){
return $("#conf-"+cid+" > .fields").html();
}
function getInput(table){
return $("#templates > #"+table+"-template").html();
}
function getCascade(cid){
return $("#conf-"+cid+" > .cascade").html();
}
function splitClass(el){
return el.attr('class').split('-');
}
function getInClass(el, pos){
var arr = splitClass(el);
return arr[pos];
}
function splitId(el){
return el.attr('id').split('-');
}
function getInId(el, pos){
var arr = splitId(el);
return arr[pos];
}
function getFunc(cid){
$func = $("#conf-"+cid+" > .updateFunction");
if(empty( $func ) == false)
return $func.html();
return 'updateArbitrary';
}
function decodeJson(json){
return eval('(' + json + ')');
}
Not much to say, a lot of small utility functions that I found the need to create to make my life easier. Three globals to keep track of what the hell we are doing too. The date, the type of worksheet we are using and the facility we are currently working with. These have all been selected in previous steps. Note jspanserializer, I’ve covered that one earlier, it’s a faithful warhorse that lets us encode javascript containers to PHP associative arrays. Sure I could’ve used some fancy JSON stuff but I know that jspan works and that’s that. Update: This one has been retired in favor of jQuery JSON.
function transText(){
var inner_html = $(this).html().replace(/( )+/, '');
$(this).html('<input type="text" value="'+inner_html+'" class="standard_box"/>');
}
function transSelect(){
var inner_html = getInput( getInId( $(this), 0 ) );
$(this).html(inner_html);
}
function fixText(){
$container = $(this);
$container.unbind("click");
$container.find("input").keydown(function(event){
if(event.keyCode == 13)
saveText($container, $(this));
});
}
function fixSelect(){
$container = $(this);
$container.find("select").change(function(){
saveSelect($container, $(this));
});
}
This is the logic that is responsible for all editing in place, the trans. functions change a piece of text into either an active input field or a select box. SaveText and saveSelect are responsible for actually seeing to it that something is passed to the server:
function execSave(cont, cur_val){
var arr_id = splitId(cont);
var cur_cid = arr_id[1];
var cur_table = arr_id[0];
var cus_id = arr_id[2];
var cur_cascade = getCascade(cur_cid);
var cur_fields = getFields(cur_cid);
var line_num = getInClass(cont, 1);
var pur_id = $("#pur-"+line_num).html();
$.post(baseUrl + getFunc(cur_cid), {id: cus_id, new_value: cur_val, cascade: cur_cascade, field: cur_fields, purchase_id: pur_id});
}
function saveText(cont, this_el){
var cur_val = this_el.val();
var inner_html = empty(cur_val) ? " " : cur_val;
cont.html(inner_html);
cont.unbind('click').toggle(transText, fixText);
execSave(cont, cur_val);
return cur_val;
}
function saveSelect(cont, this_el){
var cur_val = this_el.find("option:selected").val();
var cur_label = this_el.find("option:selected").text();
cont.html(cur_label);
execSave(cont, cur_val);
return cur_label;
}
In saveText we first get the value of course by getting it from the input field (this_el) and changing the inner html of cont (the div containing everything). SaveSelect() is a little more complicated, there we have to get both the value and the label in order to be able to replace the select box with an appropriate text. Both functions call execSave() which is able to get the cell id, current table and current customer id from it’s id field. The rest is retrieved from it’s config div which we discussed earlier.
The stuff retrieved from the config div is which tables we cascade the information to and which fields we are to update in each table. We also need the current line number which we retrieve from the div class this time (don’t ask me why). The line number is needed so that we in turn can fetch the proper purchase id which has been prepopulated when loading the worksheet or otherwise set when selecting/inserting a customer (more on that later). Lastly we submit the data to the server.
function selectCustomer(cus_id, line_num, cid, cur_name){
var cur_prod = getProdVal(line_num);
$.post(baseUrl + 'existingCustomer', {customer_name: cur_name, customer_id: cus_id, date: curDate, db_product: cur_prod}, function(cus_data){
loadCustomer(decodeJson(cus_data), line_num, cid, cur_name);
hideSearch();
});
}
function searchCustomer(cid, line_num){
$("#customer_search").removeClass("hidden_simple").addClass("workspace_searchCustomer");
$("#customer_search_input").keydown(function(event){
var cur_str = $(this).val();
if(cur_str.length > 2){
$.post(baseUrl + 'searchCustomers', {customer_name: cur_str, cell_id: cid, line_number: line_num}, function(search_result){
$("#customer_result").html(search_result);
});
}
});
}
function hideSearch(){
$("#customer_search").removeClass("workspace_searchCustomer").addClass("hidden_simple");
$("#customer_search_input").unbind("keydown").val("");
$("#customer_result").html("");
}
SearchCustomer() handles ye old SELECT * FROM customers WHERE name LIKE ‘%substr%’ to get a subset of all customers for easy and interactive searching. I’ve set the length of the substr to longer than two to avoid too many results.
SelectCustomer() is responsible for sending the proper information to the server when the user selects a customer in the “popup” that we covered earlier. The return from the server will be a JSON encoded assoc. array that we promptly evaluate to get the proper javascript object. What’s in the array? Customer information that we use to prepopulate the row with (more on that later).
Lastly we call hideSearch() which will hide the search interface.
Let continue with loadCustomer():
function loadCustomer(new_ids, line_num, cid, cur_name){
if(typeof new_ids == 'object'){
var cus_id = new_ids["customer"];
$("#pur-"+line_num).html(new_ids["purchase"]);
var data_arr = new_ids["data"];
var old_customer = true;
}else{
var id_arr = new_ids.split(" ");
var cus_id = id_arr[0];
$("#pur-"+line_num).html(id_arr[1]);
var old_customer = false;
}
var els = $(".div-"+line_num);
var col_num = 0;
jQuery.each(els, function (){
var table_cid = splitId($(this));
var cur_table = table_cid[0];
var cur_cid = table_cid[1];
var cur_type = getType(cur_cid);
var compl_id = $(this).attr('id') + '-' + cus_id;
$(this).attr('id', compl_id);
if(old_customer && cur_table != "line_num"){
var cur_html = empty(data_arr[col_num]) ? " " : data_arr[col_num];
$(this).html( cur_html );
if(cur_type == 'text')
$(this).toggle(transText, fixText);
else
$(this).toggle(transSelect, fixSelect);
}else{
$(this).html( getInput(cur_table) );
if(cur_type == 'text')
$(this).toggle(fixText, transText);
else
$(this).toggle(fixSelect, transSelect);
}
col_num++;
});
$cont = $("#db_customer-" + cid + "-" + cus_id).html(cur_name);
$cont.unbind("click").toggle(transText, fixText);
var pr_name = $("#product-"+line_num).find("option:selected").text();
$("#div-product-"+line_num).html(pr_name);
$("#delete-"+line_num).removeClass('hidden_simple');
$("#manage-"+line_num).removeClass('hidden_simple');
}
So first we check if we have an object or not, if we have an object then that must mean we are working with an existing customer and in that case we need the info we are to prepopulate with. The name new_ids for the container is in this case far from appropriate as it contains much more than just some ids. Anyway, the customer information ends up in data_arr.
In case we have a string we split it to get the id of our newly created customer and the new purchase id. This could have been done in a better way. If I knew what i was going to do from the beginning I would’ve simply serialized the whole thing in a similar way so that the customer data was the only difference. It’s on my todo list.
Next we fetch all the containers that we want to work with in the current row ( var els = $(“.div-“+line_num); ). If we are working with an old customer we use the data that the server gave us to prepopulate each container with simple text. Next we use toggle to set the click to first transform the text into active form input.
In case we are working with a new customer we do the opposite, we fetch active input and select boxes from our divs at the bottom of the document simply by copying their content into each container. Next we set toggle in exactly the opposite way of course. I suppose I could’ve used Array.shift() instead of increasing with col_num, oh well, just another thing on the todo list.
Finally we give the customer name container some extra attention because this one will be equal for both scenarios. We simply start with the customer name as pure text. Since we just set the click action with toggle we need to unbind it before we can change it. Next we change the product drop down to be simple text instead with the product name. The system currently does not support updating the product, in that case the whole purchase will have to be removed and added again with the new product, therefore the product is disabled. We also update the config area with the new product information (it will be used later when we save the workspace).
Finally we remove the hidden_simple class from our delete- and select buttons to show them.
function getProdVal(line_num){
return $("#product-"+line_num).val();
}
function onProdFocus(){
var line_num = splitId($(this)).pop();
$("input[name*='"+line_num+"']").attr('readonly', false);
$("img[class*='img-"+line_num+"']").click(function(){
searchCustomer(getInClass($(this), 2), getInClass($(this), 1));
});
}
function initWorkspace(){
var rows = $("tr[class*='trrow']");
jQuery.each(rows, function (){
if(hasType( $(this).find("div[id*='db_customer']:first"), "input") == false){
var cells = $(this).find("div[class^='div-']");
jQuery.each(cells, function (){
if(getType($(this)) == "text")
$(this).toggle(transText, fixText);
else
$(this).toggle(transSelect, fixSelect);
});
}
});
}
These functions will be used later in the document.ready function. OnProdFocus() will first get the current line we are working with and use that to set the customer name input field’s readonly attribute field to false and enable the select customer icon to show the search customer “popup” when it is pressed. The reason is that we need a product to be selected before we can allow any data to be passed to the server, if no product has been actively selected we can’t be sure that the user has remembered to select one, this is a way to prevent this. Bulletproofing the GUI in other words. This function is basically the start of the whole process, if the enter key is pressed in the customer name text input field is pressed we insert a new customer and attach a whole lot of things in a massive cascade. On the other hand if a customer is selected in the popup interface we use old data but we still create a new purchase and perform the cascading.
$(“input[name*='”+line_num+”‘]”) will return an input field whose name attribute contains line_num in this case.
InitWorkspace() is as the name implies – responsible for loading the workspace. We begin by retrieving all the rows we want to work with. We loop through them and:
– Begin with retrieving the first container in the row that has ‘db_customer’ in it’s id. If we follow convention this is the customer name field which need some extra attention because it’s responsible for adding new stuff to the database.
– Follow up with getting all the divs we want to work with, in effect all except area, purchase, select- and delete button. This is achieved through getting all divs in the row whose classes begin with ‘div-‘.
– Continue with looping through the divs and if we have a simple text we set the clicking accordingly, if not then we must have a select and set the clicking differently.
$(document).ready(function(){
$(".customer_input_insert").keydown(function(event){
if(event.keyCode == 13){
var cur_name = $(this).val();
var cid_lnum = splitId($(this));
var cid = cid_lnum[0];
var line_num = cid_lnum[1];
var cur_prod = getProdVal(line_num);
$.post(baseUrl + 'newCustomer', {customer_name: cur_name, db_product: cur_prod, date: curDate}, function(new_ids){
loadCustomer(new_ids, line_num, cid, cur_name);
});
}
});
$(".product_select").focus(onProdFocus);
$(".delete_button").click(function(){
var line_num = splitId($(this)).pop();
$cus_cont = $("div[class$='-"+line_num+"'][id^='db_customer']:first");
var cus_id = getInId($cus_cont, 2);
var cell_id = getInId($cus_cont, 1);
var els = $(".div-"+line_num);
$.post(baseUrl + 'deleteRow', {customer_id: cus_id, facility: curFacility, date: curDate});
jQuery.each(els, function (){
var table = getInId($(this), 0);
if( table != "line_num" )
$(this).html('');
var new_id = table + '-' + getInId($(this), 1);
$(this).attr('id', new_id);
});
$cus_cont.html( $("#customer_input_template").html() );
$cus_cont.find("input").attr('id', cell_id+'-'+line_num).attr('name', 'text-'+line_num);
$cus_cont.find("img").attr('class', 'img-'+line_num+'-'+cell_id);
$cus_cont.unbind("click").toggle(fixText, transText);
$pr_cont = $("#div-product-"+line_num).html( $("#product_select_template").html() );
$pr_cont.find("select").attr('id', 'product-'+line_num).focus(onProdFocus);
$(this).addClass('hidden_simple');
$("#manage-"+line_num).addClass('hidden_simple');
});
The document has finally loaded! We begin with setting the keydown event for the customer name input to submit it to the server for insertion and so on (discussed above).
Next we set the focus event for the product drop down (details also already discussed above).
The delete button resets a whole row, we need to for instance remove the customer id from all the div ids that it contains. (I think I should’ve used my config scheme for this instead, another todo.)
$(".manage_button").click(function(){
var line_num = splitId($(this)).pop();
$cus_cont = $("div[class$='-"+line_num+"'][id^='db_customer']:first");
var cus_id = getInId($cus_cont, 2);
var url = "manage.php?action=start_manage&id="+cus_id;
window.open(url);
});
$(".area_select").change(function(){
var line_num = splitId($(this)).pop();
var cur_area = $(this).val();
$.post(baseUrl + 'getHotels', {area: cur_area}, function(hotel_select){
$("#db_hotel-template").html(hotel_select);
$hotel_cont = $("div[class$='-"+line_num+"'][id^='db_hotel']");
if(empty($hotel_cont.find("select")) == false){
var cus_id = getInId($hotel_cont, 2);
$.post(baseUrl + 'changeArea', {date: curDate, facility: curFacility, customer_id: cus_id, area: cur_area});
$hotel_cont.html(hotel_select);
}
});
});
The manage button will simply redirect to another interface using the selected customer’s id (discussed above).
The select area drop down will – upon change – affect the hotels drop down to change it’s contents. This is one of a few rare times that we have to query the server for this information. Even I understand that I can’t have thousands of hotels hidden away somewhere in the GUI. Poor firefox is already suffering as it is.
$("#save_workspace").click(function(){
var saveArr = new Array();
var rows = $("tr[class*='trrow']");
jQuery.each(rows, function (){
var curRow = new Array();
var curData = new Array();
var line_num = '';
var cells = $(this).find("div");
jQuery.each(cells, function (){
var idPieces = splitId($(this));
if(idPieces[1] == "area")
curRow["area"] = $(this).find("option:selected").val();
else if(idPieces[1] == "product"){
if(hasType($(this), "select"))
curRow["product"] = "";
else
curRow["product"] = $(this).html();
}else if(idPieces[0] != "delete" && idPieces[0] != "manage"){
if(hasType($(this), "select")){
var result = saveSelect($(this), $(this).find("select"));
curData.push( result );
}else if(hasType($(this), "input")){
if(empty( $(this).find("input[class*='customer_input_insert']") )){
var result = saveText($(this), $(this).find("input"));
curData.push( result );
}else
curData.push("");
}else
curData.push( $(this).html().replace(/( )+/, '') );
if(empty(idPieces[2]) == false)
curRow["customer_id"] = idPieces[2];
}else
line_num = idPieces[1];
});
curRow["purchase"] = $("#pur-"+line_num).html();
curRow["data"] = curData;
saveArr[line_num] = curRow;
});
var cur_workspace = php_serialize(saveArr);
$.post(baseUrl + 'saveWorkspace', {date: curDate, facility: curFacility, workspace: cur_workspace, type: curType});
});
initWorkspace();
});
Finally the save workspace functionality. We loop through each row and all the relevant divs it contains to collect the necessary information, we also query some of the config divs to get the purchase id for instance. We finish by serializing and sending it off to the server side script which will actually just save the serialized string in the database where it will stay until this very workspace is loaded again.
I almost forgot some more HTML that is relevant:
workspace_customer_input.tpl:
<table cellpadding="0" cellspacing="0">
<tr>
<td><input id="{$cell_id}-{$line_num}" name="text-{$line_num}" readonly="true" type="text" size="30" class="workspace_box_customer customer_input_insert"/></td>
<td><img src="images/load.png" class="img-{$line_num}-{$cell_id}"/></td>
</tr>
</table>
workspace_component.tpl:
{if $nodiv neq 'yes'}
<div id="{$id}">
{/if}
{if $display eq "text"}
<input type="text" name="{$name}" class="workspace_box">
{elseif $display eq "select"}
{html_options name=$name options=$options class=workspace_box}
{/if}
{if $nodiv neq 'yes'}
</div>
{/if}
workspace_customer_search.tpl:
<table>
{foreach from=$customers item=customer}
<tr class="{cycle values="tr_color1, tr_color2"}">
<td width="375">{$customer.name_req}</td>
<td width="25">
<img src="images/select_small.png" onclick="selectCustomer('{$customer.id}', '{$smarty.request.line_number}', '{$smarty.request.cell_id}', '{$customer.name_req}')">
</td>
</tr>
{/foreach}
</table>
Related Posts
Tags: ajax, Javascript, jquery, ria, Smarty