Tuesday, August 30, 2011

Select from multiple tables in MySQL


Below is the example using full join and left join in MySQL.

Suppose we have two tables: members and committee.
table members (primary key member_id)
--------------------------------------
member_id       member_name
1                         Joe
2                         Andy
3                        Tommy

table committee (primary key committee_id)
----------------------------------------------
committee_id  member_id  committee_name  
1                           2                   Research                
2                           2                  Education               
3                          3                    Research               
Full join
SELECT  members.*, committee.*  FROM members, committee;
Result:
------------------------------------------------------------------------------------------------------
member_id       member_name  committee_id  member_id  committee_name 
1                         Joe             1                           2                   Research      
2                         Andy                     1                           2                   Research      
3                        Tommy                  1                           2                   Research      
1                         Joe                       2                           2                  Education             
2                         Andy                     2                           2                  Education             
3                        Tommy                   2                           2                  Education             
1                         Joe                        3                          3                    Research    
2                         Andy                     3                           3                    Research 
3                        Tommy                  3                           3                    Research 

SELECT  members.*, committee.*  FROM members, committee WHERE members.committee_id=committee.committee_id;
Result:
------------------------------------------------------------------------------------------------------
member_id       member_name  committee_id  member_id  committee_name  
 2                         Andy                     1                           2                   Research      
2                         Andy                     2                           2                  Education             
3                        Tommy                  3                           3                    Research 

Left join
SELECT members.*, committee.*  from members LEFT JOIN committee ON members.committee_id=committee.committee_id;
Result:
(A left join produces output for every row in members, whether or not  committee matches it. )
------------------------------------------------------------------------------------------------------
member_id       member_name  committee_id  member_id  committee_name
 1                         Joe                        NULL                NULL               NULL
 2                         Andy                     1                           2                   Research      
2                         Andy                     2                           2                  Education             
3                        Tommy                  3                           3                    Research 

Monday, August 29, 2011

Moving elements in PhotoShop


I want to move up Global Simulation Network in Healthcare in the following log  up.




In Photoshop,
1. Using the Marquee tool, right click mouse to select
Rectangular Marquee Tool, select the area of my image,
2. Click Move Tool (V), move the select area to center.
3. save new image. Below is the new image:


Wednesday, August 17, 2011

Flash ActionScript3 -- add snow falling


The steps to produce following animation (adding snow falling in Christmas card):
1) Click Adobe Flash CS3 Professional:
2) File->New ->ActionScript 3
3)  Rename “layer 1″ to “card”. On this layer import the image of your choice, you can take one www.xmas-wallpapers.com
4)Create a new blank keyframe, draw the shape of your snowflake and convert it to a movie clip. In property, select export for actionscript, export for first time.
Name Snowflake, class Snowflake, Base class: flash.display.MovieClip  Remove the snowflake keyframe from the stage, which is already in the library.
5).
Create a new “actions” layer. Press F9 to import following actionscript

addEventListener(Event.ENTER_FRAME, createSnow);

function createSnow(event:Event):void{
    var snowflake : Snowflake = new Snowflake();
    snowflake.x = Math.random() * stage.stageWidth;
    snowflake.y = 0 ;
    addChild(snowflake);
    snowflake.addEventListener(Event.ENTER_FRAME, moveSnowflake);
}

function moveSnowflake(e:Event):void{
    if(e.target.y < stage.stageHeight){
        e.target.y += 10 + Math.random() * 5 ;
        e.target.alpha -= 0.015;
        e.target.scaleX = e.target.scaleY -= 0.01;
       
    }
    else{   
        e.target.removeEventListener(Event.ENTER_FRAME,moveSnowflake);
        removeChild(e.target as Snowflake);
    }
}
6) Control + Test Movie (ctrl+enter) to test movie. Export movie to swf file.

Tuesday, August 16, 2011

PHP - Monitoring server down


I wrote a script test_cron.php below to monitor the web servers. If a web server is down, an email notification will be sent to me. A windows task scheduler is used.
To start task scheduler in Windows 7:
1. Go to Start > Programs > Accessories > System Tools > Task Scheduler
2. Click "Create Task" in right panel.
3. Click "General" to set task name; click "trigger->new" to set how often task happens; click "action->new", Program\Script: "C:\Program Files (x86)\Mozilla Firefox\firefox.exe" and Add argument: http://localhost:8082/test_cron.php.
4, Cilck OK.

My test_cron.php:
$servers = array(1 => "cesei.org",
2 => "acseper.facs.org");
$i=0;
foreach($servers as $key => $value){

$site = $value;
$port = '80';
$check = fsockopen( $site, $port, $errno, $errstr, 10);
$downurl=" ";
if (!$check){
$i=$i+1;
$downurl .= $value.', ';
}
}


$mail = new PHPMailer();

$mail->IsSMTP(); // set mailer to use SMTP
$mail->From = "myname@gmail.com";
$mail->FromName = "myname";
$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 = "myname@gmail.com"; // SMTP username
$mail->Password = "mypassword"; // SMTP password
$mail->AddAddress("myname@gmail.com", "myname"); //replace myname and mypassword to yours
$mail->AddReplyTo("myname@gmail.com", "myname");
$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 = 'Server '.$downurl.' down';
$mail->Body = 'Server '.$downurl.' down';

if($i>0){
if($mail->Send()) {echo "Send mail successfully";}
else {echo "Send mail fail";}
}
else echo "Server OK";
?>

PHP- send email using PHPMailer and gmail for PHP5



PHPMailer for PHP 5 can be downloaded from
http://sourceforge.net/projects/phpmailer/files/phpmailer%20for%20php5_6/PHPMailer%20v5.1/PHPMailer_v5.1.zip/download

In this example, I extract the PHPMailer to my directory PHPMailer_v5.1. To use gmail as smtp server in PHPMailer, you need to create a gmail account and replace my user name and password
to yours. The smtp host in gmail is smtp.gmail.com. The port is 465 and smtp secure is ssl.

My Code:

<?php
require("PHPMailer_v5.1/class.phpmailer.php");

$mail = new PHPMailer();

$mail->IsSMTP(); // set mailer to use SMTP
$mail->From = "myname@gmail.com";
$mail->FromName = "myname";
$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 = "myname@gmail.com"; // SMTP username
$mail->Password = "mypassword"; // SMTP password
$mail->AddAddress("myname@gmail.com", "myname"); //replace myname and mypassword to yours
$mail->AddReplyTo("myname@gmail.com", "myname");
$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";}

?>

Monday, August 15, 2011

PHP- free web server monitoring using phpsysinfo


phpSysInfo is a PHP script that displays information about the host being accessed. It will displays things like Uptime, CPU, Memory, LM Sensors, SCSI, IDE, PCI, Ethernet, Floppy, and Video Information.

You can download phpsysinfo for free in:
http://phpsysinfo.sourceforge.net/
The installation is quite simple. unzip phpsysinfo-3.0.13.tar.gz and copy directory
phpsysinfo to your web server. Rename conf.php.new to config.php and run yourservername/phpsyssinfo. You don't need MySQL database.

Below is the web server information  from my localhost using phpsysinfo:


Thursday, August 11, 2011

PHP - access to remote folder


When I run my php code test_remote.php
<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);
$dir = "\\\\10.29.227.65\\uploads";
$dh = opendir($dir);
$i = 0;
while (false!== ($filename = readdir($dh)))
{
print $filename."<br />";

if($filename<>"." and $filename<>"..")
{
$array[$i] = $filename;
$i++;
}
}
?>
I got the following error
Warning: opendir(
\\10.29.227.65\uploads) [function.opendir]: failed to open dir: No error in C:\gnsh\test_remote.php on line 8.

Firstly, I check  the folder \\
10.29.227.65\uploads can be accessed by my account (administrator).
 

I realize that the APACHE service must be set-up for my account (administrator). If no user is specified, APACHE uses an anonymous user and this is where the problem/error message is coming from. Go in control panel->administrative tools->services.  Right click on that and pop up the properties screen. In the tab LOG ON, not select local system account, but select this account and put my account name (administrator) and password. Restart Apache.

Wednesday, August 10, 2011

Add password protection for your files or folders in Linux


Type the fol following command to password protect file testfile:
zip -e -r testfile testfile
Then type your password and a new file testfile.zip is produced.

To unzip your files or folders:
upzip testfile.zip
enter the password you set.

Create a PayPal donation button


I create a PayPal donation button as follows:





It is quite straightforward to create a PayPal donation button. Follow the instruction:
https://www.paypal.com/us/cgi-bin/?cmd=_donate-intro-outside
But you may need to upgrade your PayPal personal account to to a Premier or Business account. It is for free. But you may need to pay a small fee when you receive payments with a Premier or Business account. For purchase, it is free to pay, but 1.9% to 2.9% + $0.30 CAD for getting paid.

Upgrade your paypal personal account to a Premier or Business account for free


If you want to setup donation button using PayPal, you may need to upgrade your PayPal personal account to a Premier or Business account. It is for free. But you may need to pay a small fee when you receive payments with a Premier or Business account. For purchase, it is free to pay, but 1.9% to 2.9% + $0.30 CAD for getting paid.

To upgrade your PayPal account,you need to sign in to PayPal  and click the Upgrade Account link. You can choose a Premier or Business account.

Tuesday, August 9, 2011

Import and export certificate in FireFox, transfer certificates in different computers



During your renewing S/MIME authenticate certificate, you can save your certificate
as p12 file with a simple password. You can also export your certificate,
In Firefox:
Select "Tools"->"Options" , then select "Advanced" -> "Encryption" -> "View Certificates", choose the "Your Certificates" tab and locate your client certificate from the list. The certificate will be listed under StartCom. Select the certificate and click on "Backup", choose a name for this backup file, provide a password and save it at a known location.


To restore the certificate in another computer, in FireFox:
Select "Tools"->"Options" , then select "Advanced" -> "Encryption" -> "View Certificates", choose the "Your Certificates" tab and click import with your p12 certificate file location.

Renew SSL certificate from StartSSL



1) The web server SSL certificates from startssl is free for class 1 and needs to be renewed every year. Two weeks before SSL certificates expiration, you will receive an email notification. But you need to validate your email first as email validation only for 30 days.
(Go to control panel in www.startssl.com, click Authenticate, click validations Wizard, and click email validation).

2) You may also need to renew S/MIME authenticate certificate, which is only valid for one year. If you fail to renew, you may not enter you your account.
(Go to control panel in www.startssl.com, click Authenticate, click Certificate Wizards, and click renew S/MIME authenticate certificate).

3) Now you can renew your web server SSL.
Go to control panel in www.startssl.com, click Authenticate, click Certificate Wizards, and click Web Server SSL/TSL Certificate, following the instruction,
copy and save in ssl.key. run the following command
openssl rsa -in ssl.key -out ssl.key
You can also click Decrypt Private Key in Toolbox, which is identical to the above command. If you have a copy and paste error (for example I missed a dash - at the first line), you will get the following error message:
"unable to load Private Key
15632:error:0906D06C:PEM routines:PEM_read_bio:no start line:pem_lib.c:647:
Expecting: ANY PRIVATE KEY"
In this case, you have to create a new certificate by either requesting revocation (which isn't free of charge) or use a different sub domain.


4) click Retrieve Certificate in Toolbox to get ssl.crt
Click StartCom Root CA (PEM encoded) in StartCom CA Certificates (left menu) to get ca.pem;
Click Class 1 Intermediate Server CA in StartCom CA Certificates (left menu) to get sub.class1.server.ca.pem.

5) Install your  ssl.key, ssl.crt, ca.pem and sub.class1.server.ca.pem in your web server ssl directory and restart your web server.

6) To check your ssl in your web server, go to SSL checker and input your https website or left click your mouse in your browser with your https website, click more information and view certificate.