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
code:
index.php
 <script src="http://code.jquery.com/jquery-latest.min.js"></script>
<script>
$(document).ready(function(){
$('select.status').on('change',function () {
        var decision = $(this).val();
        var id = $('td.myid').html();
        alert(decision);
        alert(id);
        $.ajax({
                 type: "POST",
                 url: "saveDecision.php",
                 data: {decision: decision, id: id },
                 success: function(msg) {
                     $('#autosavenotify').text(msg);
                 }
      })
  });
});
</script>
<h1>Ajax autosave</h1>
<div id="autosavenotify"></div>
<table width="200px">
    <tr>
    <td class="myid">
        1
    </td>   
        <td><select class="status" >
          <option value='-1'>--Select--</option>
    <option value='approve'>Approve</option>
    <option value='reject'>Reject</option>
    <option value='pending'>Pending</option>
    </select></td>
    </tr>
</table>

SaveDecision.php
<?php
if (isset($_POST['id'])) {
$con=mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("test", $con);
$decision=$_POST['decision'];
$id=$_POST['id'];
    $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
CREATE TABLE IF NOT EXISTS `decision` (
  `id` int(8) NOT NULL,
  `select_result` varchar(40) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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
http://code.jquery.com/jquery-latest.min.js
2) JS Associate Array, data may come from ajax post
  t={
     '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>
<html>
<head>
<script src="http://code.jquery.com/jquery-latest.min.js"></script>
</head>
<style>
table {
    border-collapse: collapse;
}
table, th, td {
    border: 1px solid black;
}
</style>
<script>
function myFunction() {
   var taList = {};
   taList={
     '1': {id:1, name:'Andy'},
     '2':{id:2,name:'Jiansen'},
     '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');
}
</script>

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

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:
"Forbidden
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
</Files>
3) Example 3, deny all access
<Files "*">
    Order deny,allow
    Deny from all
</Files>
You will get
"Forbidden
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
.htaccess: 
AuthType Basic
AuthName "Restricted access"
AuthUserFile  "/var/www/html/htaccess_test/.htpasswd"
Require valid-user
<Files ~ "^\.ht">
  Order allow,deny
  Deny from all
</Files>

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
<?php
    echo '<h1> This is  a test for cron job</h1>';
?>

 2) Create a shell script test.sh
#! /bin/sh
#wget -q --no-check-certificate -O- "http://swf.site40.net/crontest/test.php"
php test.php
3) run following in shell to make test.sh executable
chmod +x test.sh
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 test.sh
<Files "*">
    Order deny, allow
    Deny from all
</Files>

5) crontab -e
30 23 * * * /home/jiansenl/crontest/test.sh
each night 23:30pm to  run test.sh
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
https://code.google.com/a/apache-extras.org/p/phpmailer/downloads/list
I download PHPMailer_5.2.4.zip and use winrar to unzip and put it in
C:\xampp\htdocs\phpmailertest

2)Configuration php.ini to enable openssl
In C:\xampp\php\php.ini
extension=php_openssl.dll
 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.
<?php
error_reporting(E_ALL);
require("PHPMailer_5.2.4/class.phpmailer.php");
$mail = new PHPMailer();
$mail->IsSMTP(); // set mailer to use SMTP
$mail->SMTPDebug  = 2;
$mail->From = "jiansentest@gmail.com";
$mail->FromName = "Jiansen";
$mail->Host = "smtp.gmail.com"; // 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 = "jiansentest@gmail.com"; // SMTP username
$mail->Password = "mypassword"; // SMTP password

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

$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
 http://localhost/phpmailertest/
We got
SMTP -> FROM SERVER:220 mx.google.com ESMTP uf6sm10363353pac.16 - gsmtp
SMTP -> FROM SERVER: 250-mx.google.com at your service, [207.81.4.41] 250-SIZE 35882577 250-8BITMIME 250-AUTH LOGIN PLAIN XOAUTH XOAUTH2 PLAIN-CLIENTTOKEN 250-ENHANCEDSTATUSCODES 250-PIPELINING 250-CHUNKING 250 SMTPUTF8
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:
[sendmail]
smtp_server=smtp.gmail.com
smtp_port=587
error_logfile=error.log
debug_logfile=debug.log
auth_username=****@gmail.com
auth_password=*******
force_sender=****@gmail.com
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
 <?php

    $message = "The mail message was sent with the following mail";
    $headers = "From: youremail@gmail.com";
    mail("youremail@gmail.com", "Testing", $message, $headers);
    echo "Test message is sent to youremail@gmail.com....<BR/>";
   
?>
Change youremail@gmail.com 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. 
reference:
http://jiansenlu.blogspot.ca/2011/08/php-send-email-using-phpmailer-and.html

For Linux, refer to:
https://www.digitalocean.com/community/tutorials/how-to-use-gmail-or-yahoo-with-php-mail-function

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:
https://www.tug.org/texlive/acquire-netinstall.html

1) For Windows, run
install-tl-windows.exe

default location
C:\texlive\2014

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:
https://fedorahosted.org/liberation-fonts/
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 http://mirror.ctan.org/systems/texlive/tlnet/install-tl-unx.tar.gz
$  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 http://www.pdflabs.com/tools/pdftk-the-pdf-toolkit/pdftk-2.02-1.el6.x86_64.rpm
$ 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
 http://www.tug.org/texlive/acquire-netinstall.html
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.
Note;
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

index.php
<?php
        ob_start();
        include 'latex_template.php';
        $outputData .= ob_get_contents();
        ob_end_clean();
        $texFile = tempnam(sys_get_temp_dir(), 'test');
        $base = basename($texFile);
       
        rename($texFile, $texFile.".tex");
        $texFile .= ".tex";
        file_put_contents($texFile, $outputData);
        chdir(dirname(realpath($texFile)));

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

latex_template.php
\documentclass[12pt]{article}
\usepackage{lingmacros}
\usepackage{tree-dvips}
\begin{document}

\section*{Notes for My Paper}

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

{\small
\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}).

{\small
\enumsentence{Structure of A$'$ Projections:\\ [2ex]
\begin{tabular}[t]{cccc}
    & \node{i}{CP}\\ [2ex]
    \node{ii}{Spec} &   &\node{iii}{C$'$}\\ [2ex]
        &\node{iv}{C} & & \node{v}{SAgrP}
\end{tabular}
\nodeconnect{i}{ii}
\nodeconnect{i}{iii}
\nodeconnect{iii}{iv}
\nodeconnect{iii}{v}
}
}

\subsection*{Mood}

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.

\end{document}

  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:
https://ellislab.com/codeigniter

knockout.js - JavaScript (MVC) can be downloaded from
http://knockoutjs.com/

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

Under applications/views folder, create test.php
<!DOCTYPE html>
<html lang="en">
<head>
</head>
<body>
<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>
</body>
</html>

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
http://localhost/MVC/CodeIgniter/index.php/test
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:
 http://example.org/wiki/Main_Page?action=purge

  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
C:\xampp\htdocs\wiki\LocalSettings.php
add
require_once "$IP/extensions/Purge/Purge.php";
In Purge.php, my case
C:\xampp\htdocs\wiki\extensions\Purge\Purge.php
<?php
/**
 * An extension that adds a purge tab on each page
 *
cense http://www.gnu.org/copyleft/gpl.html 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
http://www.mediawiki.org/wiki/Download

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
localhost/wiki/mw-config/index.php
following instruction step by step. After installation complete, copy LocalSettings.php to wiki home directory
C:\xampp\htdocs\wiki\LocalSettings.php
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
 http://localhost/wiki/index.php?title=MediaWiki:Sidebar&action=edit
5) To create a template, for example Purgecontent
http://localhost/wiki/index.php/Template: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
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

 

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:
CREATE TABLE users
(
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.
CREATE TABLE pictures

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)
ON DELETE CASCADE ON UPDATE CASCADE
)

 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:

  RESTRICT | CASCADE | SET NULL | NO ACTION
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
NO ACTION: the same as RESTRICT

 INSERT INTO `users`  VALUES
('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.
Reference:
http://www.w3schools.com/sql/sql_foreignkey.asp
http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html

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 jiansen@example.com:/home/jiansen &
or  ignore the hangup signal
nohup scp test.sql jiansen@example.com:/home/jiansen &
We can not input  password.
To make an input for  background job, First we use
scp test.sql jiansen@example.com:/home/jiansen
input password, then type Ctrl +Z to suspend the job
then type
bg
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
or
 uname -m
return
x86_64
2) Go to ibm db2 download website to Download Data Server Driver Package
 https://www-304.ibm.com/support/docview.wss?uid=swg27016878

fix pack: DSClients-linuxx64-dsdriver-10.5.0.3-FP003

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
/opt/ibm/dsdriver
10) return result
Build process completed successfully
Installing '/usr/lib64/php/modules/ibm_db2.so'
install ok: channel://pecl.php.net/ibm_db2-1.9.5
configuration option "php_ini" is not set to php.ini location
You should add "extension=ibm_db2.so" to php.ini
/usr/lib64/php/modules/ibm_db2.so

11)  locate php.ini
/etc/php.ini
12)
 sudo vi  /etc/php.ini
add
extension=/usr/lib64/php/modules/ibm_db2.so
13) Restart Apache
 sudo service httpd restart

Reference:
http://www.php.net/manual/en/ibm-db2.installation.php

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)
http://sourceforge.net/projects/db2mc/files/IBM_DB2%20PHP%20Driver%20for%20Windows/
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
php_ibm_db2-1.9.5-5.4-ts-vc9-x86.zip
and unzip
4) Copy php_ibm_db2.dll to php/ext/ directory
5) In php.ini, add
extension=php_ibm_db2.dll
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
<?php
//db2 express c  (v10.5) in local
$database = "SAMPLE";
$user = "db2admin";
$password = "admin123";

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

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

?>
For Linux:
reference:
http://www.php.net/manual/en/ibm-db2.installation.php
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.sh
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
 MySQLDB_20140603.sql.gz
3) Create a cron job
 crontab -e
30 23 * * * /home/jiansen/bk/MySQLdump.sh 2>&1>>/home/jiansen/bk/mysqbackup.log

each night  23:30pm run  /home/jiansen/bk/MySQLdump.sh 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:
http://ellislab.com/codeigniter
Documentation page:
http://ellislab.com/codeigniter/user-guide/toc.html

1) Download CodeIgniter_2.1.4.zip and unzip to
C:\xampp\htdocs\xampp\codeigniter\
note I installed XAMPP under C:\xampp
2) Run
http://localhost/xampp/codeigniter/
welcome page should appear.
3) Create test.php under
application/controller
run
http://localhost/xampp/codeigniter/index.php/test
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
C:\xampp\htdocs\xampp\codeigniter\application\controllers\test.php
<?php

class Test extends CI_Controller {
    public function index() {
        $data = array();
        $this->load->model('Users');
        $user = new Users();
        $user->setUsername("jiansen");
        $user->setPassword("pass1");
        $data['message']= $user->login();
        echo '<tt><pre>' . $data['message'] . '</pre></tt>';
       
        //$this->load->view('magazines');
        echo '<h2>This is a test</h2>';
        $this->load->view('test',$data);
    }
}

?>
6) model users.php
C:\xampp\htdocs\xampp\codeigniter\application\models\users.php
<?php
Class Users extends CI_Model
{

    private $_username;
    private $_password;

    function __construct()
    {
        parent::__construct();
    }
    public function setUsername($value)
    {
        $this->_username = $value;
    }
   
    public function setPassword($value)
    {
        $this->_password = $value;
    }
   public function login()
   {
        $this->load->database();
        $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";
       }
       else
       {
         return "user name and pasword not matched";
       }
    }   
}
?>

7) view test.php
C:\xampp\htdocs\xampp\codeigniter\application\views\test.php
<?php
echo "from view ".$message;
?>

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