Creating a SCRUD System Using jQuery, JSON and DataTables
Every non trivial software allows to manipulate data, generally stored in a database. The acronymSCRUD refers to the basic manipulation functions that the user needs to work with the available data: Search, Create, Read, Update and Delete.
In this article we'll create a web application which empowers the user to search a database and fully manage its content. The system is based on jQuery, JSON, and the popular DataTables and jQuery Validation plugins.
What We'll Use
In this section I'll briefly cover the libraries and plugins I'll employ in this tutorial.
jQuery and Ajax
jQuery is a free, open source and cross-platform JavaScript library which simplifies client-side HTML scripting. jQuery makes it easier to select the DOM elements and perform DOM manipulation operations, and handle events, such as mouse clicks and keyboard input. In my opinion, jQuery is the de facto standard for creating Ajax applications.
Ajax is a technique that allows you to execute HTTP requests asynchronously. This means that web applications can retrieve data from and send data to the web server without fully reloading the web page.
At SitePoint we've covered jQuery and Ajax multiple times. If you need a refresh on these topics, you can take a look at these articles:
- How to Use jQuery's $.ajax() Function
- Easy Ajax with jQuery
- An Introduction to jQuery's Shorthand Ajax Methods
- jQuery articles on SitePoint
- jQuery: Novice to Ninja: New Kicks And Tricks
This tutorial will show you how to create a SCRUD application which performs all the tasks without having to reload the web page even once.
JSON
For the transfer of data between the application and the web server we'll use the JSON format. The JSON data format is based on attribute-value pairs, which means that each attribute has both a value and a name to identify it.
DataTables and jQuery Validation Plugins
jQuery is very suitable to use in conjunction with plugins and it's designed to be easily extensible. In this tutorial we'll employ two plugins. The first one is called DataTables. It adds advanced interaction controls to ordinary HTML tables such as pagination, search functionality, and column sorting. The second isjQuery Validation which makes it easier to validate form elements.
Getting Started
For our example we'll create the following files in the same folder:
data.php: the PHP script that will perform the database tasksindex.html: the HTML file of our interfacelayout.css: the CSS file containing the style of our web applicationwebapp.js: the JavaScript file, written using jQuery, which manages the front-end actions and events
You can download all the source code from my repository on GitHub. Apart from that, you'll need a functioning MySQL database.
The first task is to create a new HTML5 file called index.html consisting of:
- An Add button
- A mostly empty table (which will display the database data)
- An add/edit form
- A noscript message
- A message container
- A loading message container
In the head section of the HTML file we load jQuery version 1.11.2. This file is downloaded from a Google server. As stated here:
the Google Hosted Libraries service is a stable, reliable, high-speed and globally available content distribution network for many popular open-source JavaScript libraries.
DataTables provides a similar CDN. We load DataTables version 1.10.0 from their network and the jQuery Validation plugin version 1.13.1 from a CDN to get things started.
