Saturday, July 12, 2014

Control flow in MySQL conditional query



MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control within stored programs. It also supports RETURN within stored functions.
Suppose we have user table
CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `pwd` varchar(64) NOT NULL,
  `email` varchar(50) NOT NULL,
  `lasttime` date NOT NULL,
  `term` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
INSERT INTO `user` (`id`, `username`, `pwd`, `email`, `lasttime`, `term`) VALUES
(1, 'admin', '123456', 'admin@example.com', '2014-07-01', 'summer'),
(2, 'jiansen', '123456', 'jiansen@example.com', '2014-02-03', 'spring'),
(3, 'tom', '333333', 'tom@example.com', '2014-10-09', 'winter');

We want to convert lasttime (date format) to spring, summer or winter term.

1) SELECT in control flow in MySQL
SELECT  CASE 
     WHEN MONTH(DATE(lasttime))>= 1 AND MONTH(DATE(lasttime))<=4 THEN
       'spring'
     WHEN MONTH(DATE(lasttime))>= 5 AND MONTH(DATE(lasttime))<=8 THEN
        'summer'
     WHEN MONTH(DATE(lasttime))>= 9 AND MONTH(DATE(lasttime))<=12 THEN
        'winter' 
     END AS  Term
     FROM user

2) UPDATE in control flow in MySQL
UPDATE  user SET  term=CASE 
     WHEN MONTH(DATE(lasttime))>= 1 AND MONTH(DATE(lasttime))<=4 THEN
       'spring'
     WHEN MONTH(DATE(lasttime))>= 5 AND MONTH(DATE(lasttime))<=8 THEN
        'summer'
     WHEN MONTH(DATE(lasttime))>= 9 AND MONTH(DATE(lasttime))<=12 THEN
        'winter' 
     END 

Video: Example of control flow to select and update in MySQL

Thursday, July 10, 2014

Table header to stay fixed at the top when user scrolls using jQuery sticky plugin



jQuery sticky plugin  can be used to keep HTML table header to stay fixed at the top when user scrolls. jQuery sticky plugin can be downloaded from:
https://github.com/walmik/jquery.sticky
You need to copy jquery.sticky.min.js  and style.css to your working directory.
(Sometimes I found table header not align with body well when scroll down.
I slightly modify  jquery.sticky.js and directly use jquery.sticky.js, for example I found my table
header 10% less when I scrolled down, in this case in jquery.sticky.js
line 91 change
       $(firstRowThArr[i]).width(width);
to
         $(firstRowThArr[i]).width(Number(width)*1.1);

)
Example code:
<!DOCTYPE html>
<html>
<head>
    <title>jQuery Sticky plugin</title>
    <link rel="stylesheet" href="style.css">
</head>
<body>
  <table border="1" id="myTable">
    <tr><th>Problem</th><th>Solution</th> </tr>
   <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
   <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
   <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr>     
   <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
   <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
   <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr> 
    <tr><td>1</td><td>1</td> </tr>        
</table>
     <script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"></script>
  <script  src="jquery.sticky.min.js" type="text/javascript"></script>
    <script type="text/javascript">
        (function(){
            $('tr:even>td').addClass('alt');
            $("#myTable").sticky();
   //         $("#hdr3").sticky({bindWith: '#d1'});
    //        $("#hdr4").sticky();
        })();
    </script>       
  </body>
</html>

Note, if  you have menu <div id="menu"> </div> before the table, you may need set the position absolte/
       $("#menu").css('position', 'absolute');
style.css
body
{
    font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif;
    font-size: 14px;
}

#wrapper
{
    width: inherit;
    margin: 0px auto;
}

table
{
    width: 100%;


}

th
{
    text-align: left;
    background-color: #333;
    color: #fff;
    padding: 5px;
}

td
{
    padding: 5px;
    background-color: #f2f2f2;
}

.alt { background-color: #ADD8E6; }

#hdr3, #hdr4{
    background: #333;
    color: #fff;
    padding: 10px;
    margin: 0px;
    width: 980px;
}

#d1 {
    background: #666;
    padding: 10px;
    color: #fff;
    width: 980px;
}


jquery.sticky.js
 (function($){

    var JQuerySticky = function(element, options) {
        var defaults = {
            isTable: true,
            bindWith: 'body'   //Default. Used when no element is specified as bindWidth along with a non-table element.
        };

        this.options = $.extend(defaults, options);
        this.$el = $(element);
       
        this.init();
    };

    /*
        Initialize the plugin with common properties
    */
    JQuerySticky.prototype.init = function() {

    //set layout properties of the element for later use
    this.$stickyEl = this.$el;    //the element on which sticky will be applied (changes in case of a table)
    this.elWidth = this.$el.width();
    this.elHeight = 0;        //required in case sticky is applied on a non table along with a bindWidth element to check for scroll
    this.pos = this.$el.offset();


        /*
      Sticky properties will change based on the type of element.
      In case of a table, the row with the table header elements will be sticky.
      In case of a non-table, the whole element will be sticky.
    */
        if(this.$el.prop('tagName').toLowerCase() != 'table') this.options.isTable = false;

    /*
      Change parameters based on the type of element (table or not)
    */
        if(this.options.isTable) this.initStickyOnTable();
        else this.initStickyOnElement();
   
    var self = this;

    /*
      Initialize sticky behavior on window.scroll
    */
    $(window).on('scroll', function(){
      var windowScrollTop = $(window).scrollTop();
      if(windowScrollTop > self.pos.top && windowScrollTop < self.pos.top + self.elHeight)
      {
        self.$stickyEl.css('position', 'fixed');
      }
      else
      {
        self.$stickyEl.css('position', 'static');
      }
    });
    };

    JQuerySticky.prototype.initStickyOnTable = function() {
        var self = this;

        //cache the first and last row coz we ll use em again in this scope
        var $firstRow = $(this.$el.find('tr:first'));
        var $lastRow = $(this.$el.find('tr:last'));

        //since this is a table, we ll use the table's height to monitor the sticky behavior on scroll
        //and subtract the thead row's height to subtract from elHeight
        this.elHeight = this.$el.height() - $firstRow.height();

    var firstRowThArr = $firstRow.find('th');
    var lastRowTdArr = $lastRow.find('td');

        //assign widths to the th and td elements so that they dont collapse during sticky
    this.$el.find("th").each(function(i, th){
      var width = $(th).width();
      $(firstRowThArr[i]).width(width);
      $(lastRowTdArr[i]).width(width);
    });

    //assign total width and left pos to the table header
    $firstRow.width(this.elWidth);
    $firstRow.css('top', '0px');
    $firstRow.css('left', this.pos.left + 'px');
    $lastRow.width(this.elWidth);

    /*
            On tables with border-collapse set to the default 'separate',
            a small spacing is seen on the top of the element when its sticky.
            To tackle this, get the Y value of that spacing and subtract it later during sticky.
    */
    //check border collapse to sort issues of a minor spacing on the top of the table when sicky
   
    if(this.$el.css('border-collapse') == 'separate') {

      var borderSpacingStr = this.$el.css('border-spacing'); //sample: 2px 2px
      //get the X and Y value from the sample
      var xBorderSpacing = borderSpacingStr.split(' ')[0];    //returns: 2px
      var yBorderSpacing = borderSpacingStr.split(' ')[1];    //returns: 2px
      //get rid of the 'px' and update margin top and left
      var marginTop = parseInt(yBorderSpacing.substring(0,yBorderSpacing.indexOf('px')), 10);
      $firstRow.css('top', (0 - marginTop) + 'px');
      //var marginLeft = this.pos.left + parseInt(xBorderSpacing.substring(0,xBorderSpacing.indexOf('px')), 10);
    }

    //Since this is a table, the table header row will be the sticky element
    this.$stickyEl = $firstRow;
   
    };

    JQuerySticky.prototype.initStickyOnElement = function() {
        var self = this;

    var marginTopStr = this.$el.css('margin-top'); //sample: 10px
    //get rid of the 'px'
    var marginTop = 0 - parseInt(marginTopStr.substring(0,marginTopStr.indexOf('px')), 10);
    this.$el.css('top',  marginTop +'px');
    this.$el.css('width', this.elWidth+'px');

    //init sticky on window scroll
    //bindWidth parameter uses 'body' as default, incase user specified an element, then use that
    this.elHeight = $(this.options.bindWith).height();
    };


    ///////////////////////////////////////////////////
    ///////////////INITIALIZE THE PLUGIN///////////////
    $.fn.sticky = function(menu) {
        return this.each(function() {
            new JQuerySticky(this, menu);
        });
    };
    ////////////////////////////////////////////////////
    ////////////////////////////////////////////////////


 })(jQuery);

Video: Table header to stay fixed at the top when user scrolls using  jQuery sticky plugin

Saturday, July 5, 2014

Change row background color alternatively in HTML table using jQuery or CSS



To Change row background c0lor alternatively in jQuery:
<script>
$(document).ready(function()
{
  $("tr:even").css("background-color", "lightblue");
});
</script>

In CSS
<style type="text/css">
 tr:nth-child(2n) { background-color: #FFEBCD; }
 </style>
There is a small difference between jQuery and CSS.  When counting tr row number, jQuery includes th. Full code example:
<!DOCTYPE html>
<html>
<head>
<script
src="http://code.jquery.com/jquery-latest.min.js"
        type="text/javascript">
</script>
<script>
$(document).ready(function()
{
  $("tr:even").css("background-color", "lightblue");
});
</script>
<style>
/*tr:nth-child(2n) { background-color: lightblue; }*/
table,th,td
{
border:1px solid black;
border-collapse:collapse;
}
th,td{padding:5px;}
h1 {text-align:center}
</style>
</head>
<body>
<h1>Change row background color alternatively in CSS or jQuery</h1>
<table style="width:100%">
<tr>
  <th>Firstname</th><th>Lastname</th><th>Scores</th>
</tr>
<tr>
  <td>John</td><td>Smith</td><td>100</td>
</tr>
<tr>
  <td>Eve</td><td>Jackson</td><td>94</td>
</tr>
<tr>
  <td>Terry</td><td>Doe</td><td>80</td>
</tr>
<tr>
  <td>Golden</td><td>Wu</td><td>88</td>
</tr>
</table>
</body>
</html>

Video: Change row background color alternatively  in HTML table using jQuery or CSS

Tuesday, July 1, 2014

Example, create and use triggers in MySQL



We can create a trigger user_trig in MySQL
CREATE TRIGGER user_trig BEFORE INSERT
        ON users FOR EACH ROW
        INSERT INTO trigger_time VALUES(NOW())

When we execute an insert statement on users table, the trigger user_trig is executed and current time is inserted.
Tables users and trigger_time are created as follows:
CREATE TABLE users
(
user_id varchar(45) NOT NULL,
salary int NOT NULL DEFAULT 0,
PRIMARY KEY (user_id)
)

CREATE TABLE trigger_time
(
exec_time datetime NOT NULL
)

When we
INSERT INTO users VALUES('jiansen',6000)
In table trigger_time, we can see
2014-07-01 21:57:25
To see the triggers we created
SHOW TRIGGERS;
which is the same as
SELECT * FROM information_schema.triggers;
The trigger information is stored in triggers table in database information_schema
To delete trigger user_trig
DROP TRIGGER user_trig;
 Video: Example, create and use triggers in MySQL