Creating an Ajax MySQL DataGrid with 7 Lines of Code
Creating an Ajax MySQL DataGrid with 7 Lines of Code
Read this article, which includes a tutorial, in order to learn more about how to create an Ajax MySQL DataGrid with only seven lines of code.
Join the DZone community and get the full member experience.Join For Free
Download "Why Your MySQL Needs Redis" and discover how to extend your current MySQL or relational database to a Redis database.
We've all been there, our boss asks us to create something that allows for viewing and editing some MySQL datatable from some web app. This problem is so common, it's even has its own acronym; CRUD! If you're anything like me, you try to code as little as possible. Not necessarily because you're lazy, but because you know that the more code you create, the more potential bugs you'll end up with. So the first question you should ask yourselves is, "How few lines of code is it possible to solve this problem with?" Well, let's first define the problem.
First, you'll need to know which database and table you should wrap. Then, you'll need to know which columns you should display. In addition, you'll need to be able to securely and dynamically create your SQL in such a way that it supports pagination, filtering, and sorting — IF your manager tells you he wants these features in the future. Hence, arguably, the smallest possible amount of code you should be able to get away with, is the following.
micro.widgets.mysql.datagrid database:camphora table:sales columns product amount seller
The above code declares the database, it declares the table, and it declares the columns you want to display. No need to go all "fancy" here. The above should be enough, right?
Of course, you'll need to make sure your code renders responsively, such that your users can use it from their phones, tablets, computers, and "whatever". Okay, so we need a CSS grid wireframe to put our datagrid inside of. Well, that problem should be easily solvable with the following code, right?
/* * Includes our CSS grid wireframe. */ micro.css.include /* * Creates our entire page, including our MySQL datagrid. */ create-widget class:container widgets div class:row widgets div class:col widgets /* * Our actual datagrid. */ micro.widgets.mysql.datagrid database:camphora table:sales columns product amount seller
17 lines of code, plus a couple of comments, and you've got a nicely laid out page with a Bootstrap'ish CSS grid container/row/column layout, displaying an Ajax datagrid that fetches items from your MySQL database. Seriously, the problem we started out with shouldn't really need more than the above code, right...?
Well, actually, the above is the solution to our problem. If you've never seen Hyperlambda before, I bet you thought the above code was "pseudo code," right? Let me surprise you a little bit. The above is a working implementation of the problem we started out with, written in "Hyperlambda." If you paste the above code into "Hypereval" from Phosphorus Five, and you save it as a "page," The above code will actually create a page for you with its own unique URL. Below is a screenshot of how creating such a "page" will look like.
When you have saved your page, you can click the "play" button to preview it, at which point a popup window will display something like the following in your browser. Notice how our solution already has its own friendly URL, matching the name of our "page."
A perfectly working, Ajax DataGrid, wrapping your MySQL database — assuming you actually have a database named "camphora" and a table named "sales." If you don't, you can exchange the database, table, and columns to whatever database and table you happen to have in your MySQL database.
Most problems you encounter during your day at the office are in fact so generic in nature that they don't need "coding" for you to solve them. In fact, you can easily get away with "declaring" the solutions to most of your problems. Several years ago, I realised that universal fact, and I set out to create Hyperlambda, which is kind of like a "markup language for web apps," similar in syntax to YAML. Hyperlambda just so happens to allow you to "declare" your web apps without anything resembling what you would refer to as "code" to function. If you look at the above "code" example for instance, it kind of resembles JSON more than it resembles C# or PHP, right?
Now, if you want to add pagination, filtering, or sorting to your datagrid, this is also very easy. In fact, let's add sorting to it just for fun.
/* * Includes our CSS. */ micro.css.include /* * Creates our page with a container/row/column CSS grid layout. */ create-widget class:container widgets div class:row widgets div class:col widgets /* * Our actual datagrid. */ micro.widgets.mysql.datagrid:your-datagrid database:camphora table:sales columns product .header widgets button innerValue:Product onclick /* * Re-databinding our datagrid, sorting by "product". */ micro.widgets.mysql.datagrid.databind:your-datagrid order-by:product amount .header widgets button innerValue:Amount onclick /* * Re-databinding our datagrid, sorting by "amount". */ micro.widgets.mysql.datagrid.databind:your-datagrid order-by:amount seller .header widgets button innerValue:Seller onclick /* * Re-databinding our datagrid, sorting by "seller". */ micro.widgets.mysql.datagrid.databind:your-datagrid order-by:seller
If you remove the comments and spacing above, we're now up to 38 lines of code, AND we have created an Ajax DataGrid, wrapping a MySQL database, allowing the user to sort. At this point, you're probably suspicious, and apart from the "WUT?" parts, you're probably asking yourself what the price for this is. However, before we take a look at that, let's look at our result so far.
Since the datagrid never downloads more items to the client than it selects during a databind invocation, this implies that you can have a datatable with millions of records without ever needing to spend more than some ~100Kb of initial bandwidth because the whole magic is all happening on the server.
So, How Flexible Is This?
Well, if you look at the above code, you will see the following:
amount .header widgets button innerValue:Amount onclick /* * Re-databinding our datagrid, sorting by "amount". */ micro.widgets.mysql.datagrid.databind:your-datagrid order-by:amount
If you want to play around with the above code, you can download Phosphorus Five here and use "Hypereval" to play with the code. In later articles, I might dive deeper into this and implement pagination and filtering if there is interest in it.
Opinions expressed by DZone contributors are their own.