Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Creating an Ajax MySQL DataGrid with 7 Lines of Code

DZone's Guide to

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.

· Database Zone ·
Free Resource

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.

Image title

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."

Image title

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.

WUT...?

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.

Image title

Clicking the buttons in your solution will now sort your result and re-databind your datagrid. However, what is "the cost" for this? Normally such RAD tools as you're using above consumes megabytes of bandwidth, right? Well, believe it or not, the above web app will never download more than 5.7Kb of JavaScript to your client. Its total bandwidth consumption is 82Kb, including your CSS files, buttons, and the whole she bang. And it renders perfectly on your phone, tablet, or "whatever." When you click a button, it consumes an additional 1.6Kb of bandwidth. That is 1.6Kb of bandwidth consumption to sort a datagrid. Most other RAD datagrids out there will easily consume megabytes of initial bandwidth and easily 10x as much bandwidth for each "databind" operation.

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 take a look at line 4 above, it's declaring a "button widget." This implies that it will create a simple "button" HTML element for you, automatically creating an "onclick" event handler for it, which automatically will create the necessary Ajax request for you, going towards your server, and evaluating your Hyperlambda for you as the button is clicked, and if you look at the source for your web app, you will see that it renders a plain old HTML table for you. Hence, if you know HTML and CSS from before, there shouldn't be anything in the above code's result that's not well known to you from before. In fact, it'll even nicely format your HTML. Not too bad for 38 lines of code and a handful of comments. Of course, comparing our 38 lines of actual code above with "whatever tutorial" you find out there on the internet, trying to accomplish something similar, with "whatever framework", should really be all the arguments you need. And our initial datagrid started out with 7 lines of code! And it consumes 5.7Kb of JavaScript! Please allow these numbers to sink in ...

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.

Read "Developing Apps Using Active-Active Redis Enterprise" and discover the advantages over other active-actve databases.

Topics:
ajax ,mysql ,web developement ,database

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}