Sunday, April 28, 2013

Google search tips

Google search tips
1. Using Google to search only one site
2. search only swf files or only one file type
3. Search exactly a word using double quote
"PHP programmer"
4. Exclude a word using dash
"PHP programmer", -Java
5. Using search tools
for example after typing "PHP programmer"  and search
you can find search tools to search only in a country,
location, specific  time range, blog search etc

Tuesday, April 23, 2013

Difference between PRIMARY KEY, UNIQUE KEY and key in MySQL

Primary key, unique key and key in MySQL are used as index to speed up search process.
Primary key and  unique key can not have same values for two distinct rows, while key will not require this. There is only one primary key in one table, but can have  several unique keys.
CREATE TABLE  member` (
  `member_id` mediumint(8) unsigned NOT NULL auto_increment,
  `login` varchar(20) NOT NULL default '',
  `password` varchar(20) NOT NULL default '',
  `email` varchar(50) NOT NULL default '',
  `country` varchar(50) NOT NULL default '',
  `phone` varchar(15) NOT NULL default '',
  `extension` varchar(10) NOT NULL,
  `creation_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `membership_type` tinyint(4) NOT NULL default '0',
  `last_login` datetime NOT NULL default '0000-00-00 00:00:00',
  `account_control` tinyint(4) NOT NULL default '1',
  `dept_id` mediumint(8) unsigned NOT NULL default '0',
  `organization` varchar(255) NOT NULL,
  `site` varchar(255) NOT NULL,
  `unit` varchar(255) NOT NULL,
  `manager` varchar(255) NOT NULL,
  `language` varchar(5) NOT NULL default 'en',
  `preferences` text NOT NULL,
  PRIMARY KEY  (`member_id`),
  UNIQUE KEY `login` (`login`),

  KEY `email` (`email`)

PRIMARY KEY  (`member_id`): index name and kind are primary, using column member_id
UNIQUE KEY `login` (`login`): index name login and kind unique using column login
KEY `email` (`email`) index name email, index kind: index, using column email.

Friday, April 19, 2013

Change when computer go to sleep or turn off display in Windows 7

Click start, type power, choose power option.  Click when computer sleeps in left panel,
the following page will appear. change the time from drop down menu.
Second method:  go to control Panel->Hardware and Sound and choose power options.

PHP and MySQL , process post array

Suppose in the following form, we have arrays   outcome and modules to post to search_results.php
<form name="search_form" action="search_results.php" onsubmit="select_multiples(this);" method="POST">
<h5>Were outcomes measured? including the following</h5> 

<input type="checkbox" name="outcome[]" value="0" id="outcome-unknown" /> 
 <input type="checkbox" name="outcome[]" value="1" id="outcome-yes" />
 <input type="checkbox" name="outcome[]" value="2" id="outcome-no" />
<br />
<h5>Including the selected modules</h5>

 <select name="modules[]" id="modules" multiple="multiple" size="10"> </select>
In search_result.php, we use implode to combine query in MySQL query:

$sql ='SELECT event_id FROM cesei_activity_new WHERE outcome IN('.implode(',',$_POST['outcome']).')';
     $result = mysql_query($sql, $db);
      $in_events     = array();
      while($row = mysql_fetch_assoc($result)){
         $in_events[$row['event_id']] = $row['event_id'];
      $include_by_type[] = $in_events;


$sql ='SELECT event_id FROM cesei_activity_mod WHERE module_id IN('.implode(',',$_POST['modules']).')';
     $result = mysql_query($sql, $db);
      $in_events     = array();
      while($row = mysql_fetch_assoc($result)){
         $in_events[$row['event_id']] = $row['event_id'];
      $include_by_type[] = $in_events;

  In some cases, we may need to force all the values in select box selected, for example the case for transfer selection items between box. Define   JavaScript function select_multiples for onsubmit in the form
function select_multiples(form){
   for(var e = 0; e<form.elements.length; e++){
      var elm = form.elements[e];
      if(elm.type.indexOf('select') >= 0 &&'[]') >= 0 && elm.className.indexOf('submit-all') >= 0){
         for(var op=0; op<elm.options.length; op++ ){
            elm.options[op].selected = true;

PHP, calculate the common elements in arrays using array_intersect

If MySQL query is too complicated, we may  divide it into several queries. This happens
often when we have very complicated search criterion.We need to combine the query results.
For example the query result event_id is stored in array $include_by_type, and
$include_by_type[0] stores ( 1,3,4, 5)
$include_by_type[1] stores ( 2,3,5, 6)
$include_by_type[2] stores ( 3,4,5, 7)
Now we want to combine all these results, i.e. to get common elements (3,5),
below is the code to achieve this using PHP function  array_intersect
  $acount = count($include_by_type);
   $intersect = $include_by_type[0];
   $i = 1;
   while($i < $acount){
      $intersect = array_intersect($intersect,$include_by_type[$i]);


What happen if we want to combine the result and remove the duplication, such as
final result (1,2,3,4,5,6,7)? We can first use array_merge to merge array, then use
array_unique function to removes duplicate values from an array.
  $acount = count($include_by_type);
   $merge = $include_by_type[0];
   $i = 1;
   while($i < $acount){
      $merge = array_merge($merge,$include_by_type[$i]);

   $result = array_unique($merge);


Tuesday, April 16, 2013

Post a multi-dimension array using a single hidden input using PHP serialize

Suppose we have a 2d array:

We want to post it to report_csvD.php in HTML form.
  <form name="getcsvpdf" action="report_csvD.php" method="POST">
We can use PHP serialize function to generates a sortable representation of a value.
After post the data, we can use PHP function unserialize to change it back.
 We  had better use PHP function htmlentities  to display array in the form properly and use  html_entity_decode to change it back.
  <form name="getcsvpdf" action="report_csvD.php" method="POST"> 
    $mydata=Array();//Need to assign the value by yourself

  echo '<input type="hidden" name="mydata" value="'.$mydata_encoded.'">';

in report_csvD.php, assume the key of last column is 'dates'
header('Content-Type: application/x-excel');
header('Content-Disposition: attachment; filename="cesei_activity_log.csv"');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
 foreach($mydata as $val0)  foreach($val0 as $key=>$val){echo str_replace(',','',$val);
 if($key=='dates') echo("\n");
 else  echo(", ");}

Monday, April 15, 2013

pagination using PHP and JavaScript for a report

We can use limit in MySQl query to divide query into pages. But here I try to use CSS display to hide or show pages.
First we define each number in page as a box and hover effect. And define class current to hightlight current page
div.paging li a {
    text-decoration: none;
    padding: 2px 5px;
   border: 1px solid #000;
div.paging li a:hover{
   color: #ececec;
   background: #366161;
div.paging li a.current {
   color: #ececec;
   font-style: italic;
   background: #00283e;

Assume we already have a nig array $event in PHP, we can set results peg page
     $results_per_page = 15;
     $num_pages = max(ceil($num_results/$results_per_page), 1);

We create a JavaScript function setPage_r to only display current page and highlight current page number
 <script type="text/javascript">
 function setPage_r(num,num_pages){
     for(var i=1; i<=num_pages; i++){
        document.getElementById('page-'+i).style.display = "none";
         document.getElementById('page-mark-top-'+i).className = " ";
        if(i==num) {
           document.getElementById('page-'+i).style.display = "block";
           document.getElementById('page-mark-top-'+i).className = "current";

To display pages. If more than 20 pages, we go to next line
<div class="paging"><span>Page #</span>
    for($i=1; $i<=$num_pages;$i++){
    echo '</ul></div></div><div class="clear-after"> <div class="paging"><span>Page #</span><ul>';?>
        <li><a href="#<?php echo ($i); ?>" id="page-mark-top-<?php echo ($i); ?>"  onclick='setPage_r(<?php echo $i.",$num_pages"; ?>);return false;'><?php echo $i; ?></a>
    <?php } ?>

Table body:
   $row_count = 0;

 foreach($events as $group_id=>$group_events){
          $this_count ++;
          $end_div =0;
            if($j==1) echo "<div id='page-$j'>   <table   cellspacing=0 border=1 style='font-size:10' width=100% >";
             else echo "</table></div><div id='page-$j'><table   cellspacing=0 border=1 style='font-size:10' width=100% >";
      <!-- <th scope="col"><a href="#program_name" onclick="setSort('program_name')">Member Name</a></th> -->
      <!-- <th scope="col"><a href="#last_name" onclick="setSort('contact_last_name')">Society</a></th> -->
      <th scope="col" width=35% style="text-align:left" >Name of Course</th>
      <th scope="col"  style="text-align:left" >Date of Course</th>


         echo '<td>'.$event['header'].'</td>';
         echo '<td>'.$event['dates'].'</td>';
           echo '<td></tr>';


Final, we set the default page is the first page:
 setPage_r(1,<?php echo $num_pages; ?>);

Saturday, April 13, 2013

Jquery expandable tree and toggle tree exmple

We can use jQuery to display or hide a tree, items or lists.
Example code:
<h1>Jquery expandable tree exmple</h1>
    <script src=""
    $(function() {
    $('div.mytree div:has(div)').addClass('parent');
    $('div.mytree div').click(function() {
        var thistree = $(this);
        return false;
div.mytree div {
 background:transparent url( no-repeat top left;
div.mytree div.parent div {
div.mytree div.parent {
 cursor:pointer ;
 background:transparent url( no-repeat top left;
div.mytree div.expanded {
 background:transparent url( no-repeat top left;
<div class="mytree" style="padding:12px;border:2px solid #ccc;">
    <div>Top tree 1, expandable
        <div>Second tree 2.1</div>
        <div>Second tree 2.2, expandable
            <div>Third tree 2.2.1</div>
            <div>Third tree 2.2.2</div>
            <div>Third tree 2.2.3</div>
        <div>Second tree 2.3</div>
Top tree 1, expandable
Second tree 2.1
Second tree 2.2, expandable
Third tree 2.2.1
Third tree 2.2.2
Third tree 2.2.3
Second tree 2.3

Friday, April 12, 2013

PHP: add a download as pdf file button in report page

In my last post, I created a button for convert and  download csv file
PHP: add a download as csv file button in report page
Now I want to add another button for download pdf file.In my report.php:
function generatePdfA(){
   document.forms['getcsvpdf'].action = 'report_pdfA.php';
       <form name="getcsvpdf" action="report_csvA.php" method="POST">
         <input type="submit" name="submit" value="Download CSV file" class="input-button" />
          <input type="submit" name="submitpdf" value="Download pdf file" class="input-button"  onclick="generatePdfA();" />

For pdf file conversion, I use action report_pdf.php, which used tcpdf library to convert html table to pdf format.
 I  download tcpdf from

 and install tcpdf in current directory

 report_pdf.php code:

// create new PDF document

// set header and footer fonts
$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));

// set default monospaced font

//set margins

//set auto page breaks
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);

//set image scale factor

//set some language-dependent strings
// set document information
// set font
$pdf->SetFont('helvetica', 'B', 20);

// add a page

$pdf->Write(0, 'CESEI Activity Reports for ACS', '', 0, 'L', true, 0, false, false, 0);

$pdf->SetFont('helvetica', '', 8);

$tbl = '
<table cellspacing="0" cellpadding="1" border="1">
      <th scope="col" width=35% style="text-align:left" >Type of Activity </th>
      <th scope="col"  style="text-align:left" >Number of Activity</th>
      <th scope="col" style="text-align:left">Hours of Instruction</th>
       <th scope="col" style="text-align:left" colspan="6">Surgeon, Physicians, Residents, Medical Students, Nurses, Allied Health Professionals</th>
 <td> </td>  <td> </td>  <td> </td>
 <td> S</td>  <td>P </td>  <td>R</td> <td>MS</td>  <td>N</td>  <td>AHP</td>
 <td><b>Courses</b> </td>  <td> </td>  <td> </td>
 <td> </td>  <td> </td>  <td> </td> <td> </td>  <td> </td>  <td> </td>
 <tr><td>Simulators - High Fidelity</td><td>'.$_POST['count_sh'].'</td><td>'.
$pdf->writeHTML($tbl, true, false, false, false, '');
$pdf->Output('activity_log_for_acs.pdf', 'I');

Video: add a download as pdf file button in report page


PHP: add a download as csv file button in report page

Suppose  we have  a form to post data from MySQL to report.php
<form name="form1" action="report.php" method="POST">
Now we not only want to display data in the table in report.php, we also need ot add a button to download as CSV file.
In report.php below, we have a table to display data. We also  have a hidden input to transfer data
to new action script report_csvA.php to download as csv file.
     <form name="getcvs" action="report_csvA.php" method="POST">
         <input type="submit" name="submit" value="Download CSV file" class="input-button" />
   <table   cellspacing="0" border="1" style="font-size:10" width="100%">
      <!-- <th scope="col"><a href="#program_name" onclick="setSort('program_name')">Member Name</a></th> -->
      <!-- <th scope="col"><a href="#last_name" onclick="setSort('contact_last_name')">Society</a></th> -->
      <th scope="col" width=35% style="text-align:left" >Type of Activity </th>
      <th scope="col"  style="text-align:left" >Number of Activity</th>
      <th scope="col" style="text-align:left">Hours of Instruction</th>

        $count_basic_skills = $_POST['count1'];
        $count_hours_bs = $_POST['count2'];
         echo '<td>Basic Skills</td>';
         echo '<td>'.$count_basic_skills.'</td>';
         echo '<td>'.$count_hours_bs.'</td>';     

      <input type="hidden" name="count_basic_skills" value="<?php echo $count_basic_skills;?>">

      <input type="hidden" name="count_hours_bs" value="<?php echo $count_hours_bs;?>">

In  report_csvA.php, a csv file is produced for download:
header('Content-Type: application/x-excel');
header('Content-Disposition: attachment; filename="cesei_activity_log.csv"');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
$headers=array('Type of Activity','Number of Activity','Hours of Instruction');
 $row_1=array('Basic Skills', $_POST['count_basic_skills'],$_POST['count_hours_bs']);


Video: PHP: add a download as csv file button  in report page

Tuesday, April 9, 2013

Mutiple buttons with different actions in a single HTML form

Suppose in a HTML form with name search_form_results, we have three buttons.
One button requires action reports.php, while other two buttons  require reportsD.php and reportsA.php. We can use JavaScript function to submit a form with different actions:
   document.forms['search_form_results'].action = 'reportsA.php';

Example code:
<form name="search_form_results" action="reports.php" method="POST">
  <input type="submit" name="submit_report"  value="Generate a Report Using This Data Set" " />
      <input type="button" name="submit_reportD"  value="Generate a Report for Dean"   onclick="generateReportD();" />
            <input type="button" name="submit_reportA"  value="Generate a Report for ACS"  onclick="generateReportA();" />

Here we need to define two  JavaScript functions for different actions reportsD.php and reportsA.php, the default action reports.php is ignored.
function generateReportD(){
   document.forms['search_form_results'].action = 'reportsD.php';

function generateReportA(){
   document.forms['search_form_results'].action = 'reportsA.php';


Monday, April 8, 2013

Combination of input box and dropdown menu using JavaScript

Sometimes users may not only want to select the value from the dropdown menu, but also can input new values if the values are not in the list, We can use
In SELECT where input box name is event_name.
Example code:
 <input type="text"  name="event_name" maxlength="255"  style="width:50%"    />    
<SELECT NAME="options"
     <OPTION VALUE=" ">Type event name or choose from the list
    <OPTION VALUE="Emergency Medicine 3 Year Resuscitation Sim">Emergency Medicine 3 Year Resuscitation Sim
    <OPTION VALUE="ENT Research Cadaver Lab">ENT Research Cadaver Lab
    <OPTION VALUE="Feeding Tube Insertion">Feeding Tube Insertion
    <OPTION VALUE="General Surgery Laparoscopic Training">General Surgery Laparoscopic Training
    <OPTION VALUE="General Surgery M&M Rounds">General Surgery M&M Rounds
    <OPTION VALUE="Harvey Internal Medicine Residents">Harvey Internal Medicine Residents
    <OPTION VALUE="Harvey Internal Medicine Student Teaching">Harvey Internal Medicine Student Teaching
    <OPTION VALUE="Internal Medicine Resident Procedural Train">Internal Medicine Resident Procedural Train   
    <OPTION VALUE="Perioperative Nursing">Perioperative Nursing
    <OPTION VALUE="Plastic Cadaver Lab">Plastic Cadaver Lab
    <OPTION VALUE="Tour for Laerdal">Tour for Laerdal
    <OPTION VALUE="Orthopedic Resident Cadaver Lab">Orthopedic Resident Cadaver Lab
    <OPTION VALUE="Simulation Fellows">Simulation Fellows
    <OPTION VALUE="Simulation Fellows Lecture">Simulation Fellows Lecture
    <OPTION VALUE="Simulation Fellow Reflection">Simulation Fellow Reflection
    <OPTION VALUE="Surgery Undergraduate Curriculum Comm.">Surgery Undergraduate Curriculum Comm.
    <OPTION VALUE="UBC Ethics Committee">UBC Ethics Committee
    <OPTION VALUE="UBC Summer eHealth Camp">UBC Summer eHealth Camp
DEMO: the selection in drop-down menu will go to input box

Saturday, April 6, 2013

Copy table in MySQL, change table name in MySQL

To copy a table in MySQL, for example copy table users to new_users.
First we create  an empty table new_users with the same structure as users
create table new_users like users;
Then we insert new_users withe same content from users
INSERT new_users SELECT * FROM users;

To change table name in MySQL, for example new_users to new_users1
RENAME TABLE new_users TO new_users1;

Using Ajax to communicate MySQL database in server

In JavaScript, it is difficult to communicate MySQL in server. Below is the example to use
  Ajax to communicate MySQL database in server.
Suppose we have a button, when users click the button, JavaScript function  getEnrollmentData() is called and student enrollment list in MySQL database is displayed.
<input type="button" onclick="getEnrollmentData();" name="get_enroll_data" id="get_enroll_data" value="Select the modules in which this user is enrolled" class="input-button" />
In JavaScript (jQuery library is needed), first we  defined a AJAX event
var enrollData_Ajax = new Ajax();
If AJAX request is successful, we call  function selectModules
Event.attachListener(enrollData_Ajax, 'ajaxSuccess', selectModules);
If AJAX request fails, we call  function selectModules
 Event.attachListener(enrollData_Ajax, 'ajaxFailure', showError);
Now we can use Ajax in  getEnrollmentData()
function getEnrollmentData(){

Here we communicate to MySQL database in Server using xml_user_enroll_data.php and get data in XML format.
   header('Expires: 0');
   header('Pragma: no-cache');
   header('Cache-Control: no-cache, must-revalidate, post-check=0, pre-check=0');
   header('Content-Type: application/xml');
   echo('<?xml version="1.0" encoding="UTF-8"?>');

$user_id = intval($_GET['user_id']);
$module_id = intval($_GET['module_id']);
   $sql = "SELECT course_id AS ed FROM edu_course_enrollment WHERE member_id = $user_id";
   $sql = "SELECT member_id AS ed  FROM edu_course_enrollment WHERE course_id = $module_id ";
$result = mysql_query($sql,$db);
echo('<enrollment>0</enrollment>'); //make sure its always an array
while($row = mysql_fetch_assoc($result)){

If ajax request is successful, we call  function selectModules, which gets XML data from AJAX
    userData = new Object();
     userData = XMLObject.toJS(enrollData_Ajax.getXML()).e_data;

Wednesday, April 3, 2013

jQuery autocomplete UI - text hint like google search

The Autocomplete widgets in jQuery provides suggestions while you type into the field, similar to text hint in Google search. The hints are stored in an tag array in JavaScript. Detail can be found:
Demo:  type ja to try

Demo source code:
<!doctype html> <html lang="en"> <head> <meta charset="utf-8" /> <title>jQuery UI Autocomplete - Default functionality</title> <link rel="stylesheet" href="" /> <script src=""></script> <script src=""></script>
<link rel="stylesheet" href="/resources/demos/style.css" />
$(function() {
var availableTags = [
$( "#tags" ).autocomplete({
source: availableTags
<div class="ui-widget">
<label for="tags">Tags: </label>
<input id="tags" />

PHPWind-- A PHP forum script applcaition in China

PHPWind is a popular PHP scripting forum application in China. PHPWind is based on the MySQL database engine and supports a large number of mods and templates made by its users.
You can download it from:
There are also other PHP script for forum application in English:
discuz, mybb, phpbb

Tuesday, April 2, 2013

Computer date time wrong may cause gmail security warning

If your desktop or laptop date time is not set right (maybe due to battery run down), you may get
a security warning message when you try to access Gmail in Firefox. You can choose ignore the message and  continue to access to Gmail. But it is better to change the computer date right by  clicking the time  at the right bottom of the Windows to change Windows date and time settings.