There are pro's and con's to both mySql and MS SQL, one of the pro's of mySql is the limit function. This allows you to set a starting record number, and select N number of records from there. But how can we do that in MS SQL?

This is usually used for pagination functions, IE SELECT the next N records from the current count.

Firstly declare two variables, a "rows per page" and a start record.

Next we need to write the query we want, but wrap it in a parent select. Remember at this point to do all your conditional processing on the query inside the parenthesis.

Next we can apply a where clause that filters down the results based on the two variables declared at the top of the script. This means we will only get back the required number of records, starting at our defined record number.

The whole script looks like this:

I am selecting *, but I recommend that you actually list your fields here, I tweaked it for this entry.

Not quite as nice as the mySql version tho is it:

One of the more repetitive tasks a server side developer encounters is displaying the results from a query. This is traditionally in the format of a table that displays the rows of data, along with any other functionality, such as paging controls and sortable headers.

I was recently commissioned to look into building a generic table display "engine", and thought I'd investigate if there were any JQuery plugins that could do the bulk of the work for me. Ideally I didn't want to have to write a whole load of script to parse sorting variables, and detect if a limit was set on the returned record set for paging.

After some investigation I ended out using the table sorter JQuery plugin http://tablesorter.com/docs/.

This plugin allows for sortable results that you can page through, and it does not keep posting the values back and forth to the server.

Start by including the references to the JQuery libraries. I've also included references to the paging plugin, and the blue theme stylesheet.

Next we will create a fake query, so that we have some records to display.

We have to change the way we build the table code slightly, as the JQuery plugin is expecting certain field naming conventions. Give your table a class name of tablesorter. This is the style that the JQuery is watching for.

In the code above I am looping over the columnlist of the query to generate headings. I've had to change the headings to 'th' tags which I never normally use.

Next we can generate the table content, making sure it is inside a 'tbody' html tag. Simply loop over the query displaying all the results within td tags.

Lastly the pager plugin is looking for a div with a class of pager. Inside this div you place your paging controls, and the value of the page recordsets that you want to offset by.

This builds a one page table display that can paginate and sort with a single refresh.

A full example of this is here.

Most of us are familiar with the standard method of displaying data in a tabulated fashion, selecting a record, and populating the form that follows. What about editing the content directly into a template that mirrors the actual live version of a page?

This article examines how to edit web content directly inline, and commit it back to a server using an AJAX post request.

The main catalyst for this is that clients that use a content management system do not often have a clear image of how their content will look online. The traditional form layout for entering text does not lend itself well to representing the actual content in the format it is display in.

The aim here is to build a flexible system that allows for inline content editing, and saves it gracefully to a server based database.

I will start by saying thank you to Peter-Paul Koch. His article here (http://www.quirksmode.org/dom/cms.html) on making content editable was invaluable, and a lot of this is based on his theory.

We start by setting a value "editing" to false. This is the default for the page, as the user isn't editing anything when the page loads.

I wont go into massive depth on a line by line basis but Peter's article does break this down a lot. The premise is that there is a function catchit(), which will intercept any click events. It will then check that the event was triggered from a 'P' tag, which is our defining element for editable content. IE any P elements hold editable content. It will then remove the P html container, replacing it with a textarea, and re insert the P tags previous html content using the innerHTML JavaScript function.

In this way we can create editable inline textareas within the framework of our page.

The next step is to create a save function. The function 'saveToServer{)' take several arguments. It needs the value to commit, IE what the amended text string is, and the fieldname. Each 'P' tag has an id that I am matching to a data field. In this way if there are multiple p tags in a display they can each be attributed to a specific storage field in a database.

Because we are destroying the 'P' tag when we create the textarea we need to re assign the id to it when we save. We can do this by using the JavaScript function 'setAttribute'. The setAttribute function is used to set the value of an attribute on an object. It is typically used along with objects returned by document.getElementById to assign a new value to the object's attribute.

If we don't do this then the recreated 'P' tag no longer has an id attribute, so will error on any subsequent updates.

Next we use a JQuery Post function to post the values through an AJAX request.

This will post the values to the cfml CFC "commitChange.cfc", which handles them in a function.

This will allow you to perform seamless inline edits to the display layer, and commit them back to a server, so they are stored in real time.

There is an example of this here. (Minus the storing). You can track the AJAX post using a tool like charles http proxy, or firefox's firebug.

Now, to write a nice JQuery response handler to fade the returned massage in and out.