Increase MySQL SELECT speed in complicated statement

I have following MySQL statement using 4 SELECT loops:
  $sql = "SELECT subject_id,study_code,active,reminders FROM subjects WHERE subject_id IN(
               SELECT subject_id FROM review_slot_subjects WHERE review_slot_id
                  IN (SELECT review_slot_id FROM review_slots WHERE project_id=$pid AND review_slot_id
                     IN (SELECT review_slot_id FROM review_slot_reviewers WHERE member_id=$rid))) ORDER BY study_code";
      $result = mysql_query($sql, $db);
It takes  about 2 minutes to finish execution.
Change the MySQL statement into two steps: one is three SELECT, another one SELECT
//step 1
$sql = "SELECT subject_id FROM review_slot_subjects WHERE review_slot_id
                  IN (SELECT review_slot_id FROM review_slots WHERE project_id=$pid AND review_slot_id
                     IN (SELECT review_slot_id FROM review_slot_reviewers WHERE member_id=$rid))";
        $result = mysql_query($sql, $db);
       $subject_id_array=array();
    while($row = mysql_fetch_assoc($result))  $subject_id_array[]=$row['subject_id'];
 //step 2
    $matches = implode(',',$subject_id_array);        
    $sql = "SELECT subject_id,study_code,active,reminders FROM subjects WHERE subject_id IN(
               $matches) ORDER BY study_code";
      $result = mysql_query($sql, $db);

The MySQl execution time is reduced to few seconds.

Mac OSX Snow Leopard 10.6.3 CD in Virtual Box in Windows 7

Read some articles about install Mac OSX Snow Leopard 10.6.3 in Virtual Box in Windows 7 using official CD. And make some summary:
1)iboot.iso  from
http://www.tonymacx86.com/downloads.php?do=cat&id=3
2) Oracle VM Virtual Box:
https://www.virtualbox.org/
3) imgburn:
http://www.imgburn.com/index.php?act=download
Following instruction from
http://www.sysprobs.com/install-mac-os-1065-snow-leopard-virtualboxseveral-issues-fixed
http://www.tonymacx86.com/hardware-troubleshooting/112092-ebios-read-error-error-0x0c-block-0x18a96a-sectors-0-a.html
Still errors
EBIOS read error, error 0x0c,
block 0x0 sector 0
 Later reset storage as following:

Then start, iboot is working and switch to Host Drive "D:" (set in SATA port 1, passthrough is checked)
The  error disappeared, and following shown:

But system hanged in "Mac OS X install DVD"

Build Native iOS Apps on Windows Computers using Mobione

Mobione can be downloaded from:
http://www.genuitec.com/mobile/features/
Now anyone can build App Store-ready apps for iPhone, iPad or iPod devices. The best part is you can do it from your existing Windows computer. (Apple requires you to own a Mac, but now you don't!)
Video:Build Native iOS Apps on Windows Computers

MobiOne FAQ:
http://www.genuitec.com/mobile/mobione_faqs.html#dev3

Apple store  requires that your application must be a native iOS application. Use the Build iOS Application wizard in MobiOne to create a native iOS application (.ipa file).

Record your screen with microphone using Hypercam and ezvid for free

There are server software to record screen with microphone: camtasia  (not free), www.screenr.com (free account maximum 5 minutes, save as mp4), ezvid (maximum 45 minutes)  and this one Hypercam (no time limit, free, save as avi).

Hypercam for Windows can be downloaded from:
http://www.hyperionics.com/hc/downloads.asp

HyperCam supports text annotations, sound, and screen notes (great for creating automated software demos and software training!). You can also select Frame rate and compression quality prior to recording your movie.  But HyperCam is not intended for re-recording of other video clips from the screen (e.g. playing in Media Player, RealVideo, QuickTime etc.), but rather for creating regular software presentations, tutorial, demos etc.

ezvid has a lot of nice features, such as insert symbol, text pronunciation, video editing. ezvid can be downloaded from:
 http://www.ezvid.com

Video: How to use ezvid:

Password protect your folders in Windows 7 using "Folder Lock"

You may need to protect your folders in Windows 7 for important document.
You can download free version of  "Folder Lock" from here:
http://www.newsoftwares.net/folderlock/

Folder Lock lets you password-protect files, folders and drives; encrypt your important files on--the-fly; backup them in real-time; protect USB drives and portable devices; shred files and clean history.

Hide some of dropbox folder in Windows 7

When you install dropbox in Windows 7, it will create a dropbox folder in your Windows 7 and sync to dropbox.  You may not want some of your security files stay in your local computer. To hide some of the local dropbox folder in Windows 7, there are two ways:
Method 1:
1) Go to  system tray at the bottom menu of Windows 7, click dropbox  icon,   a menu popup, click gear icon at top right,  click preferences, the click advanced,  click selective sync and choose the folder you want to show in Windows. The unchecked folder will be deleted from your local computer.
2) But local dropbox folder  is still  linked to your dropbox account. You many need to got to preference, and click Accounts and click "unlink to computer"
 Method 2:
1) Log in to your dropbox account,   go to Settings and click Security, find your PC name and
click unlink.  The dropbox folder in local PC is still there, but no more updated. You can delete some of directories which you do not want to show.
Video:  Hide some of dropbox folder in Windows 7

Protect your PC with external flash USB drive using Predator

The Predator software uses a USB drive as a key to lock or unlock your computer by removing it or plugging it in. The software is free to download here:
http://www.predator-usb.com/predator/en/index.php

How it works?

• when you're away from your PC, you simply remove the USB drive:
  once it is removed, the keyboard and mouse are disabled and the screen darkens
• when you return back to your PC, you put the USB flash drive in place:
  keyboard and mouse are immediately released, and the display is restored. 

 Video: Secure your Computer with a USB!

MySQL: copy a row value to a new row and update some of values

Below is PHP code to copy a row value in table projects and update the values of  date_created and name columns: The method is used for copy modules and research projects in PHP projects.
Her we use insert into ...set..
$nl  = "\r\n";
$insert_set    = "INSERT INTO `%1s` SET %2s;".$nl;
$sql_insert = '';
$s1 = '';
$s2 = array();
$sql    = "SELECT * FROM projects WHERE project_id=$project_id";
$result = mysql_query($sql,$db);
if($row = mysql_fetch_assoc($result)) {
   $s1 = 'projects';
   $s2 = array();
   foreach($row as $key=>$value){
      if($key == 'project_id'){
         $s2[] = 'project_id=0';
         continue;
      }
      if($key == 'date_created'){
         $s2[] = "date_created = NOW()";
         continue;
      }
      if($key == 'name'){
         $s2[] = "name ='$project_name'";
         continue;
      }
      $value = addslashes($value);
      $s2[] = "$key='$value'";
   }

   $sql_insert = (vsprintf($insert_values,Array($s1,implode(',',$s2))));
   mysql_query($sql_insert,$db);
   $new_project_id = mysql_insert_id();
}

The  table projects is created as following, projects and project_id are primary key with auto increment:
CREATE TABLE  `research_projects_new1`.`projects` (
  `project_id` mediumint(8) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `owner_id` mediumint(8) unsigned NOT NULL default '0',
  `date_created` datetime NOT NULL default '0000-00-00 00:00:00',
  `date_start` datetime NOT NULL default '0000-00-00 00:00:00',
  `date_end` datetime NOT NULL default '0000-00-00 00:00:00',
  `arms` tinyint(3) unsigned NOT NULL default '0',
  `groups` tinyint(3) unsigned NOT NULL default '0',
  `subjects` smallint(5) unsigned NOT NULL default '0',
  `auto_study_code` tinyint(1) NOT NULL default '1',
  `subject_demographics` text,
  `subject_to_group` enum('ordered','random','manual') NOT NULL default 'ordered',
  `reviewer_to_subject` enum('ordered','random','manual') NOT NULL default 'ordered',
  `reviewer_assignment_rule` enum('subject','arm_group','arm','group') NOT NULL default 'arm_group',
  `subjects_per_review_slot` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`project_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8; 

The MySQL statement is:
INSERT INTO `projects` SET project_id=0,name ='small project COPY',owner_id='37',date_created = NOW(),date_start='2008-07-21 00:00:00',date_end='2008-07-25 00:00:00',arms='1',groups='2',subjects='10',auto_study_code='1',subject_demographics='N;',subject_to_group='manual',reviewer_to_subject='ordered',reviewer_assignment_rule='subject',
subjects_per_review_slot='1'; 

We can also use insert into ...values
Insert into table values and insert into table set in MySQL are identical.
Example

INSERT INTO table_1 (a, b, c) VALUES (1,2,3)
is the same as
INSERT INTO table_1 SET a=1, b=2, c=3

 PHP code to copy a row value to a new row and update some of values
$nl  = "\r\n";
$insert_values = "INSERT INTO `%1s` VALUES(%2s);".$nl;

$sql_insert = '';
$s1 = '';
$s2 = array();
//start with the base entry edu_courses
$sql    = "SELECT * FROM projects WHERE project_id=$project_id";
$result = mysql_query($sql,$db);
if($row = mysql_fetch_assoc($result)) {
   $s1 = 'projects';
   $s2 = array();
   foreach($row as $key=>$value){
      if($key == 'project_id'){
         $s2[] = '0';
         continue;
      }
      if($key == 'date_created'){
         $s2[] = "NOW()";
         continue;
      }
      if($key == 'name'){
         $s2[] = "'$project_name'";
         continue;
      }
      $value = addslashes($value);
      $s2[] = "'$value'";
   }
   $sql_insert = (vsprintf($insert_values,Array($s1,implode(',',$s2))));

   mysql_query($sql_insert,$db);
   $new_project_id = mysql_insert_id();
}

The MySQL statement is:
 INSERT INTO `projects` VALUES(0,'small project COPY','37',NOW(),'2008-07-21 00:00:00','2008-07-25 00:00:00','1','2','10','1','N;','manual','ordered','subject','1');

Find stored passwords in Fireftp ftp client

Fireftp ftp client stores around  quick connections for you, but passwords are hidden. Those passwords can be found in the location as  normal Firefox password location.

1) Go to Tools at the top of Firefox menu, click options, then select  Security,
2) click Saved Passwords:, then click  Show Passwords button
3) Look for ftp under site column, then look for password column, you can see a list of passwords, which are for Fireftp,

It is recommended to use Master password in security setting.
Video:  Find stored passwords in Fireftp ftp client

Find stored passwords in Filezilla ftp client

Filezilla ftp client stores around 8 quick connections for you, but passwords are hidden. Those passwords can be found in Filezilla configuration directory.  For example  in my Windows 7 PC:
open file (replace jiansen to your user name)
C:\Users\jiansen\AppData\Roaming\FileZilla\recentservers.xml
You can see something like this:
<FileZilla3>
    <RecentServers>
        <Server>
            <Host>10.29.227.88</Host>
            <Port>21</Port>
            <Protocol>0</Protocol>
            <Type>0</Type>
            <User>jiansen</User>
            <Pass>my_pass</Pass>
            <Logontype>1</Logontype>
            <TimezoneOffset>0</TimezoneOffset>
            <PasvMode>MODE_DEFAULT</PasvMode>
            <MaximumMultipleConnections>0</MaximumMultipleConnections>
            <EncodingType>Auto</EncodingType>
            <BypassProxy>0</BypassProxy>
        </Server>
    </RecentServers>
</FileZilla3>
The password is stored inside pass tag in recentservers.xml file.

Sometimes Appdata directory is hidden, To unhide this directory,  under \user\jiansen\ (replace jiansen to your user name),  click organize at top left menu,  click folder and search options, click view, select  "show hidden files , folders and drives) and click OK.

Another way to find Appdata folder in Windows 7:
Click Start and in search box enter:
%AppData%
The Appdata directory will show up.

Create a New User with same privileges as root in MYSQL

Sometimes we may need to create a new user with same  privileges as root in MYSQL in case we forget the root password.
To  create a new user jianse  in MySQL with password "mypass"
CREATE USER 'jiansen'@'localhost' IDENTIFIED BY 'mypass';
Sets all simple privileges except GRANT OPTION to new user jiansen
GRANT ALL PRIVILEGES ON * . * TO 'jiansen'@'localhost';
Add GRANT OPTION to new user jiansen
GRANT GRANT OPTION ON * . * TO 'jiansen'@'localhost';
Now user jiansen has same rigth as root in MySQL.

Reference:
Crack mysql root password in several ways
 http://www.sureshkumarpakalapati.in/2011/07/crack-mysql-root-password-in-several.html

Gmail backup and archive to local computers (Windows) and restore utility

You can download your  google data except gmail into zip file via:
https://www.google.com/settings/takeout
You may want to backup and archive your gmail.
Solution one:
1) Backup gmail to Google drive:
reference:
 http://www.skipser.com/p/2/p/archive-backup-gmail-google-drive.html
First open this "Gmail archiver spreadsheet" and select File->Make a Copy.
 Select "Gmail Drive Backup" menu, click "Initialize
 Now select "Gmail Drive Backup" again and click "Start Daily Gmail Backup". You will be asked to enter the Gmail Label you want to archive. This application is able to archive the attachment. But this script also remove the original mails in the label. The next solution is better.
Solution 2
2) Another solution is using Gmail backup utility
https://code.google.com/p/gmail-backup-com/
This application will backup all emails (event trash), but no attachment and no classification in labels.

MySQL NOW() function add 1 hour, day, month, year

MySQl NOW() function returns current time with format ‘YYYY-MM-DD HH:MM:SS’. If we use
NOW()+1, a second is added, but the return value format is changed to:

YYYYMMDDHHMMSS.uuuuuu #sthash.K5ILIJDY.dpuf

 

 YYYYMMDDHHMMSS.uuuuuu
MySQL statement example 1:
 SELECT NOW(), NOW() + 1;
we will get
 '2013-11-13 13:17:07', 20131113131708.000000
To keep the same format: we can use
NOW() + INTERVAL 1 second;
to add a second with format ‘YYYY-MM-DD HH:MM:SS’

MySQL statement example 2:
 SELECT NOW(), NOW() + INTERVAL 1 second;
 we will get
 '2013-11-13 13:19:21', '2013-11-13 13:19:22'
Similarly  we can add 1 hour, 1 day, 1 month or one year using
+INTERVAL 1 hour,  +INTERVAL 1 month, +INTERVAL 1 year

We can use "-" to reduce time.

YYYYMMDDHHMMSS

YYYYMMDDHHMMSS.uuuuuu #sthash.K5ILIJDY.dpuf

YYYYMMDDHHMMSS.uuuuuu #sthash.K5ILIJDY.dpuf

YYYYMMDDHHMMSS.uuuuuu #sthash.K5ILIJDY.dpuf

Make and embed Youtube Playlist

A lot of youtube music videos are only a few minutes, you may want to create a playlist and embed in your blog.
Go to  your Youtube account, go to Video manager (in upload tab), click playlists in left menu,  click New Playlist in right top corner, add url from youtube videos.
Below is the playlist I created:
https://www.youtube.com/watch?v=JnNT-DSsb1w&list=PLmhRNZyYVpDmMBBLU_a8ur8yYSTnus_5N
The list id is PLmhRNZyYVpDmMBBLU_a8ur8yYSTnus_5N
To embed this playlist:
<iframe allowfullscreen="" frameborder="0" height="600" src="http://www.youtube.com/embed/videoseries?list=PLmhRNZyYVpDmMBBLU_a8ur8yYSTnus_5N" width="100%"></iframe>
To embed another playlist, replace my list id  PLmhRNZyYVpDmMBBLU_a8ur8yYSTnus_5N to yours.
Result:

PHP, global variable and local variable

In PHP programming, we should be careful of the scope of global variable and local variables.
Example 1: the $a variable inside test function is not related to the global varialbe $a outside test function, and default is 0
<?php
$a = 2; /* global scope */
function test()
{
    $a=$a+1;
    echo $a.'<br />'; /* reference to local scope variable */
}
test();
test();
echo $a;
?>
The result is
1
1
2

Example 2: now we declare $a as a global variable inside function test, it is  the same as the $a  variable defined  outside the test function
<?php
$a = 2; /* global scope */

function test()
{
   global $a;
    $a=$a+1;
    echo $a.'<br />'; /* reference to local scope variable */
}

test();
test();
echo $a;
?>
The result is
3
4
4
Example 3: global variable $db is often used in mysql_query($sql, $db) in PHP functions,
and  static variable $now is used to memorize its first  value, after first time call write_to_rplog function,  the variable $now is set. 
function write_to_rplog($operation_type, $table_name, $num_affected, $details) {
    global $db;
    static $now;
 
    if (!$now) {
        $now = date('Y-m-d H:i:s');
    }
    if ($num_affected > 0) {
        $details = addslashes(stripslashes($details));
        $sql    = "INSERT INTO  rp_admin_log VALUES ('$_SESSION[login]', '$now', '$operation_type', '$table_name', $num_affected, '$details')";
        $result = mysql_query($sql, $db);
    }
}

Customize iPhone 5s

Below are some tips for iPhone 5s beginners:

1) Setup fingerprints:
 In Settings, click General then touch ID & Passcodes->Touch ID, then click add a fingerprint to add fingerprints

  2) Setup Apple ID and  allow icloud  to locate the iPhone:

1. Open the "Settings" application
- 2. Go to "General" and select "Passcode & Fingerprint"
- 3. Go to "Fingerprints" and tap "Add a fingerprint"
- 4. Follow the on-screen prompts
Read more at http://www.mobileburn.com/22047/news/apple-iphone-5s-touch-id-demo-how-to-setup-the-iphone-5s-fingerprint-scanner#dGEvzqZJAc70OTOk.99

1. Open the "Settings" application
- 2. Go to "General" and select "Passcode & Fingerprint"
- 3. Go to "Fingerprints" and tap "Add a fingerprint"
- 4. Follow the on-screen prompts
Read more at http://www.mobileburn.com/22047/news/apple-iphone-5s-touch-id-demo-how-to-setup-the-iphone-5s-fingerprint-scanner#dGEvzqZJAc70OTOk.99

1. Open the "Settings" application
- 2. Go to "General" and select "Passcode & Fingerprint"
- 3. Go to "Fingerprints" and tap "Add a fingerprint"
- 4. Follow the on-screen prompts
Read more at http://www.mobileburn.com/22047/news/apple-iphone-5s-touch-id-demo-how-to-setup-the-iphone-5s-fingerprint-scanner#dGEvzqZJAc70OTOk.99

 Sign up Apple ID,  allow icloud (https://www.icloud.com/) to find my iPhone.
install icloud (http://www.apple.com/ca/icloud/setup/) in my local Windows 7 PC

3) Setup WiFi
 In Settings,  click WiFi, enter  WiFi password for work and at home

4) Setup email account, voicemail password and phone contact

5)  Check cellular data usage.

In settings,  click cellular data, turn off when  not using it. Click cellular data, to see cellular data usage.

6) multiple  input keyboards for different languages, for example for English and Chinese
In Settings, click General and select Keyboards, then click keyboards, add New Keyboard, add Chinese -simplified (handwriting) and Chinese -simplified (Pinyin), my default is English. So I can input Chinese.

7) Configure to spend less  battery time:
To spend less battery time, in settings, click General and select Background App Refresh, turn off Background App Refresh.
In pedometer, disable auto tracking (in more menu) to not using it.
 
8) In App store, download several free applications:
transit:
to see the bus schedule  and map directly related to where you are.
 pacer pedometer,
measure how many steps you walk every day
 YouTube, Google, Google Maps, TD Canada, Roger  my account, 文学城 etc

iphone 5s slow motion: Halloween night fireworks

Using iphone 5s slow motion to take Halloween night fireworks
Video:  iphone 5s slow motion: Halloween night fireworks

Below is how I make this video:
1)Take video using iphone 5s slow motion. Export slow motion to local computer
2) Use Windows live movie maker in Windows 7,
edit -speed 0.5x (0.25x too slow)
3) Export the movie in 720p
4) The drawback of slow motion has no sound