Saturday, October 15, 2011

Auto Number w/ formatting in PHP/MySQL

In creating your database you must set in mind that it should always complies with normalization. 

If you are given a task to create unique IDs for every person who register on your website (e.g. REG-2011-000001) you must find a better way to represent the data on the front end and back end properly.

If you will visit forums some of the idea maybe given to you is to create random numbers, or a row counter for the value of your IDs. 
  • Generating random numbers for you IDs is prone for data duplication since a random number is not always unique every time it was created, The solution for this is a 'checker for duplicate IDs' but it has drawbacks on performance since every time you need to add a new row, you need to query against your database for checking.
  • A row counter is prone for data inconsistency. If you don't have a 'place holder for the number of deleted rows', you will need this as an 'adder variable' to the current row count. The performance also pay the price since you do query to count rows before adding a new row.

You must do it plain and simple, Let's begin!...

1.) Open your MySQL monitory and execute the commands on the figure below. 

On this Registration table we will use the id column and created column to make it look like REG-2011-000001 upon data representation, Both id column and created column is not required on insert statement.

2.) Code the php script, follow the code below.

On this code the EXTRACT and the PRINTF function is used to represent the IDs properly with formatting.

2.) Output should look like below.



Monday, September 5, 2011

elycharts Part 2 of 2 (ASP .NET WebForms)


The main goal here is to create a N-Tier VB/C# .NET project counterpart for the previous web application which is written on PHP. The architecture will involved a simple business tier and presentation tier layers.

Let's begin!...
1.) On Visual Studio click on to create a 'New Project'. The dialog below will appear, choose Installed Templates > Other Project Types > Visual Studio Solutions then pick Blank Solution. Fill up the Name field as ElyCharts and hit OK.


2.) Right click on ElyCharts solution, choose Add > New Project.

3.) A dialog will appear, on the Installed Templates choose VB or C# then pick Class Library. Name it as ElyChartsObjects then hit OK. This is the business tier layer.

4.) Repeat # 2.

5.) A dialog will appear, on the Installed Templates expand VB or C#, choose Web, then pick ASP .NET Empty Web Application. Name it as ElyChartsAspNet then hit OK. This is the presentation tier layer.


6.) Start adding items on each project.

       6.1) ElyChartsObjects -Add Classes namely chart, legend, tooltips, values.

       6.2) ElyChartsAspNet -Add a single WebForm name Default
              Add a Folder name Scripts and another Folder inside name lib,
              Inside Scripts add a JavaScript name tmpl.js (copy & paste the source code from the previous php web application).
              Download elycharts.min.js, jquery-1.6.2.min.js and raphael-min.js then place it on Scripts/lib folder.

      The solution should look like below. L => VB, R => C#.
 
7.) Right click the ElyChartsAspNet project choose Add Reference..., A dialog will appear with the ElyChartsObjects project selected. Hit OK.

8.) Start coding for the ElyChartsObjects, below are POCO good for later json serialization.




9.) Start coding for the ElyChartsAspNet, below is the code-behind and markup of Default
We use JavascriptSerializer.Serialize to convert the 'chart' instance to JSON.


10.) Right click the ElyChartsAspNet project > Set as StartUp Project then hit F5.
 
11.) Output should look like below.

Sunday, September 4, 2011

elycharts Part 1 of 2 (PHP)


Elycharts is an open source pure javascript charting library, no need for jre or flash player to be installed on your 127.0.0.1, you just need a web browser (e.g. Firefox 3.0+, Safari 3.0+, Opera 9.5+, Internet Explorer 6.0+, Google Chrome/Chromium). This tool supports multiple chart types like pie, bar, line, etc.. and dependent on Raphaël and jQuery libraries.

Elycharts is customizable using JSON format. You can easily manipulate its colors, text parameters, sizes and the grid types. 

Since the examples on the official site of Elycharts are all javascript it's quite interesting to integrate a server-side scripting like PHP.


Let's begin!...

1.) Go to http://elycharts.com/docs and read the documentation, This is required because we will use the "QuickStart - Step 4" sample for the demo.

2.) On your htdocs create a folder named 'ElyCharts', go inside this directory and create a folder and file structure same with the image below.
 3.) As far as object-oriented programming is concern, below are POPO good for later json serialization.

         3.1.) classes/chart.php

        <?php

        include 'tooltips.php';
        include 'values.php';
        include 'legend.php';

        class charts {

            private $template;
            private $tooltips;
            private $values;
            private $legend;

            function set_template($template)
            {   
                $this->template = $template;
            }

            function get_template()
            {   
                return $this->template;
            }

            function set_tooltips(tooltips $tooltips)
            {
                $this->tooltips = $tooltips;
            }

            function get_tooltips()
            {
                return $this->tooltips;
            }

            function set_values(values $values)
            {
                $this->values = $values;
            }

            function get_values()
            {
                return $this->values;
            }

            function  set_legend(legend $legend)
            {
                $this->legend = $legend;
            }

            function  get_legend()
            {
                return $this->legend;
            }
        }

        ?>
         3.2.) classes/legend.php

        <?php

        class legend {

            private $serie1;
            private $serie2;

            function set_serie1($serie1)
            {
                $this->serie1 = $serie1;
            }

            function get_serie1()
            {
                return $this->serie1;
            }

            function set_serie2($serie2)
            {
                $this->serie2 = $serie2;
            }

            function get_serie2()
            {
                return $this->serie2;
            }
        }

        ?>

         3.3.) classes/tooltips.php


        <?php

        class tooltips {

            private $serie1 = array();
            private $serie2 = array();

            function set_serie1($serie1)
            {
                $this->serie1 = $serie1;
            }

            function get_serie1()
            {
                return $this->serie1;
            }

            function set_serie2($serie2)
            {
                $this->serie2 = $serie2;
            }

            function get_serie2()
            {
                return $this->serie2;
            }
        }

        ?>

         3.4.) classes/values.php


        <?php

        class values {

            private $serie1 = array();
            private $serie2 = array();

            function set_serie1($serie1)
            {
                $this->serie1 = $serie1;
            }

            function get_serie1()
            {
                return $this->serie1;
            }

            function set_serie2($serie2)
            {
                $this->serie2 = $serie2;
            }

            function get_serie2()
            {
                return $this->serie2;
            }
        }

        ?>

4) Download elycharts.min.js, jquery-1.6.2.min.js and raphael-min.js then place it on 'scripts/lib' folder.

5.) Go to scripts/tmpl.js, then copy and paste the code below, This is only a template for the look and feel of the chart.

    $.elycharts.templates['line_basic'] = {
        type: "line",
        margins: [10, 10, 20, 50],
        defaultSeries: {
            plotProps: {
                "stroke-width": 4
            },
            dot: true,
            dotProps: {
                stroke: "white",
                "stroke-width": 2
            }
        },
        series: {
            serie1: {
                color: "red"
            },
            serie2: {
                color: "blue"
            }
        },
        defaultAxis: {
            labels: true
        },
        features: {
            grid: {
                draw: [true, false],
                props: {
                    "stroke-dasharray": "-"
                }
            },
            legend: {
                horizontal: false,
                width: 80,
                height: 50,
                x: 210,
                y: 220,
                dotType: "circle",
                dotProps: {
                    stroke: "white",
                    "stroke-width": 2
                },
                borderProps: {
                    opacity: 0.3,
                    fill: "#c0c0c0",
                    "stroke-width": 0,
                    "stroke-opacity": 0
                }
            }
        }
    }
 

6.) And lastly, Create your index.php and try the code below, the colored in red is a PHP source, green  is a regular HTML and purple is JavaScript/jQuery.

Note: Do not include to your source-code the text which begins with 3 asterisk.

*** php imports
    <?php
    include 'classes/chart.php';
    ?>
    <!DOCTYPE html>
    <html>
        <head>
            <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
            <title>Elycharts - Default</title>
*** style sheet
            <style type="text/css">
                #chart { width: 300px; height: 300px; }
            </style>
 *** javacript imports
            <script type="text/javascript" src="scripts/lib/jquery-1.6.2.min.js"></script>
            <script type="text/javascript" src="scripts/lib/raphael-min.js"></script>
            <script type="text/javascript" src="scripts/lib/elycharts.min.js"></script>
            <script type="text/javascript" src="scripts/tmpl.js"></script>
            <?php
 
 *** object reflection
            function toDataObj($obj) {
                $ref = new ReflectionClass($obj);
                $data = array();
                foreach ($ref->getMethods() as $method) {
                    if ((0 === strpos($method->name, "get"))
                            && $method->isPublic()) {
                        $name = substr($method->name, 4);
                        $name[0] = strtolower($name[0]);
                        $value = $method->invoke($obj);
                        if ("object" === gettype($value)) {
                            $value = toDataObj($value);
                        }
                        $data[$name] = $value;
                    }
                }
                return $data;
            }
 
*** tooltip instance, this where you can put dynamic values.
            $tooltips = new tooltips();
            $tooltips->set_serie1(array("a", "b", "c", "d"));
            $tooltips->set_serie2(array("a", "b", "c", "d"));
 
*** values instance, this where you can put dynamic values.
            $values = new values();
            $values->set_serie1(array(52, 76, 67, 49));
            $values->set_serie2(array(10, 17, 1, 19));
 
*** legend instance, this where you can put dynamic values.
            $legend = new legend();
            $legend->set_serie1("Serie 1");
            $legend->set_serie2("Serie 2");
 
*** charts instance, this where you can put dynamic values.
            $chart = new charts();
            $chart->set_template("line_basic");
            $chart->set_tooltips($tooltips);
            $chart->set_values($values);
            $chart->set_legend($legend);

            ?>
            <script type="text/javascript">

 *** This is where dynamic values will be generated.
                $(document).ready(function () {        
                    $("#chart").chart(<?= json_encode(toDataObj($chart)) ?>);
                });
            </script>
        </head>
        <body>
            <form id="frmDefault">
                <h2>
                    Elycharts on PHP</h2>
 *** This is where the chart will materialize.
                <div id="chart">
                </div>
            </form>
        </body>
    </html>

 7.) Output should look like below.



click here to view elycharts Part 2 of 2 (ASP .NET WebForms)