__Onkar: 2008

Union All is used to select all values including
duplicates from the tables

both UNION ALL and UNION distinct use temporary table for result generation.

The difference in execution speed comes from the fact UNION requires internal temporary table with index (to skip duplicate rows) while UNION ALL will create table without such index.

Interesting enough the fact UNION and UNION ALL require temporary table can only be seen in SHOW STATUS - EXPLAIN does not want to tell you this shameful fact.

In fact EXPLAIN output is the same for UNION and UNION ALL

Q: is UNION ALL indeed faster than UNION DISTINCT (the UNION is shortcut for UNION DISTINCT) ?

A: yes it is.

Union vs. Union All

In simple we can say that
1. union is used to select distinct values from two
tables,where as union all is used to select all values
including duplicates from the tables.

2. The UNION operator allows you to combine the results of
two or more SELECT statements into a single result set. The
result sets combined using UNION must all have the same
structure. They must have the same number of columns, and
the corresponding result set columns must have compatible
data types.By default, the UNION operator removes duplicate
rows from the result set. If you use UNION ALL, all rows
are included in the results and duplicates are not removed.

Wednesday, September 17, 2008

Mysql Prompt on on SSH client

1)Login with primary acnt
2)Login with su acnt
3)type command mysql
4)then prompt appears mysql >
5)mysql > use ;
6)start writting triggers here

Friday, September 12, 2008

Function to get Date time duration for display

Mp3 player in PHP

Upload a File or image function

## Function _fnUploadFile processing the Uploadedfile StaticPages sub module

function _fnUploadFile($strOldFileName,$strUpFileName,$strFolderName,$strFileTypes="img",$height=0,$width=0,
{

### SOLID FILE UPLOADING CODE ###

if($strFileTypes == "img")
{
$arrAllowImageTypes = array("jpg","jpeg","gif","png");
//$arrAllowImageTypes = array("jpg");
//$strFileTypes = "\"jpg\",\"jpeg\",\"gif\",\"png\"";
}
elseif ($strFileTypes == "doc")
{
$arrAllowImageTypes = array("doc","pdf","rtf","txt","xls","html","html","ppt","csv");
//$strFileTypes = "\"doc\",\"pdf\",\"rtf\",\"txt\"";
}
elseif ($strFileTypes == "video")
{
$arrAllowImageTypes = array("mp4","wmv","mov","mpg","avi","flv");
//$arrAllowImageTypes = array("flv");
}
elseif ($strFileTypes == "podcast")
{
$arrAllowImageTypes = array("mov","mp3","mpe","mp4","mpeg");
}
elseif ($strFileTypes == "csv")
{
$arrAllowImageTypes = array("csv","xls");
}
elseif ($strFileTypes == "resume")
{
$arrAllowImageTypes = array("doc","pdf","rtf","txt");
}

$strImgFileSize = (int)$_FILES[$strOldFileName]["size"];
if($strImgFileSize)
{
if($strFileTypes=="video"&&$strImgFileSize>10485760)
{
$strErrorMessage .= "

Please upload video up to 10 MB.

";
}
elseif($strFileTypes=="img"&&$strImgFileSize>2097152)
{
$strErrorMessage .= "

Please upload image up to 2 MB.

";
}
else
{
$strFileSuccess=1;
$strImgFileName = $_FILES[$strOldFileName]["name"];
$strImgFileError = (int)$_FILES[$strOldFileName]["error"];
if($strImgFileError)
{
$strErrorMessage .= "$strImgFileName Error in uploaded File
"; $strFileSuccess=0;
}
if((int)$strFileSuccess)
{
$strImgFileName = $_FILES[$strOldFileName]["name"];
$strImgFileTmpName = $_FILES[$strOldFileName]["tmp_name"];

#-- get the file extention
$arrImgFileExtention = array_reverse(explode(".",$strImgFileName));
$strImgFileExtention = strtolower($arrImgFileExtention[0]);

#-- create new unique filename to avoide file overwriting situation --#
//$strImgFileNewName = $strUpFileName."_".time().".".$strImgFileExtention;
$strImgFileNewNameTemp = $strUpFileName."_".time();
$strImgFileNewName = $strImgFileNewNameTemp.".".$strImgFileExtention;

# -- check extention in allowed exptentionlist --#
// $arrAllowImageTypes = array($strFileTypes);
// print_r($arrAllowImageTypes);
if(!in_array($strImgFileExtention,$arrAllowImageTypes))
{
$strErrorMessage .= "$strImgFileExtention File Type not allowed for $strImgFileName
"; $strFileSuccess=0;
}
if((int)$strFileSuccess)
{
#-- move file to server --#
if($strFileTypes == "img")
{
$arrHeightWidth = array();
// $arrHeightWidth = clsUtil::fnSetImageHeighWidth($strImgFileTmpName, $height, $width);
if($strSmallImgNewFileName != "")
{
image_resize($strImgFileTmpName,DOCUMENT_ROOT."/usercontent/".$strFolderName."/".$strSmallImgNewFileName,$height,$width);
}
else
{
image_resize($strImgFileTmpName,DOCUMENT_ROOT."/usercontent/".$strFolderName."/".$strImgFileNewName,$height,$width);
}
}
else
{
if(!move_uploaded_file($strImgFileTmpName,DOCUMENT_ROOT."/usercontent/".$strFolderName."/".$strImgFileNewName))
{
$strErrorMessage .= "$strImgFileName File Could not upload
";
}

}
}
}
}
}
else
{
$strErrorMessage .= "

Please check the size.

";
}

if($strErrorMessage == "")
{
return "success=".$strImgFileNewName;
}
else
{
return "error=".$strErrorMessage;
}

}

function image_resize($source, $dest, $targetHeight=75, $targetWidth=100)
{

$image = new Imagick($source);

$intActualheight = $image->getImageHeight(); // GET ACTUAL HEIGHT
$intActualWidth = $image->getImageWidth(); // GET ACTUAL WIDTH

$arrDimentions = fnSetImageHeighWidth($source, $targetHeight, $targetWidth, (int)$intActualheight, (int)$intActualWidth);

// If 0 is provided as a width or height parameter,
// aspect ratio is maintained
$image->thumbnailImage($arrDimentions['width'], $arrDimentions['height']);
$image->writeImage($dest);
$image->clear();
$image->destroy();
}

function fnSetImageHeighWidth($strfilename, $intTargetHeight, $intTargetWidth, $intActualHeight=0,$intActualWidth=0)
{
$arrHeightWidth = array();
if($intActualHeight==0 && $intActualWidth==0)
{
list($intWidth, $intHeight) = @getimagesize($strfilename);
}
else
{
$intWidth = $intActualWidth;
$intHeight = $intActualHeight;
}

if($intWidth == 0 || $intHeight == 0)
{
$arrHeightWidth['width'] = $intWidth;
$arrHeightWidth['height'] = $intHeight;
return $arrHeightWidth;
}

if ($intWidth > $intTargetWidth)
{
$intNewWidth = $intTargetWidth;
$intNewHeight = round(($intHeight * $intNewWidth)/$intWidth);
if ($intNewHeight > $intTargetHeight)
{
$intNewHeight = $intTargetHeight;
$intNewWidth = round(($intWidth * $intNewHeight)/$intHeight);
}
$arrHeightWidth['width'] = $intNewWidth;
$arrHeightWidth['height'] = $intNewHeight;
}
elseif ($intHeight > $intTargetHeight)
{
$intNewHeight = $intTargetHeight;
$intNewWidth = round(($intWidth * $intNewHeight)/$intHeight);
if ($intNewWidth > $intTargetWidth)
{
$intNewWidth = $intTargetWidth;
$intNewHeight = round(($intHeight * $intNewWidth)/$intWidth);
}
$arrHeightWidth['width'] = $intNewWidth;
$arrHeightWidth['height'] = $intNewHeight;
}
elseif ($intWidth == $intHeight && $intWidth > $intTargetWidth)
{
$intNewWidth = $intTargetWidth;
$intNewHeight = round(($intHeight * $intNewWidth)/$intWidth);
$arrHeightWidth['width'] = $intNewWidth;
$arrHeightWidth['height'] = $intNewHeight;
}
elseif ($intWidth == $intHeight && $intHeight > $intTargetHeight)
{
$intNewHeight = $intTargetHeight;
$intNewWidth = round(($intWidth * $intNewHeight)/$intHeight);
$arrHeightWidth['width'] = $intNewWidth;
$arrHeightWidth['height'] = $intNewHeight;
}
elseif ($intWidth < $intTargetWidth)
{
$intNewWidth = $intTargetWidth;
$intNewHeight = round(($intHeight * $intNewWidth)/$intWidth);
if ($intNewHeight > $intTargetHeight)
{
$intNewHeight = $intTargetHeight;
$intNewWidth = round(($intWidth * $intNewHeight)/$intHeight);
}
$arrHeightWidth['width'] = $intNewWidth;
$arrHeightWidth['height'] = $intNewHeight;
}
elseif ($intHeight < $intTargetHeight)
{
$intNewHeight = $intTargetHeight;
$intNewWidth = round(($intWidth * $intNewHeight)/$intHeight);
if ($intNewWidth > $intTargetWidth)
{
$intNewWidth = $intTargetWidth;
$intNewHeight = round(($intHeight * $intNewWidth)/$intWidth);
}
$arrHeightWidth['width'] = $intNewWidth;
$arrHeightWidth['height'] = $intNewHeight;
}
elseif ($intWidth == $intHeight && $intWidth < $intTargetWidth)
{
$intNewWidth = $intTargetWidth;
$intNewHeight = round(($intHeight * $intNewWidth)/$intWidth);
$arrHeightWidth['width'] = $intNewWidth;
$arrHeightWidth['height'] = $intNewHeight;
}
elseif ($intWidth == $intHeight && $intHeight < $intTargetHeight)
{
$intNewHeight = $intTargetHeight;
$intNewWidth = round(($intWidth * $intNewHeight)/$intHeight);
$arrHeightWidth['width'] = $intNewWidth;
$arrHeightWidth['height'] = $intNewHeight;
}
else
{
$arrHeightWidth['width'] = $intWidth;
$arrHeightWidth['height'] = $intHeight;
}
return $arrHeightWidth;
}

Cron

What is cron?

Actually it is called 'cron daemon'. Cron is an automatic task machine. You will use it on your Unix or Linux operating systems for doing some tasks at specific intervals with out your intervention every time. You set the clock and forget. The cron daemon runs the work for you.

What is cron tab?

'Cron tab(CRON TABle)' is a text file that contains a series of cron functions.

What cron will do for you?

--->If you want to send your email cources to your subscribers at 11.30 night, you will set the cron job on your server.And your cron manager sends the one email every day at 11.30 until all the emails will be finished.

---->If you want to send them on Sundays, you can schedule it with your cron.

--->You can schedule it to delete your website members with expired accounts.

--->You can schedule it to recieve an update on your subscribers from your mailing list manager.

--->You can check your links on other websites in link exchange programms.

Cron Commands:

crontab filename
Install filename as your crontab file. On many systems, this command is executed simply as crontab filename (i.e., without the -a option).

crontab -e
Edit your crontab file, or create one if it doesn't already exist.

crontab -l
Display your crontab file.

crontab -r
Remove your crontab file.

crontab -v
Display the last time you edited your crontab file. (This option is only available on a few systems.)

How to do it?

Basically FOUR steps:

Create cron.txt
Upload
Install the txt file as cron file with the command 'crontab cron.txt'
Check your cron file

Crontab File
A crontab file has five fields for specifying day , date and time followed by the command to be run at that interval.
* * * * * command to be executed
- - - - -
| | | | |
| | | | +----- day of week (1 - 7) (monday = 1)
| | | +------- month (1 - 12)
| | +--------- day of month (1 - 31)
| +----------- hour (0 - 23)
+------------- min (0 - 59)

Examples :
Cron set For Database BackUp

5 21 * * * mysqldump -u root -p --opt specto | gzip > /backup1/abc/spec
to_`date +%Y%m%d%H%M`.gz

Cron Set for run PHP File ..
* 0,6,12,18 * * * /usr/bin/php -q /var/www/html/abc/xyz/expertblog/blogsadmin/runcron.php > /dev/null

RESTART MYSQL SERVER

## RESTART MYSQL SERVER
[root@XYZ xyz]# /etc/init.d/mysqld stop
Stopping MySQL: [ OK ]
[root@XYZxyz ]# /etc/init.d/mysqld start
Starting MySQL: [ OK ]

Copy one folder to another with same permissions

## COPY USERCONTENT FROM LIVE TO DEVELOPMENT [IMAGES, RESUMES, FILES ETC ETC]
[root@XYZ httpdocs]# mv abc/ abc_old
[root@XYZ httpdocs]# mkdir abc
[root@XYZ httpdocs]# chown rootusername:psaserv abc
[root@XYZ httpdocs]# chmod 777 abc
[root@XYZ usercontent]# pwd
/var/www/vhosts/xyz.com/httpdocs/abc
[root@XYZ abc]# cp -pR /var/www/vhosts/xyz.com/httpdocs/abc/*

How to backup the database from server

Login to server via shell using shell details

Enter Following commands:

[root@XYZ]# cd /var/www/vhosts/abc.com/foldername

[root@XYZ backup]# mkdir

[root@XYZ backup]# cd /

## COPY SYSTEM FILES DIRECTLY FROM DEST DATABASE

[root@XYZ ]# cp -pR /var/lib/mysql/dbname/* .

## Ensure if DATA is copied for not

[root@XYZ ]# ls

## list will display here