Friday, October 25, 2013

MySQL select random rows



Below is the example of MySQL statement   to select 4 random rows form table cesei_members using ORDER BY RAND() LIMIT 4:

SELECT * FROM  cesei_members  ORDER BY RAND() LIMIT 4;

I use it to randomly pick up 4 committee members for each module.

Wednesday, October 23, 2013

php, upload file same name



I upload mp3 files using PHP, I want to check if the file exists, then change the file name.
using
$filebase = basename($filename, ".mp3");
to get the filename without mp3 extension, for example test.mp3, we get test.
If test.mp3 exists, the upload file name will be changed to test_1.mp3, test_2.mp3 ... in sequence.
The final PHP code is as follows
<?php
$filetmpname = $_FILES['newfile']['tmp_name'];    
$filename=$_FILES['newfile']['name'] ;
 $basedir="C:\\LMS_CESEI\\file_repository\\";
$filebase = basename($filename, ".mp3");
$i=0;
while(file_exists($basedir.$filename)) {
$i = $i +1;
$filename = $filebase.'_'.$i.'.mp3';
}
$ok = move_uploaded_file($filetmpname, $basedir.$filename);

?>

To see the list of files uploaded in the remote directory
<?php
$basedir=
"C:\\LMS_CESEI\\file_repository\\";
 if ($handle = opendir($basedir)) {
      while (false !== ($file = readdir($handle))) {
          if ($file == '.' || $file == '..') {
            continue;
          }
          echo $file."<br />";
        }
}
?>

php, play mp3 file outside web server directory



To prevent that mp3 audio files are downloaded, we may need to  put mp3  file outside  web server directory. On the other hand, we also want to play these mp3 files online. The following PHP script is used to play the nn.mp3 file in folder C:\LMS\uploads\, which is not related to the web server folder.
<?php
$file = "C:\\LMS\\uploads\\nn.mp3";
$real = realpath($file);
if (file_exists($real)){

     $ext   = 'audio/mpeg';
      $size  = filesize($real);
      $size2 = $size-1;
      header('Content-Type: '.$ext);
      header("Content-Range: bytes 0-$size2/$size");
      header('Content-Length: '.$size);
      echo @file_get_contents($real);

}else {
       echo $file." does not exsit";

}
?>

For other file formats, $ext will be changed as below:
$mime['zip']   = 'application/zip';
$mime['au']    = 'audio/basic';
$mime['snd']   = 'audio/basic';
$mime['mid']   = 'audio/midi';
$mime['midi']  = 'audio/midi';
$mime['kar']   = 'audio/midi';
$mime['mpga']  = 'audio/mpeg';
$mime['mp2']   = 'audio/mpeg';
$mime['mp3']   = 'audio/mpeg';
$mime['aif']   = 'audio/x-aiff';
$mime['aiff']  = 'audio/x-aiff';
$mime['aifc']  = 'audio/x-aiff';
$mime['ram']   = 'audio/x-pn-realaudio';
$mime['rm']    = 'audio/x-pn-realaudio';
$mime['ra']    = 'audio/x-realaudio';
$mime['wav']   = 'audio/x-wav';
$mime['pdb']   = 'chemical/x-pdb';
$mime['xyz']   = 'chemical/x-pdb';
$mime['gif']   = 'image/gif';
$mime['ief']   = 'image/ief';
$mime['jpeg']  = 'image/jpeg';
$mime['jpg']   = 'image/jpeg';
$mime['jpe']   = 'image/jpeg';
$mime['png']   = 'image/png';
$mime['tiff']  = 'image/tiff';
$mime['tif']   = 'image/tiff';

$mime['xml']   = 'text/xml';
$mime['mpeg']  = 'video/mpeg';
$mime['mpg']   = 'video/mpeg';
$mime['mpe']   = 'video/mpeg';
$mime['qt']    = 'video/quicktime';
$mime['mov']   = 'video/quicktime';
$mime['avi']   = 'video/x-msvideo';
$mime['movie'] = 'video/x-sgi-movie';
$mime['ice']   = 'x-conference/x-cooltalk';
$mime['html']  = 'text/html';
$mime['htm']   = 'text/html';
$mime['xls']   = 'application/vnd.ms-excel';
$mime['log']   = 'text/plain';

$mime['gtar']  = 'application/x-gtar';
$mime['gz']    = 'application/x-gzip';
$mime['tgz']   = 'application/x-gzip';

$mime['pdf']   = 'application/pdf';
$mime['swf']   = 'application/x-shockwave-flash';

Record voice using microphone and upload to server using flash and PHP



Below is to get the flash from  Moodle LMS audio recording plugin and redesign PHP upload file.
Below is the snapshot of Moodle flash plugin interface:

1) Download moodle audio recording plugin:
https://moodle.org/plugins/pluginversions.php?plugin=assignment_onlineaudio
unzip and get onlineaudio folder,  copy assests/recorder.swf to your working directory.
2) Embed recorder.swf:
<embed type="application/x-shockwave-flash" src="recorder.swf" id="recorder" name="recorder" quality="high" wmode="transparent" flashvars="gateway=simpleupload.php" height="276" width="430">
3) create simpleupload.php to process the sound file produced by recorder.swf:
<?php
 $filename = preg_replace('/\s/', '', $_FILES['newfile']['name']);
$filetmpname = $_FILES['newfile']['tmp_name'];  

$ok = move_uploaded_file($filetmpname, "C:\\LMS\\uploads\\".$filename);
?>
In this example, the upload file will be put in C:\LMS\uploads\
4) To displayand play  the audio file (for example nn.mp3), I used the longtail_payer.swf from JW player:
<audio controls="controls">
 <source src="nn.mp3" />
 <!-- fallback -->
  <embed type="application/x-shockwave-flash"                                                        
    flashvars="audioUrl=nn.mp3"   
     src="longtail_player.swf"  
     width="650? height="0? quality="best"></embed>
</audio>`  

5)   For recorder using Flash Media server, refer to pRecorderTest. 115K Zip file in 
http://flash-communications.net/sourcecode/index.html

Friday, October 18, 2013

Remove last character of a string, for example comma and a space



In the following example, I combined all reviewer full name using ',  ' (comma and  a pace), but I need to remove the last comma and space when they are displayed.  PHP function rtrim can be used for this purpose, example PHP code:
  $sql_1 = "SELECT
                  M.prefix,M.first_name,M.last_name
                  FROM cesei_module_user_reviews MUR
                  LEFT JOIN cesei_members M ON MUR.member_id=M.member_id WHERE MUR.module_id=".$module_id ;
  $result_1 = mysql_query($sql_1, $db);
   $reviewers = ' ';
 while($row_1 = mysql_fetch_assoc($result_1)){
             $reviewers .= trim(htmlspecialchars($row_1['prefix'].' '.$row_1['first_name'].' '.$row_1['last_name'])).',  ';
        
    }
    $reviewers = rtrim($reviewers, ", ");

Wednesday, October 16, 2013

PHP/MySQL - Simple way to return a single value using mysql_result



Normally, we can use mysql_fetch_assoc to get the MySQL query result in PHP, example:
   $module_id = 2;
   $sql = "SELECT  * FROM  cesei_review_done WHERE module_id=$module_id";
    $result = mysql_query($sql,$db);
    while($row = mysql_fetch_assoc($result)) {
        $review_result = $row['review_done'];
    }

In some casews, we know MySQL will return only a single value, the simple way to get this single value is to use PHP function  mysql_result instead of  mysql_fetch_assoc, example:

   $module_id = 2;
   $sql = "SELECT  review_done FROM  cesei_review_done WHERE module_id=$module_id";
    $result = mysql_query($sql,$db);
    $review_result  = mysql_result($result, 0);

Tuesday, October 15, 2013

MySQL key, primary key and uinque key



KEY is normally a synonym for INDEX. Using key or index,  MySQL can quickly determine the position of the data  without having to look at all the data. It is much faster then reading sequentially.  MySQL uses indexes for the operations such as WHERE and LIKE for quick  search.

 A UNIQUE  key creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

A PRIMARY KEY is a special unique key. A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.

Example of MySQL code for these keys:
CREATE TABLE `FACULTIES` (
  `FACULTYID` int(11) NOT NULL auto_increment,
  `FACULTYCODE` varchar(4) NOT NULL default '',
  `TITLE` varchar(127) NOT NULL default '',
  `dean` varchar(7) NOT NULL default '',
  PRIMARY KEY  (`FACULTYID`),
  UNIQUE KEY `FACULTYCODE` (`FACULTYCODE`),
  KEY `dean` (`dean`)
) TYPE=MyISAM;


Tuesday, October 8, 2013

PHP, split summary text data from pubmed using preg_split and preg_match


I have summary text data from pubmed and I want to separate them into author, title, author, date and pubid etc. The PHP code:
<?php
//Journal list number and authorlist
$pmregex = '/^(\d+): (.+?)\.'

//article title
         . ' (.+?)\.'

// (.+?)  Journal name such as Oral Dis
// \. (\d\d\d\d)(.*?);  . 2013 Jul
// ;(.+?)     :19 (5)
//:(\w+)-?(\w*?) page number such as  440-451
// add \n? after considering 0+ line break
         . '(.+?)[\. \n?](\d\d\d\d)(.*?);(.+?):(\w+)-?(\w*?)[\.\;](.*)'
//PMID number
       . '[PMID: \n?](\d+)'
         . '/s';
$data="1: Saini R, Poh C. Photodynamic therapy: a review and its prospective role in the
management of oral potentially malignant disorders. Oral Dis. 2013
Jul;19(5):440-451. doi: 10.1111/odi.12003. PubMed PMID: 24079944.



2: Powell JJ, Apiratpracha W, Partovi N, Erb SR, Scudamore CH, Steinbrecher UP,
Buczkowski AK, Chung SW, Yoshida EM. Subcutaneous administration of hepatitis B
immune globulin in combination with lamivudine following orthotopic liver
transplantation: effective prophylaxis against recurrence. Clin Transplant. 2006
Jul-Aug;20(4):524-5. PubMed PMID: 16842532.
";
//[\r\n]: newline, \d+:  separator is digital numbers followed by : ,
//?=Separator is included in the output array
$choppeddata = preg_split('/([\r\n])(?=\d+: )/',$data);

foreach($choppeddata as $subdata)
{
print_r($subdata);
echo '<br /><br />';
preg_match($pmregex, trim($subdata), $matches);
echo '<pre>';
print_r($matches);
echo '</pre>';
}
?>

Running result:
1: Saini R, Poh C. Photodynamic therapy: a review and its prospective role in the management of oral potentially malignant disorders. Oral Dis. 2013 Jul;19(5):440-451. doi: 10.1111/odi.12003. PubMed PMID: 24079944.
Array
(
    [0] => 1: Saini R, Poh C. Photodynamic therapy: a review and its prospective role in the
management of oral potentially malignant disorders. Oral Dis. 2013
Jul;19(5):440-451. doi: 10.1111/odi.12003. PubMed PMID: 24079944
    [1] => 1
    [2] => Saini R, Poh C
    [3] => Photodynamic therapy: a review and its prospective role in the
management of oral potentially malignant disorders
    [4] =>  Oral Dis
    [5] => 2013
    [6] => 
Jul
    [7] => 19(5)
    [8] => 440
    [9] => 451
    [10] =>  doi: 10.1111/odi.12003. PubMed 
    [11] => 24079944
)
2: Powell JJ, Apiratpracha W, Partovi N, Erb SR, Scudamore CH, Steinbrecher UP, Buczkowski AK, Chung SW, Yoshida EM. Subcutaneous administration of hepatitis B immune globulin in combination with lamivudine following orthotopic liver transplantation: effective prophylaxis against recurrence. Clin Transplant. 2006 Jul-Aug;20(4):524-5. PubMed PMID: 16842532.
Array
(
    [0] => 2: Powell JJ, Apiratpracha W, Partovi N, Erb SR, Scudamore CH, Steinbrecher UP,
Buczkowski AK, Chung SW, Yoshida EM. Subcutaneous administration of hepatitis B
immune globulin in combination with lamivudine following orthotopic liver
transplantation: effective prophylaxis against recurrence. Clin Transplant. 2006 
Jul-Aug;20(4):524-5. PubMed PMID: 16842532
    [1] => 2
    [2] => Powell JJ, Apiratpracha W, Partovi N, Erb SR, Scudamore CH, Steinbrecher UP,
Buczkowski AK, Chung SW, Yoshida EM
    [3] => Subcutaneous administration of hepatitis B
immune globulin in combination with lamivudine following orthotopic liver
transplantation: effective prophylaxis against recurrence
    [4] =>  Clin Transplant
    [5] => 2006
    [6] =>  
Jul-Aug
    [7] => 20(4)
    [8] => 524
    [9] => 5
    [10] =>  PubMed 
    [11] => 16842532
) 
 

 
 
 

Thursday, October 3, 2013

AS2 (actionscript 2) Post final score in flash to MySQL databse using PHP


The following actionscript 2 post the data string to sendtest.php and display the feedback result.
Example, we get the scoreTotal  value in flash and want to post it in MySQL database, AS2 script in flash:
 scoreTotal = 100;
var php_process:LoadVars = new LoadVars();
var post_variable:LoadVars = new LoadVars();
    post_variable.string = scoreTotal;
    post_variable.sendAndLoad("http://www.cesei.org/sendtest.php",php_process,"POST");

php_process.onLoad = function(success:Boolean) {
    if (success) {
        my_txt.text +=  php_process.result;
        my_txt.setTextFormat(my_fmt);
    }
    else {
        my_txt.text += "Error connecting to server.";
        my_txt.setTextFormat(my_fmt);
    }
};

my_txt.setTextFormat(my_fmt);
 

  sendtest.php: we need to use "result=" in echo so the feedback can be displayed in flash php_process.result, simple test:

 <?php
if(isset($_POST[string])){
    $upper = strtoupper($_POST[string]);
    echo "result=Your average score $upper".' is posted in CESEI database.';
}
else{
    echo "result=empty string posted";   
}

?>

if it is needed to be  connected to MySQl database:
<?php 
 $host = "localhost";
 $user = "root";
$password = "";
$database = "wrapup";
$link = mysql_connect($host, $user, $password); mysql_select_db($database);
if(isset($_POST[string])){
    $score = mysql_real_escape_string($_POST[string]);
    $addClient = "INSERT INTO data (studentscore) VALUES ('$score')";
   if(mysql_query($addClient) )  echo "result=Your average score $upper".' is posted in CESEI database.';
    else die(mysql_error());
    mysql_close($link); }
else{
   echo "result=empty string posted";
}
?>

Difference betweening pushing button in actionscript 2 (as2) and as3, and more


To push a button in Actionscript 2 (As2), example:
 on (release) {
    play();
}


To push a button in Actionscript 3 (As3), example:
stop();
start_button1.addEventListener(MouseEvent.CLICK, clickFunction);
function clickFunction(event:MouseEvent):void {
gotoAndPlay(2);
}


Other difference between aAS2 and AS3:
AS2
btn_play._visible = false;
AS3
btn_play.visible = false;
vol_slider.visible = false;


AS2
    target = _parent;
AS3
    target = this.parent;

AS2
do_pause = function():Void{
AS3
var do_pause:Function = function():void{

AS2
if(mc._currentframe != mc._totalframes){
AS3
   if(mc.currentFrame != mc.totalFrames){

Difference betweening Creating text filed in actionscript 2 (as2) and as3


In Actionscript 3 (AS3)  to create a text field, example:
 var field:TextField=new TextField();
var format:TextFormat=new TextFormat("Tahoma");
field.defaultTextFormat=format;
field.text="Score: "+"  "+"";
addChild(field);
field.y=300;
field.x=200;


In Actionscript 2 (AS2)  to create a text field, example: 
this.createTextField("my_txt", 1, 100, 120, 300, 120);
my_txt.multiline = true;
my_txt.wordWrap = true;
var my_fmt:TextFormat = new TextFormat();
my_fmt.color = 0x000000;
my_fmt.size = 14;
my_fmt.underline = false;
my_txt.text = "This is my first test field object text.";
my_txt.setTextFormat(my_fmt);