Saturday, November 8, 2014

Ajax autosave selection without submit button

Ajax autosave selection without submit button
1. jQuery library is called
2. The value of  selection in dropdown menu and id are posted
to saveDecision.php, and decsion table in MySQL is updated by ajax post values.
3. The return message is save in div with id autosavenotify
 <script src=""></script>
$('select.status').on('change',function () {
        var decision = $(this).val();
        var id = $('td.myid').html();
                 type: "POST",
                 url: "saveDecision.php",
                 data: {decision: decision, id: id },
                 success: function(msg) {
<h1>Ajax autosave</h1>
<div id="autosavenotify"></div>
<table width="200px">
    <td class="myid">
        <td><select class="status" >
          <option value='-1'>--Select--</option>
    <option value='approve'>Approve</option>
    <option value='reject'>Reject</option>
    <option value='pending'>Pending</option>

if (isset($_POST['id'])) {
if (!$con)
  die('Could not connect: ' . mysql_error());
mysql_select_db("test", $con);
    $query = "UPDATE decision SET select_result='$decision' WHERE id=$id";
    $resource = mysql_query($query)
        or die (mysql_error());
    echo 'decision table is successfully updated to '.$decision;   

MySQL Database
  `id` int(8) NOT NULL,
  `select_result` varchar(40) NOT NULL,
  PRIMARY KEY (`id`)

INSERT INTO `decision` (`id`, `select_result`) VALUES (1, ' ');

Video: Ajax autosave selection without submit button

Friday, October 24, 2014

jQuery Modify dropdown menu and default value

1) Call latest jQuery library
2) JS Associate Array, data may come from ajax post
     '1': {id:1, name:'Andy'},
     '2': {id:2, name:'Jiansen'},
     '3': {id:3, name:'Tommy'}

  3) Put t in dropdown menu and set default value 2
4) Able not to duplicate id    
5) first we have a empty selection dropdown
 <select id="mySelect"><option value=" ">=No TA Assigned=</option></select>
6) Then we have JS function  myFunction() to deal with onclick to add items to dropdown, to append item in dropdown list
row.find('td.Ta select').append('<option value="'+taList[id0].id+'">'+taList[id0].name+'</option>');
7) We need to prevent duplication of items  dropdown, also we can set the default value
   row.find('td.Ta select').val('2');
Complete Code:
<!DOCTYPE html>
<script src=""></script>
table {
    border-collapse: collapse;
table, th, td {
    border: 1px solid black;
function myFunction() {
   var taList = {};
     '1': {id:1, name:'Andy'},
     '3': {id:3, name:'Tommy'} };
   var row = $('table.template tr.template');
//add other options
    for (var id0 in taList){
        var x = document.getElementById("mySelect");
        var i, add='y';
        //prevent duplication           
        for (i = 0; i < x.length; i++) {
 if(t[j].id ==x.options[i].value) add='y' ;
           if(add=='y')     row.find('td.Ta select').append('<option value="'+taList[id0].id+'">'+taList[id0].name+'</option>');          
//Set default value, for example Andy
    row.find('td.Ta select').val('2');

<h1>Add content in dropdown menu and set its default value in jQuery</h1>
 <table class='template' style="border:1" >
   <th>Courses</th><th>Pick TA</th>
<tr class='template'>
 <td>Physics</td>   <td class='Ta'><select id="mySelect"><option value=" ">=No TA Assigned=</option></select></td>
<button onclick="myFunction()">Click me to add TAs TA dropdown list</button>
<p id="demo"></p>

Video: jQuery  Modify dropdown menu and default value
Note: in video, 
       if(taList[id0].id ==i) add='n'
should be changed to
 if(t[j].id ==x.options[i].value) add='y' ;

Sunday, October 12, 2014

Examples of .htaccess - web directory password protected

.htaccess file is used to control the directory access
in Apache web server. Comparing to httpd.conf, 
httpd.conf is global settings for apache.
.htaccess can be applied per folder, overwrite http.conf 
1) Example 1 of .htaccess, prevent directory list
Options -Indexes
You will get message:
You don't have permission to access /htaccess_test/ on this server."
2) Example 2, will not display .txt file
Options +Indexes
IndexIgnore *.txt

You can not see txt in list, but you still can access, to disable access to txt
<Files "*.txt">
    Order deny,allow
    Deny from all
3) Example 3, deny all access
<Files "*">
    Order deny,allow
    Deny from all
You will get
You don't have permission to access /htaccess_test/ on this server."
Remember, no space between deny and allow, or you will get
error message
"Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.
Please contact the server administrator, root@localhost and inform them of the time the error occurred, and anything you might have done that may have caused the error.
More information about this error may be available in the server error log."
4)For password protection
 Create encrypted password  for user name jiansen for
 htpasswd -c /var/www/html/htaccess_test/.htpasswd jiansen
File permission:
chmod 644 /var/www/html/htaccess_test/.htpasswd
Make sure it can be read by apache, but can not read by outside
AuthType Basic
AuthName "Restricted access"
AuthUserFile  "/var/www/html/htaccess_test/.htpasswd"
Require valid-user
<Files ~ "^\.ht">
  Order allow,deny
  Deny from all

Video: Some examples of .htaccess

Saturday, October 11, 2014

Using Cron Job to process PHP scripts

Using Cron Job to process PHP scripts.
For example, we have test.php and want to run it  every day using cron job.
1) test.php
    echo '<h1> This is  a test for cron job</h1>';

 2) Create a shell script
#! /bin/sh
#wget -q --no-check-certificate -O- ""
php test.php
3) run following in shell to make executable
chmod +x
4)  The reason we want to use php instead of wget is that we do want outside people access test.php
Set up .htaccess to Deny from all in the directory of
<Files "*">
    Order deny, allow
    Deny from all

5) crontab -e
30 23 * * * /home/jiansenl/crontest/
each night 23:30pm to  run
6) crontab -l
list cron jobs.
Video: Using Cron Job to process PHP scripts

Sunday, October 5, 2014

Install PHPMailer 5.2.4 and use smtp gmail

1) PHPMailer download
I download and use winrar to unzip and put it in

2)Configuration php.ini to enable openssl
In C:\xampp\php\php.ini
 If not, you may get the following message:
"SMTP -> ERROR: Failed to connect to server:
 Unable to find the socket transport "ssl" -
 did you forget to enable it when you configured PHP? (1909703479) "

3) Restart Apache

4) Create index.php under  C:\xampp\htdocs\phpmailertest. The red code below is import for using gmail as SMTP.  Change jiansentest and mypassword to your gmail account and password.
$mail = new PHPMailer();
$mail->IsSMTP(); // set mailer to use SMTP
$mail->SMTPDebug  = 2;
$mail->From = "";
$mail->FromName = "Jiansen";
$mail->Host = ""; // specif smtp server
$mail->SMTPSecure= "ssl"; // Used instead of TLS when only POP mail is selected
$mail->Port = 465; // Used instead of 587 when only POP mail is selected
$mail->SMTPAuth = true;
$mail->Username = ""; // SMTP username
$mail->Password = "mypassword"; // SMTP password

$mail->AddAddress("", "Jiansen"); //replace myname and mypassword to yours
$mail->AddReplyTo("", "Jiansen");
$mail->WordWrap = 50; // set word wrap
//$mail->AddAttachment("c:\\temp\\js-bak.sql"); // add attachments

$mail->IsHTML(true); // set email format to HTML
$mail->Subject = 'test';
$mail->Body = 'test';

if($mail->Send()) {echo "Send mail successfully";}
else {echo "Send mail fail";}

5) Run
We got
SMTP -> FROM SERVER:220 ESMTP uf6sm10363353pac.16 - gsmtp
SMTP -> FROM SERVER:250 2.1.0 OK uf6sm10363353pac.16 - gsmtp
SMTP -> FROM SERVER:250 2.1.5 OK uf6sm10363353pac.16 - gsmtp
SMTP -> FROM SERVER:354 Go ahead uf6sm10363353pac.16 - gsmtp
SMTP -> FROM SERVER:250 2.0.0 OK 1412491179 uf6sm10363353pac.16 - gsmtp
SMTP -> FROM SERVER:221 2.0.0 closing connection uf6sm10363353pac.16 - gsmtp
Send mail successfully

Video: Install PHPMailer 5.2.4 and use smtp gmail 

Thursday, October 2, 2014

PHP mail function using Gmail account in Windows XAMPP

1) When we test php mail code in  Windows XAMPP, we do  not have smtp server in localhost. Windows XAMPP has sendmail.exe, but do not have PHPmailer. The following setting will make mail function working in PHP in XAMPP.
2) For example, you install XAMPP in C:\xampp
in C:\xampp\sendmail\sendmail.ini:
in C:\xampp\php\php.ini:
[mail function]
sendmail_path = "\"C:\xampp\sendmail\sendmail.exe\" -t"
3) Replace  **** to your gmail account name and password.
Restart Apace in XAMPP.
4) testmail.php to test if mail function is working

    $message = "The mail message was sent with the following mail";
    $headers = "From:";
    mail("", "Testing", $message, $headers);
    echo "Test message is sent to<BR/>";
Change to your email account. You will receive an email. 
Video: PHP mail function using Gmail account in Windows XAMPP

5) PHPmailer is another option to send email in PHP. 

For Linux, refer to:

Wednesday, October 1, 2014

Install Tex Live in Windows and Linux, Add Liberation Fonts

TeX Live is a free software distribution for the TeX typesetting system that includes major TeX-related programs, macro packages, and fonts. Textlive can be installed in Windows or Linux. (Around 3GB)
Texlive can be downloaded from:

1) For Windows, run

default location

2)  Add new font, such as Liberation Fonts
If you want to add extra ttf  fonts, such as The Liberation(tm) Fonts, which can be downloaded from:
under C:\texlive\2014\texmf-dist\fonts\truetype\public
create directory liberation
copy all ttf liberation font files to liberation  directory.

For Linux, create directory liberation under /usr/share/fonts/
copy all ttf liberation font file to /usr/share/fonts/liberation

3)  Installation for Redhat Linux
 $  wget
$  tar xf install-tl-unx.tar.gz
$  cd 'directory where file extracted'
$  sudo ./intall-tl
$  O
$  L
        Choose the following:
    (1)   New value for binary directory [/usr/local/bin]: /usr/bin
    (Type /usr/bin.)
    (2)   New value for man directory [/usr/man]:
    (Do not type anything.)
    (3)   New value for info directory [/usr/info]:
    (Do not type anything.)
$  R
$  I
You may also need to install PDFtk:
$ wget
$ sudo yum install jre-gcj
$ sudo rpm -Uvh pdftk-2.02-1.el6.x86_64.rpm

Video:  Install Tex Live in Windows and Linux, Add Liberation Fonts

Tuesday, September 30, 2014

PHP, convert latex to pdf

To convert latex to pdf in PHP, first we need to download text live
xelatex  executable from text live will be used in PHP 
1) In temporary directory, we put the text template as a tex file and run xelatex to convert tex to pdf.
then display pdf file.
ob_get_contentsReturn the contents of the output buffer
 tempnamCreate file with unique file name
sys_get_temp_dirReturns directory path used for temporary files
basenameReturns trailing name component of path

        include 'latex_template.php';
        $outputData .= ob_get_contents();
        $texFile = tempnam(sys_get_temp_dir(), 'test');
        $base = basename($texFile);
        rename($texFile, $texFile.".tex");
        $texFile .= ".tex";
        file_put_contents($texFile, $outputData);

        $console = shell_exec("xelatex {$base}");
        header("Content-type: application/pdf");
        $pdf =    dirname(realpath($texFile)).DIRECTORY_SEPARATOR.$base.".pdf";


\section*{Notes for My Paper}

Don't forget to include examples of topicalization.
They look like this:

\enumsentence{Topicalization from sentential subject:\\
\shortex{7}{a John$_i$ [a & kltukl & [el &
  {\bf l-}oltoir & er & ngii$_i$ & a Mary]]}
{ & {\bf R-}clear & {\sc comp} &
  {\bf IR}.{\sc 3s}-love   & P & him & }
{John, (it's) clear that Mary loves (him).}}

\subsection*{How to handle topicalization}

I'll just assume a tree structure like (\ex{1}).

\enumsentence{Structure of A$'$ Projections:\\ [2ex]
    & \node{i}{CP}\\ [2ex]
    \node{ii}{Spec} &   &\node{iii}{C$'$}\\ [2ex]
        &\node{iv}{C} & & \node{v}{SAgrP}


Mood changes when there is a topic, as well as when
there is WH-movement.  \emph{Irrealis} is the mood when
there is a non-subject topic or WH-phrase in Comp.
\emph{Realis} is the mood when there is a subject topic
or WH-phrase.


  Video: PHP, convert latex to pdf

Monday, September 22, 2014

Add knockout.js in CodeIgniter

CodeIgniter - PHP Model-View-Controller (MVC), can be downloaded from:

knockout.js - JavaScript (MVC) can be downloaded from

Create assests/js folder and copy knockout library  knockout-3.2.0.js to this folder.
Under application/controllers, create test.php
class Test extends CI_Controller {
    public function index() {
        echo '<h2>Add knockout.js in CodeIgniter</h2>';

Under applications/views folder, create test.php
<!DOCTYPE html>
<html lang="en">
<p>First name: <strong data-bind="text: firstName"></strong></p>
<p>Last name: <strong data-bind="text: lastName"></strong></p>

<p>First name: <input data-bind="value: firstName" /></p>
<p>Last name: <input data-bind="value: lastName" /></p>

<p>Full name: <strong data-bind="text: fullName"></strong></p>

<script type="text/javascript" src="<?php echo base_url('assets/js/knockout-3.2.0.js') ?>"></script>
<script type="text/javascript" src="<?php echo base_url('assets/js/inputname.js') ?>"></script>

Under assests/js folder, create inputname.js
// Here's my data model
// This is a simple *viewmodel* - JavaScript that defines the data and behavior of your UI
function AppViewModel() {
    this.firstName = ko.observable("Jiansen");
    this.lastName = ko.observable("Lu");

    this.fullName = ko.computed(function() {
        return this.firstName() + " " + this.lastName();   
    }, this);
// Activates knockout.js
ko.applyBindings(new AppViewModel());

My case, run: (I create a parent folder MVC0
Video: Add knockout.js in CodeIgniter

Monday, August 4, 2014

Create a purge tab in Media Wiki

To purge a page in media wiki, add ?action=purge to the URL of the page you are viewing. For example:

  It is better to create a purge tab . To create a purge tab. frist create a Purge directory under extensions directory, then create file Purge.php under Purge directory.

In LocalSettings.php, my case
require_once "$IP/extensions/Purge/Purge.php";
In Purge.php, my case
 * An extension that adds a purge tab on each page
cense GNU General Public License 2.0 or later

$wgExtensionCredits['other'][] = array(
        'path' => __FILE__,
        'name' => 'Purgepage',
        'descriptionmsg' => 'purge-desc'

$dir = dirname( __FILE__ ) . '/';
$wgHooks['SkinTemplateNavigation::Universal'][] = 'PurgeActionExtension::contentHook';

class PurgeActionExtension{
        public static function contentHook( $skin, array &$content_actions ) {
                global $wgRequest, $wgUser;

                // Use getRelevantTitle if present so that this will work on some special pages
                $title = method_exists( $skin, 'getRelevantTitle' ) ?
                        $skin->getRelevantTitle() : $skin->getTitle();
                if ( $title->getNamespace() !== NS_SPECIAL && $wgUser->isAllowed( 'purge' ) ) {
                        $action = $wgRequest->getText( 'action' );

                        $content_actions['actions']['purge'] = array(
                                'class' => $action === 'purge' ? 'selected' : false,
                                'text' =>  'purge' ,
                                'href' => $title->getLocalUrl( 'action=purge' )

                return true;

A purge tab will be added in submenu of down arrow at the top menu globally.
Video: Create a purge tab in  Media Wiki

Install Media wiki in localhost

Media Wiki is a Wiki software based on PhP and MySSQL, download link

You will get MediaWiki 1.23.2.tar.gz.
Using 7-zip to unzip to get tart file, unzip again to produce   mediawiki-1.23.2/mediawiki-1.23.2
directory, rename it to mediawiki-1.23.2/wiki and copy wiki directory to c::/xampp/htdocs/
assuming you have installed xampp.

1) Using PHpMyAdmin to create database wiki, user name wiki, password for example jiansen
2) run
following instruction step by step. After installation complete, copy LocalSettings.php to wiki home directory
You can change the configuration based on this file
3) After installation complete
to create a new page, search the page then create
4) To edit sidebar
5) To create a template, for example Purgecontent
To use Purgecontent {{ Purgecontent}}
Video:  Install Media wiki in localhost

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
  `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`)
INSERT INTO `user` (`id`, `username`, `pwd`, `email`, `lasttime`, `term`) VALUES
(1, 'admin', '123456', '', '2014-07-01', 'summer'),
(2, 'jiansen', '123456', '', '2014-02-03', 'spring'),
(3, 'tom', '333333', '', '2014-10-09', 'winter');

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

1) SELECT in control flow in MySQL
     WHEN MONTH(DATE(lasttime))>= 1 AND MONTH(DATE(lasttime))<=4 THEN
     WHEN MONTH(DATE(lasttime))>= 5 AND MONTH(DATE(lasttime))<=8 THEN
     WHEN MONTH(DATE(lasttime))>= 9 AND MONTH(DATE(lasttime))<=12 THEN
     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
     WHEN MONTH(DATE(lasttime))>= 5 AND MONTH(DATE(lasttime))<=8 THEN
     WHEN MONTH(DATE(lasttime))>= 9 AND MONTH(DATE(lasttime))<=12 THEN

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:
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

Example code:
<!DOCTYPE html>
    <title>jQuery Sticky plugin</title>
    <link rel="stylesheet" href="style.css">
  <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>        
     <script src="" type="text/javascript"></script>
  <script  src="jquery.sticky.min.js" type="text/javascript"></script>
    <script type="text/javascript">
   //         $("#hdr3").sticky({bindWith: '#d1'});
    //        $("#hdr4").sticky();

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

    width: inherit;
    margin: 0px auto;

    width: 100%;


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

    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;


    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);

        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 > && windowScrollTop < + self.elHeight)
        self.$stickyEl.css('position', 'fixed');
        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();

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

            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);


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:
  $("tr:even").css("background-color", "lightblue");

<style type="text/css">
 tr:nth-child(2n) { background-color: #FFEBCD; }
There is a small difference between jQuery and CSS.  When counting tr row number, jQuery includes th. Full code example:
<!DOCTYPE html>
  $("tr:even").css("background-color", "lightblue");
/*tr:nth-child(2n) { background-color: lightblue; }*/
border:1px solid black;
h1 {text-align:center}
<h1>Change row background color alternatively in CSS or jQuery</h1>
<table style="width:100%">

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
        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:
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
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


Thursday, June 26, 2014

Example of MySQL foreign key constraint in relational database design

In relational database design, some of tables have several foreign keys, which point to parent tables.
When the rows in  parent tables  are deleted or updated, what is the behavior of child tables?
Are they deleted or updated accordingly, or et to NULL, or reject the parent table delete/update?
Suppose we have users table:
user_id int NOT NULL,
user_name varchar(45) NOT NULL,
PRIMARY KEY (user_id)

We have another table  pictures,  user_id is foreign key which is primary key in table users.
Using foreign key to prevent  insert user_id in table pictures which is invalid in table users.
We can use  naming of foreign key constraint, which can be used in drop foreign key.

pic_id  int NOT NULL,
user_id int NOT NULL,
PRIMARY KEY (pic_id),
CONSTRAINT fk_pictures FOREIGN KEY (user_id) REFERENCES users(user_id)

 Now we can drop the FOREIGN KEY constraint using the following SQL:
ALTER TABLE pictures DROP FOREIGN KEY fk_pictures

When the primary key is updated or deleted on parent table, what is the behavior of the child table?
 We have four options:

CASCADE: automatically delete or update the child table when the parent table is deleted or rejected
SET NULL: the foreign key table is set NULL  child table when the parent table is deleted or rejected
RESTRICT:  Rejects the delete or update operation for the parent table

('1', 'jiansen'), ('2', 'andy')

INSERT INTO `pictures`  VALUES('1', '1')
will be OK
INSERT INTO `pictures`  VALUES('2', '3')
will fail due to that   user_id=3 is not in users table.

Video:  MySQL foreign key constraint

Monday, June 9, 2014

Run Linux command asking input or password in background

When we run a Linux command in background such as
scp test.sql &
or  ignore the hangup signal
nohup scp test.sql &
We can not input  password.
To make an input for  background job, First we use
scp test.sql
input password, then type Ctrl +Z to suspend the job
then type
to put it in background.
To list all process
ps aux
To list process containing scp and find process id
ps aux |grep scp
To kill a process
kill processid
To force to kill, this is strong:
kill -9 processid

Friday, June 6, 2014

Install PHP ibm_db2 extension in Linux (redHat)

Install PHP ibm_db2 extension in Linux (redHat) to make sb2_connect function worinkg in Linux:
1) check Linux version
uname -a
 uname -m
2) Go to ibm db2 download website to Download Data Server Driver Package

fix pack: DSClients-linuxx64-dsdriver-

IBM Data Server Driver Package (Linux/x86-64 64 bit) V10.5 Fix Pack 3
This is for PHP5.3
3) sudo mkdir /opt/ibm
cd /opt/ibm

download v10.5fp3_linuxx64_dsdriver.tar.gz

and upload to /opt/ibm
4) sudo tar -xvf v10.5fp3_linuxx64_dsdriver.tar.gz
cd /opt/ibm/dsddriver
sudo chmod 755 installDSDriver
5) Install ksh
sudo yum install ksh
6) Install ibm_db2 driver
sudo ksh installDSDriver
7) To make  phpize working which is needed for sudo pecl install ibm_db2
sudo yum install php-devel
8) sudo pecl install ibm_db2
export IBM_DB_HOME=/opt/ibm/dsdriver
sudo pecl install ibm_db2
9) in db2 install directory message, enter
10) return result
Build process completed successfully
Installing '/usr/lib64/php/modules/'
install ok: channel://
configuration option "php_ini" is not set to php.ini location
You should add "" to php.ini

11)  locate php.ini
 sudo vi  /etc/php.ini
13) Restart Apache
 sudo service httpd restart


Thursday, June 5, 2014

PHP connect IBM db2 database in XAMPP

php_ibm_db2.dll is needed for PHP db2_connect function to connect to IBM db2 database.
1) Go to (for Windows)
2) Click PHP 5.4._
Latest xampp is PHP5.5, but latest php_ibm_db2.dll is for PHP5.4
I have to install two version of XAMPP, one is PHP5.5, another PHP5.4 for php_ibm_db2.dll
3) Download
and unzip
4) Copy php_ibm_db2.dll to php/ext/ directory
5) In php.ini, add
6)Restart Apache in XAMPP
7) My PHP test code to connect to IBM db2 database, replace to your database name, user name and password
//db2 express c  (v10.5) in local
$database = "SAMPLE";
$user = "db2admin";
$password = "admin123";

$conn = db2_connect($database, $user, $password);

if ($conn) {
echo "Connection succeeded.";
else {
echo "Connection failed.";

For Linux:
Video: PHP connect IBM db2 database in XAMPP

Tuesday, June 3, 2014

Linux, automatically backup MySQL database daily

Backup MySQL database daily using cron job
1) create a bk directory under home directory, in my case /home/jiansen,
cd bk
2) Create a shell scrip
mysqldump  -u root -pmypass mydb | gzip >MySQLDB_`date +"%Y%m%d"`.sql.gz
replace root,  mypass, mydb to your databse username, password and database name
date +"%Y%m%d
is shell comand to produce year, month and day, for example  20140603
example output
3) Create a cron job
 crontab -e
30 23 * * * /home/jiansen/bk/ 2>&1>>/home/jiansen/bk/mysqbackup.log

each night  23:30pm run  /home/jiansen/bk/ and dump errors
to logfile 
4) to list cron job
  crontab -l
5) To remove cron job
  crontab -r
Video: Linux, automatically backup MySQL database daily

Thursday, May 29, 2014

Install and run PHP CodeIgniter under XAMPP in Windows 7

CodeIgniter is a powerful PHP framework 
based on the Model-View-Controller development pattern
Download page:
Documentation page:

1) Download and unzip to
note I installed XAMPP under C:\xampp
2) Run
welcome page should appear.
3) Create test.php under
4) configure database\application\config\database.php
In mycase, conect to localhost test database with root and no password
$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'root';
$db['default']['password'] = '';
$db['default']['database'] = 'test';

Example code:
5) controller test.php

class Test extends CI_Controller {
    public function index() {
        $data = array();
        $user = new Users();
        $data['message']= $user->login();
        echo '<tt><pre>' . $data['message'] . '</pre></tt>';
        echo '<h2>This is a test</h2>';

6) model users.php
Class Users extends CI_Model

    private $_username;
    private $_password;

    function __construct()
    public function setUsername($value)
        $this->_username = $value;
    public function setPassword($value)
        $this->_password = $value;
   public function login()
        $username = $this->_username;
        $password = $this->_password;
        $this -> db -> select('id, username, password');
        $this -> db -> from('users');
        $this -> db -> where('username', $username);
        $this -> db -> where('password', $password);
        $query = $this -> db -> get();
         if($query -> num_rows() == 1)
           return "user name and pasword matched";
         return "user name and pasword not matched";

7) view test.php
echo "from view ".$message;

Video: Install and run PHP  CodeIgniter under XAMPP in Windows 7