Monday, October 20, 2014

Important queries based formulas to watch performance of a MySQL based application

(1) Session Vs global status variables value
select G.variable_name,G.variable_value GlobalValue,S.variable_value SessionValue from (select * from information_schema.global_status) G inner join (select * from information_schema.session_status) S using (variable_name) where G.variable_name like '$status_variable_name';
Please replace $status_variable_name with relative status variable name.
If you want to list down all similar status variable just prefix and suffix % with $status_variable_name.
Example:- If we want to list down all the status variables (Session and Global) related with threads :
select G.variable_name,G.variable_value GlobalValue,S.variable_value SessionValue from (select * from information_schema.global_status) G inner join (select * from information_schema.session_status) S using (variable_name) where G.variable_name like '%Threads%';

Variable_name  GlobalValue      SessionValue

(2) Cache miss rate: If this value should be <=0.01
select format(TC*100/CN,2) CacheMissRate from (select variable_value TC from information_schema.global_status where variable_name='Threads_created') A,(select variable_value CN from information_schema.global_status where variable_name='Connections') B;

(3) Cache hit rate: This value should be >=90
select format((QH/(QH+QI+QnC))*100,2) CacheHitRate from
(select variable_value QH from information_schema.global_status where variable_name='Qcache_hits') A,
(select variable_value QI from information_schema.global_status where variable_name='Qcache_inserts') B,
(select variable_value QnC from information_schema.global_status where variable_name='Qcache_not_cached') C;

(4) Key Buffer Used: if result is (<=100 ) then your all indexes are cached into key_buffer
if result is (>100) then your all indexes are not cached into key_buffer you may gain performance boost by increasing key_buffer_size.

select format((Index_Size)/(kbsmb/(1024*1024)) * 100,2) as key_buffer_used
(SELECT SUM(INDEX_LENGTH)/(1024*1024) 'Index_Size' FROM information_schema.TABLES where ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema')) A,
(select variable_value kbsmb from information_schema.global_variables where variable_name='key_buffer_size') B;

(5) Table_open_cache optimum value: open_files_limit should also higer than this value.
(max_connections * (maximum number of innodb tables per join + 2*maximum number of MyISAM tables per join))+ $x
Where $x is few reserved value. As per the size of Database and uses, we should assign $x value.

(6) List down table properties: We can add more column names in the select list
SELECT table_name,table_type,Engine,version,table_rows FROM information_schema.tables WHERE table_schema = 'DBname';

(7) List down slow queries: N is the maximum number in seconds. After this value, query will be treated as slow query.
SELECT INFO FROM INFORMATION_SCHEMA.PROCESSLIST  where command !='sleep' and time>=N and command !='Binlog Dump'  order by TIME desc;

(8) List down command wise processlist:
select command, count(command) as CNT from information_schema.processlist group by command;
Example of result:
Command        CNT
Binlog  Dump 1
Query 2
Sleep 12

(9) List down state wise processlist:
select state, count(state) from information_schema.processlist group by state;
Example of result:

State Count (state)
executing 1
Reading from net 1
Writing to net 1
Sorting 1

(10) List locked tables: List down all the locked tables
show open tables where name_locked>0;

Tuesday, September 2, 2014

Start with MongoDB - A new experience with NoSQL database

I will not discuss about what MongoDB is?
MongoDB official website itself provides all the information what you need. Also, there is a lot of information available on Internet when you search Why there is a need of NoSQL database?
Below are the steps to start with MongoDB in Windows:
1. Download the appropriate version from Download page.
2. Double click on installer and follow the installation wizard.
By default, you will not see any service named as MongoDB in Services window.
You have to create a configuration file, log file and data directory and mention the path of log file and datapath in the configuration file.
3. Locate the path of installed MongoDB in your system.
4. MongoDB is not restricted with any specific path for data directory or log path. It depends upon you what path you assigned in configuration file.
For example, MongoDB has been installed in C:\Program Files\MongoDB 2.6 Standard\
Important note: if directory name contains space then we must put the path within double quote.
5. Open the Command Prompt as administrator. (Right click on the CMD link and select "Run as administrator").
6.  Create data directory. 
C:\Program Files\MongoDB 2.6 Standard\bin>md data
C:\Program Files\MongoDB 2.6 Standard\bin>cd data
C:\Program Files\MongoDB 2.6 Standard\bin\data>md db
C:\Program Files\MongoDB 2.6 Standard\bin\data>cd..
C:\Program Files\MongoDB 2.6 Standard\bin>cd..
7. Create log path
C:\Program Files\MongoDB 2.6 Standard>md log
C:\Program Files\MongoDB 2.6 Standard>cd log
C:\Program Files\MongoDB 2.6 Standard\log>type NUL > mongo.log
C:\Program Files\MongoDB 2.6 Standard\log>cd..
8. Create configuration file
C:\Program Files\MongoDB 2.6 Standard>echo logpath="C:\Program Files\MongoDB 2.6
 Standard\log\mongo.log" > "C:\Program Files\MongoDB 2.6 Standard\mongod.cfg"
Concatenate below line in your configuration file:-
dbpath="C:\Program Files\MongoDB 2.6 Standard\bin\data\db"
9. Start MongoDB server
C:\Program Files\MongoDB 2.6 Standard>"C:\Program Files\MongoDB 2.6 Standard\bin\mongod.exe" --dbpath="C:\Program Files\MongoDB 2.6 Standard\bin\data\db"
MongoDB server will be started and you will see that MongoDB server is waiting for connections. Please do not close this window.
10. Open MongoDB shell 
Open another Command prompt with  administrator privileges.
C:\Program Files\MongoDB 2.6 Standard\bin>mongo.exe
Shell prompt will display where you can execute MongoDB commands and statements just like this one.

If you want to avoid to start MongoDB server each time, you can install this as a service too.
11. Install MongoDB sevice in windows system:
C:\Program Files\MongoDB 2.6 Standard>"C:\Program Files\MongoDB 2.6 Standard\bin
\mongod.exe" --config "C:\Program Files\MongoDB 2.6 Standard\mongod.cfg" --dbpat
h "C:\Program Files\MongoDB 2.6 Standard\bin\data\db" --logpath "C:\Program File
s\MongoDB 2.6 Standard\log\mongo.log"  --install

12. Start MongoDB service:
You will see that MongoDB is also listed in your services window ( Windws button+R -> type services.msc). From here , you can click on start.
Alternatively, you can issue below line from command line:
C:\>net start MongoDB
You will see the MongDB shell.

13. Stop MongoDB service:
First execute exit command in MongoDB shell.
C:\>net stop MongoDB

14. Uninstall MongoDB sevice:
C:\Program Files\MongoDB 2.6 Standard>"C:\Program Files\MongoDB 2.6 Standard\bin
\mongod.exe" --remove

To proceed with DB operations like creating database, select database and other operations, please visit MongoDB official tutorial.

Thursday, July 31, 2014

Must read topics before working on performance improvement of MySQL based applications

Are you going to work on improving performance of an MySQL based applications? If yes, this post is for you.
MySQL has a very good documentation. On this left panel of this page, you will find reference manual list for each available version. If you are reading something in MySQL-5.1 and want to check the same topic in MySQL-5.6 you have to just change a single value in URL. Below is the example:
Below page describe the server status variable in MySQL-5.1
while below page describe the server status variable in MySQL-5.6
There is a single difference between these two URLs (5.1 Vs 5.6)

I am providing below URL list which must be covered before or during performance improvement project. I am mentioning for 5.6, if required you can change the version in URL:

1. List of URLs for MySQL Performance Tuning: Benchmarks, Best Practices, FAQs,92131,92131#msg-92131
2. Find information about all status variables
3. Find information about all server system variables
4. MySQL Glossary - very important to understand the database terms
5. Optimization - contains multiple chapters
6. Understanding Query by Explain command
7. Partition Management
8. Server SQL modes
9. How MySQL Uses Threads for Client Connections
10. How MySQL Opens and Closes Tables
11. General Thread States
12. Thread Command Values
13. Query Cache Thread States
14. The MySQL Query Cache
15. MySQL Cluster Overview - only if you have implemented  MySQL cluster database
16. MySQL option files - information about configuration file and group mentioned in that file
17. Query Cache Status and Maintenance
18. Query Cache Configuration
19. How MySQL Uses Internal Temporary Tables
20. Optimizing Disk I/O

Hopefully you would have enough understanding after going through these document pages of MySQL. Feel free to drop your valuable comment if you want to discuss about anything.

Wednesday, July 30, 2014

Install and uninstall of MySQL server and client on RHEL / Fedora

Please note that yum is case sensitive and it maintain its own database.

Download MySQL installer and placed into a directory. Unzip that file. For example, you have placed all installer into mysql_installer directory within /u000.
For installation of MySQL-5.6 in RHEL / Fedora server, Prashanth written a very good post.
1. Install shared library first
 sudo rpm -ivh /u000/mysql_installer/MySQL-shared-community-5.x.y-1.rhel5.x86_64.rpm
If you face any compatibility issue, try to install another library:
sudo rpm -ivh /u000/mysql_installer/MySQL-shared-compat-5.1.73-1.rhel5.x86_64.rpm
2. Install mysql server
sudo rpm -ivh /u000/mysql_installer/MySQL-server-community-5.x.y-1.rhel5.x86_64.rpm
Preparing...                ########################################### [100%]
1:MySQL-server-community ########################################### [100%]
You will get message for further instructions required to follow.
MySQL-5.1 has empty password while for MySQL-5.5 and MySQL-5.6 stored default password in a file /root/.mysql_secret

3. Install mysql client
sudo rpm -ivh /u000/mysql_installer/MySQL-client-community-5.1.73-1.rhel5.x86_64.rpm
Only after installing the client, you would be able to use MySQL prompt - mysql>

User management
Login with default credentials ( username root and password mentioned in file for MySQL-5.6, empty for MySQL-5.1)
$ mysql -u root -p****
Now, you will get mysql> prompt

mysql> create user 'newuser'@'hostname' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

If you want to create user for external access like from application or from any client tool.
mysql> create user 'newuser'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

Grant permission (all means - insert, update, delete, select, drop, shutdown, reload, process, file, grant, refrences, indexes, alter etc.)
mysql> GRANT ALL ON *.* TO 'newuser'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
Above command is not secure. You are granting all permissions to this user and that user can make connection from everywhere. So, take precaution when granting permissions.

Now try to access from outside ( from your application or from tool by using hostname, username and password provided in previous mysql command by you).

In the installation information, you will find the location of my.cnf file. There is a single my.cnf file in MySQ-5.6 (/usr/my.cnf) while there are multiple my.cnf files available in different location for MySQL-5.1. To find all location, please run below command
mysql --verbose --help | grep -C3 my.cnf

Below is the command to start / stop the mysql service:

  1. Stop MySQL service  - sudo /etc/init.d/mysql stop
  2. Start MySQL service - sudo /etc/init.d/mysql start
  3. Restart (stop + start) - sudo /etc/init.d/mysql restart 

You can update the parameters values in my.cnf file. After change, don't forget to restart the mysql service.

    Uninstall MySQL completely

Remove mysql server - please note that server version. It should be same as mentioned during installation.
sudo yum remove MySQL-server-5.x.y-1.rhel5.x86_64 MySQL-server

Now, remove the MySQL client:
sudo yum remove MySQL-client-community-5.1.73-1.rhel5.x86_64.rpm

In the same way, uninstall MySQL library:
sudo yum remove MySQL-shared-community-5.1.73-1.rhel5.x86_64.rpm

Clean db cache
sudo yum clean dbcache

About Linux and some useful commands

The most popular Linux distribution (and very good for newbies) is Mint, followed by Ubuntu. Both are based on the Debian packaging system (Ubuntu is based on Debain and Mint is based on Ubuntu). On the other side of the divide, you have the RPM systems, the most popular one for end users are Fedora and openSuSe.
Then you have the various source based ones like Arch and Gentoo.
As a new user, I would recommend you try one of Mint, Ubuntu or openSuSe or perhaps Manjaro.
there is a Mint Debian Edition you could try.

Debian is not user friendly. It is not very scary either but Mint actually actively tries to be user friendly while Debian does not.
Well, Debian is a distribution. RPM is a package format. The two main package formats in the Linux world are .deb (used by Debian and its derivatives) and RPM (used by RedHat/Fedora and their derivatives).
That is true for all Debian based distributions. Kind of true for RPM-based ones too but they often have fewer choices immediately available from their repositories.
Well, Linux is just a kernel. Think of that as the core of the operating system. A distribution is a collection of software (kernel+tools) that all together make a working operating system. Different distributions have set up a different ecosystem around the core Linux kernel.
 from the perspective of a new user, the most important differences are 1) the package management system (which defines how you can install/manage your software) and 2) the default graphical environment (the Desktop Environment, or DE).
 While any DE can be installed on any distribution, different distros have chosen different DEs as default and that can affect your choice if you don't feel comfortable installing one yourself.

1. Start, stop, restart  a service:
sudo service service_name start
For example, if want to start mysql service, execute command like: sudo service mysql start
sudo service service_name stop
sudo service service_name restart

2. Find a file or directory:
locate xyz
find directory_path -name "file_name / directory_name"
you can also use wildcard characters. For example sudo find /bin -name "mysql*"
If you want to search a file in all the sub-directories, run below command:
find . -name "filename" - you can also use wildcard characters.
For example: find . -name 'abhishek_xyz*.csv'

3. To collect memory / CPU information of Linux server
 cat /proc/meminfo
cat /proc/cpuinfo

4. display the contents of the fstab configuration file, which contains all partitions and storage devices connected to the computer, both internal and external:
sudo cat /etc/fstab

5. Describe Filesystem,Size,Used,Avail,Use%, Mounted on
df -h

6. Describe Filesystem,Size,Used,Avail,Use%, Mounted on
df -h

7. find the filesystem of below directory:
df /home

8. List all the installed applications:
rpm -qa | less or
yum list installed

9. Know all the processor running status
mpstat -P ALL

10. Know CPU running history:

11. Find out running Linux kernel version:
uname -mrs  or
uname -a  or
cat /proc/version

12. Display list of all open ports
netstat -tulpn

13. List PID of all the HTTP applications running on Port 80
 fuser 80/tcp

14. Display list of all running tasks likewise task manager of windows server

15. Unzip a tar  and zip file
tar -zxvf data.tar.gz
unzip sourcefilename destinationfilename

15. List directories under a file system
tree -LP levelnumber partitionNumber
The -P command stands for pattern and L is level Max display depth of the directory tree.
Make sure your in the / directory when running this command
For example:
If you have 4 partitions of your hard disk likewise sda1,sda2,sda3,sda4 and you want to list all the files and directories till 2 levels only under partition sda2: run the below command:
tree -LP 2 /dev/sda2

16. Copy a directory from source to destination
cp -avr sourceDIR destinationDir
For example:
cp -avr /var/lib/mysql/proddb /var/lib/mysql
-a : Preserve the specified attributes such as directory an file mode, ownership, timestamps, if possible additional attributes: context, links, xattr, all.
-v : Explain what is being done.
-r : Copy directories recursively.

Monday, July 7, 2014

MySQL status variables: Difference between Created_tmp_tables and Created_tmp_disk_tables

MySQL has two very important types of variables: -

  1. Global variables
  2. Status variables

For optimum performance, global variables should be tuned as per the application usage and available resources. It is recommended to tune (increase or decrease the value) one parameter (global variable) a time and then use the system with peak load and check the value of respective status variables. These status variables indicate positive and negative affect of tuned global variable.
Usually, status variables are counter variable since start of MySQL service.

There are two status variable - created_tmp_tables and created_tmp_disk tables.
These variables are related with  tmp_table_size and  max_heap_table_size
Since max_heap_table_size is specially for memory storage engine. If your database hasn't any table which storage engine type is not memory, we can leave it.
Created_tmp_tables is increased when server creates an internal temporary tables either in memory or in disk. Temporary tables created in memory if size is small and placed on disk when size increased. This size is determined by tmp_table_size and max_heap_table_size global variables.
Created_tmp_disk_tables is increased when server creates an internal temporary tables only on disk.
This is the reason we always find value of created_tmp_tables is always higher than created_tmp_disk_tables.

Created_tmp_tables = Created_tmp_disk_tables + Temporary tables open in memory

Please feel free to input your comments...

Friday, October 5, 2012

MySQL Query Optimization - avoid temporary tables in query execution

Hey folks,

After a long interval, I have again returned to my blog. Sorry for this long gap.

I am going to share a very important part of web development. It is the query optimization. For any web application irrespective of their technologies, SQL is query required to insert the records as well as fetch the records from database. If the number of tables/records are less, there is no major effect on the performance of the website. But, if we have to fetch the data by joining multiple tables and one or more tables contains huge amount of records, site performance degrade and it increase a heavy load on the web server as well as database server. So, writing optimized query is very important. We must follow the RDBMS rules while designing the database for an application.
Starting out improving performance with MySQL (assuming you don't already know what the problem query is) is to check the slow query log - it logs to a file all queries taking longer than x seconds.

Lets focus on optimization of queries:
During the work, I found a keyword "Explain" and "Explain Extended".
Please visit below urls to get the details knowledge about these: (Optimizing Queries with Explain) (Explain Extended Ouptput Format)
explain is anonymous of describe. But, it works magically when we use this keyword before any select query. After execution, it returned following fields:

Below is the best tutorial which will help you to understand in and out of Explain:

Below are some common things which must be taken care while writing / optimizing queries:
  • Try to avoid In clause
  • Add Index for those columns which are frequently used by the query
  • Arrange the WHERE clause properly - In WHERE clause write those conditional statements first which are going to filter more records.
  • Setup MySQL query cache and give it more RAM if possible
  • Put a timestamp instead of now() function in mysql query
  • If required add new columns
  • If your query has Group by and /or order by clause on a column, then try to place the table of that respective column at first place in joining of multiple tables. For example, if you have applied Group by and /or order by clause on column X of table XYZ then try to place XYZ table as first table in join. This effort will avoid creation of temporary tables.
  • Try to avoid Blob or Text column in your column. Use these data types as a last option. If it happens - Temporary tables will create on Disk only. 
  •  Place Group by or distinct clause on smaller columns (size <=512 bytes).
  •  Try to avoid Union / Union ALL in your queries.
  • GROUP_CONCAT() or COUNT(DISTINCT) evaluation also tend to create temporary tables. If possible, you can also try to avoid such clauses.
  • If executing update statement which will update multiple tables also tend to create temporary tables.

Monday, May 9, 2011

Number format in javascript and php

In PHP, number_format() is a built-in function for formatting of an integer or float number with grouped thousands. But, in javascript, there is no such function for formatting of a number. We need to define write own custom function in javascript which will work exactly like number_format() function in PHP.

string number_format ( float $number [, int $decimals = 0 ] )
string number_format (float $number , int $decimals = 0 , string $dec_point = '.' , string $thousands_sep = ',')
$number=10527;  echo number_format($number);
// output will be 10,527
$number=1052.87;  echo number_format($number); 
//output will be 1,053
$number=105246.87;  echo number_format($number,3,'.','\''); 
//output will be 105'246.870 . Here, I have specified thousand separator as '. Default thousand separator is ,
Note: in this function, 2nd, 3rd and 4th parameter are optional. But, if u supply 3rd parameter, 4th parameter becomes mandatory. Otherwise, it can generate warning message - Wrong parameter count.

<script language="javascript">
function number_format(a, b, c, d) {
 a = Math.round(a * Math.pow(10, b)) / Math.pow(10, b);
 e = a + '';
 f = e.split('.');
 if (!f[0]) {
 f[0] = '0';
 if (!f[1]) {
 f[1] = '';
 if (f[1].length < b) {
 g = f[1];
 for (i=f[1].length + 1; i <= b; i++) {
 g += '0';
 f[1] = g;
 if(d != '' && f[0].length > 3) {
 h = f[0];
 f[0] = '';
 for(j = 3; j < h.length; j+=3) {
 i = h.slice(h.length - j, h.length - j + 3);
 f[0] = d + i + f[0] + '';
 j = h.substr(0, (h.length % 3 == 0) ? 3 : (h.length % 3));
 f[0] = j + f[0];
 c = (b <= 0) ? '' : c;
 return f[0] + c + f[1];

 var number=105246.87;
// output will be 105'246.9

Please do not hesitate to give your valuable suggestions.

Wednesday, April 20, 2011

How to use PHP code in HTML file

Many places, we feel that we can give better result if it would possible to use PHP code instead of javascript code in HTML page. Below is the way to use php code in the html page.

Html file name is "abc.html" :
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<title>Use PHP in HTML file</title>

<!-- We can also pass the parameters to PHP as query string -->
<script src="xyz.php?uid=10&pid=string value"></script>
PHP file name is "xyz.php":
$mytime=date('m-d-Y H:i:s');
echo "document.write('Your IP is <b>$ip</b><br />Server date and time is <b>$mytime</b>');";
echo "document.write('<br />$pid');";

Output :
Your IP is
Server date and time is 04-27-2011 01:01:44
string value

 Please feel free to provide your valuable feedback.

Tuesday, April 19, 2011

Enhanced Extravote plugin in joomla

Extravote is a very popular and useful 5 star rating plugin to rate an article. So, if a page contains multiple content items, it will display mulitple times on a page and works great. We can also use this plugin in third party extensions. But, there is a major issue with this plugin which has not been considered by developers.

About the issue : Just click on a star to give your rating for an article. Your vote will be counted and system will display message "Thanks for voting". If you try to vote again, system will display message "You have already voted". But, with this message, total vote will be decreased by 1. It will be corrected only when you refresh the page. If you are the first one to vote an article, you will notice that, after repeating the vote second time, you will get the message "You have already voted" and total vote become 0. I have consider the issue and debug the code. Since, this plugin is using AJAX for whole functionality, value passed to the javascript function don't change. Finally, I have change the way of implementation and it woks without any issue.
I have not only fix this issue, also enhanced its functionality.

Monday, April 4, 2011

Call plugin in third party extension of joomla

global $mainframe;
        $dispatcher =& JDispatcher::getInstance();
        $articleObject=new ContentModelArticle();
        $id= JRequest::getVar('content_id');
        $limitstart    = JRequest::getVar('limitstart', 0, '', 'int');
        $item=new JObject;
        $item->text = $articleObject->getArticle(); 
        $item->params = clone($mainframe->getParams('com_content'));
        $results=$dispatcher->trigger('onBeforeDisplayContent', array (& $item->text, & $item->params, 0));
        echo $results[0];

Saturday, April 2, 2011

FFMPEG - a boon from open source community

FFMPEG an open source software is a product of Google summer of code. It is very useful in the manipulation of video, audio and image. Benefits of FFMPEG are below:
  • Extract n number of thumbnails from video and vice-versa
  • Convert types of video and audio
  • Extract audio and video for specific duration from audio/video
  • Extract audio from video
  • Mix audio and video
  • Extract thumbnails from an image
It is very useful for web applications because in many cases, we need to render light weight images and videos.
Click here to download ffmpeg software
Click here to download ffmpeg help and list of commands
Click here to download ffmpeg FAQ
Click here to download ffmpeg commands with examples
Click here to download supported  file formats and codecs for Audio and video files 

    Wednesday, March 23, 2011

    Forcing to download a zip file in all browsers

    Have you ever gotten a chance to force the browser for downloading a zip file? Hopefully yes. You will not get any issue unless and untill it contains multiple zip files i.e. nested zip files. Behavior is also different for IE and firefox. Below is the code to download a zip file(may be single level or nested)

     $yourfile=""; // file which is stored on the server
     $dfile="";  // name of the file which will be downloaded
     $fp = @fopen($yourfile, 'rb');
     if (strstr($_SERVER['HTTP_USER_AGENT'], "MSIE"))
            header('Content-Type: "application/x-zip-compressed"');
            header('Content-Disposition: attachment; filename="'.$dfile.'"');
            header('Expires: 0');
            header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
            header("Content-Transfer-Encoding: binary");
            header('Pragma: public');
            header("Content-Length: ".filesize($yourfile));
            header('Content-Type: "application/x-zip-compressed"');
            header('Content-Disposition: attachment; filename="'.$dfile.'"');
            header("Content-Transfer-Encoding: binary");
            header('Expires: 0');
            header("Content-Length: ".filesize($yourfile));

    Provide your valuable comments please.............if you get any issue or wanna anything to share.

    Friday, January 28, 2011

    how to configure Phpmyadmin and use mysql on terminal in UBUNTU/LINUX

    To configure phpmyadmin, use synaptic Package manager.

    To run mysql on terminal, simply go to terminal and write command as below:

    aanand@aanand-desktop:~$ mysql --user=root --password=root
    Here, you can user the user and password as you have supplied during installation.

    Now, you will see messages as below:

    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 26
    Server version: 5.0.51a-3ubuntu5.7 (Ubuntu)

    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


    Your system is ready to run mysql commands and execute queries.
    To test, you can write commands like
    mysql>show databases;
    mysql>use test;
    mysql>show tables;
    To get the help, use help command as below:

    Sunday, January 16, 2011

    The best way to handle date and time in PHP and MySQL

    Usually we use date() function with their different formats. When we have to store the date in table, we convert the date in yyyy-mm-dd format if data-type of column is Date or yyyy-mm-dd HH:mm:ss format if data-type of column is datetime . In the same way, before use of these values, after fetching, need to convert these according to our required format. But, doing all the above process, we have to use explode() and list() function many times. But, using strtotime() is the best way to handle date in different formats.
    strtotime() is a function that converts a date which is in string format into unix timestamp. Please select the data-type as timestamp instead date or datetime when define table structure.
    At the time of saving, we have to just pass the date value which is in string format as argument of strtotime() function and get  the timestamp as output of this function. Now, we can save this timestamp in the table.
    At the time of using this value, after fetching pass this timestamp value in the date() function as second parameter. Format will be as below:
    //Before saving the value in table,
    $timestamp_field_value=strtotime('2010-12-23 12:55:32');
    //After fetching the value from table,
    echo date('Y-m-d',$timestamp_field_value);

    Sunday, January 9, 2011

    How do we upload large files on server using PHP

    Though PHP presents a very versatile and user friendly interface for handling file uploads, the default installation is not geared for working with files in excess of 2 Mega Bytes. This article is an aid to configuring PHP for handling such large file transfers.
    Windows Apache Web Server Configuration: Installation Guide for Apache 2.0.x Web Server - WAMP
    The php.ini file contains all the configuration settings for your installation. Sometimes these setting might be overridden by directives in apache .htaccess files or even with in the scripts themselves but for the moment let's just concentrate on the ini file.
    This file contains the following settings that we need to modify
    • file_uploads [ default is on]
    • upload_max_filesize [default is 2mb, 4 times more than size of file to be uploaded]
    • max_input_time [default is 60, increase it to 300]
    • memory_limit [default is 128M, increase it to 256M]
    • max_execution_time [default is 30, increase it to  500]
    • post_max_size [default is 8MB, it should be greater than upload_max_filesize]
    The first one is fairly obvious if you set this off, uploading is disabled for your installation. We will cover the rest of the configuration settings in detail below.
    upload_max_filesize and post_max_size
    Files are usually POSTed to the webserver in a format known as 'multipart/form-data'. The post_max_size sets the upper limit on the amount of data that a script can accept in this manner. Ideally this value should be larger than the value that you set for upload_max_filesize.
    It's important to realize that upload_max_filesize is the sum of the sizes of all the files that you are uploading. post_max_size is the upload_max_filesize plus the sum of the lengths of all the other fields in the form plus any mime headers that the encoder might include. Since these fields are typically small you can often approximate the upload max size to the post max size.
    According to the PHP documentation you can set a MAX_UPLOAD_LIMIT in your HTML form to suggest a limit to the browser. Our understanding is that browsers totally ignore this directive and the only solution that can impose such a client side restriction is Rad Upload Applet
    When the PHP engine is handling an incoming POST it needs to keep some of the incoming data in memory. This directive has any effect only if you have used the --enable-memory-limit option at configuration time. Setting too high a value can be very dangerous because if several uploads are being handled concurrently all available memory will be used up and other unrelated scripts that consume a lot of memory might effect the whole server as well.
    max_execution_time and max_input_time
    These settings define the maximum life time of the script and the time that the script should spend in accepting input. If several mega bytes of data are being transfered max_input_time should be reasonably high. You can override the setting in the ini file for max_input_time by calling the set_time_limit() function in your scripts.
    Special Notes:
    Apache Settings
    The apache webserver has a LimitRequestBody configuration directive that restricts the size of all POST data regardless of the web scripting language in use. Some RPM installations sets limit request body to 512Kb. You will need to change this to a larger value or remove the entry altogether.
    Other Options
    If you expect to handle a large number of concurrent file transfers on your website consider using a perl or java server side component. PHP happens to be our favorite web programming language as well but perl is just slightly ahead when it comes to file handling.
    Most installations of perl as an apache module can accept up to 32 megabytes out of the box. Compare this against the 2MB default for PHP. The downside is that perl coding takes just a bit more effort than PHP but it's worth it.
    Installing & Configuring Web Servers Using Apache

    Sunday, January 2, 2011

    How to configure PHP on IIS server

    1. Install IIS (Required Windows CD to install IIS server).
    2. Go to Control Panel > add/Remove programs windows components > select IIS
    3. Go to Start > control panel > performance & maintenance > Internet Information Services > websites > default websites ---right click--->Select stop
    4. Download PHP from PHP website
    5. Extract the downloaded zip folder (for example php5)and then  see the directory structure.
    6. Copy php5\dev\php5ts.dll  to C:\windows\system32\
    7. Copy php5\ to c:\windows\ and rename it as php.ini
    8. Copy all files from php5\ext\ to c:\windows\sysem32\ or change the path variable to php5\ext\
    9. Open the file php.ini from c:\windows\ and make  changes like  extension-dir=c:\php5\ext
    10. Remove the semicolon which is prefixed from the line extensions=php_gd2.dll
    11. As point 9, we can enable the extensions as per our requirements. Above extensions will enable GD library.
    12. Save the modified php.ini file and close it.
    13. Go to Start > control Panel > performance & maintenance > administrative tool  > IIS > website > Default website ---right click---> properties ---click--->SAPI filter tab ---click--->Add button ----create a new filter [filter name PHP] [Extension: c:\php5\php5isabpi.dll] > OK
    14. Home directory tab > configuration button ---click on add button > Executable ---click on Browse button to select the file (c:\php5\php5isapi.dll)
    15. Provide .php as extension > ok >ok
    16. Go to Start > control Panel > performance & maintenance > administrative tool  > IIS > website > default website ---right click--->start
    Beginning PHP 5.3 (Wrox Programmer to Programmer)

    Q. What is Path environment variable?
    A: A path environment variable contains a list of directories paths in which windows will look for things. To set the path variable,
    Go to start > control panel > system > advanced > environment variables
    Add your new path and click on save

      Thursday, December 30, 2010

      What is AJAX

      AJAX stands for "Asynchronous JavaScript And XML".
      It is a browser technology independent of web server software. It can directly communicate with the server using the Javascript XMLHttpRequest object. We can say that by the use of this object, JavaScript has become able to trade data directly with the server. With an HTTPRequest, a web page can make a request to, and get a response from a web server without reloading the page. The user will stay on same page and he/she will not notice that scripts request pages or send data to a server in the background. The best example which we she everyday is google suggest. Now, we can assume how much an AJAX application is useful! But, it is not useful everywhere. It has also some drawbacks too. So, it is not preferable to apply AJAX everywhere.
      There are two ways that AJAX can access the server.
      Synchronous :- In this way, script stops and waits for the server to send back a reply before continuing.
      Asynchronous :- In this way, script allows the page to continue to be processed and will handle the reply if and when it arrived. Due to implementation of this way, processing become richer,user friendly, smoother as well as reduce the gap between desktop application and web application.
      AJAX is based on following web standards:
      • JavaScript
      • XML
      • HTML
      • CSS
      Different browsers uses different methods to use XMLHttpRequest object. For example, Firefox,Opera,Safari,Netscape use JavaScript built-in method - XMLHttpRequest while IE uses ActivexObject.

      Properties of  XMLHttpRequest object
      1. onreadystatechange
      2. readystate
      3. responseText
      4. responseXML
      5. status
      6. statusText  
      Methods of  XMLHttpRequest object
      1. open()
      2. send()
      3. abort()
      4. getAllResponseHeaders()
      5. getResponseHeaders()
      6. setRequestHeader()
      Best way to use AJAX using jQuery is below:
      google.load("jqueryui", "1.8.2");
      var total;
      total = jQuery.ajax({url: live_site+"/plugins/content/extravote/ajax.php",global: false, type: "POST",data: ({task : "getrating",cid:id,xid:xid,column_name:"rating_sum",dummy:new Date().getTime()}),
            dataType: "text",async:false,
            success: function(msg){ }

        Prototype in javaScript

        Prototype is a prebuilt object  in javascript (introduced in version 1.1) which makes it possible to add one or more properties/methods in a single or all instances of an object (both types of object - prebuilt and custom).
        Case 1: Below is the example to add custom property to single instance only:
        //adding a custom property to a prebuilt object
        var myimage=new Image()
        /*adding a custom property to the custom object "circle"*/
        //First, create the custom object "circle"
        function circle(){
        var smallcircle=new circle()
        Case 2: We have to use prototype keyword to apply a property to all instances of an object Below is an example:
        //First, create the custom object "circle"
        function circle(){
        // create the object method
        function alertmessage(){

        Now, all instances of circle() now has the pi property prebuilt into them. Also, all instances of the circle object contain a alertmessage() method!

        case 3: Extending functionality to the pre-built string() object
        We can use the prototype object on any custom objects. But, it is not for prebuilt object only. Javascript allows us to "prototype" prebuilt objects that are created with the new keyword like string object, image object, Array object, date object.

        <script type="text/javascript">
        /*code for extending String object with method that writes text backwards*/

        //core custom method for writing text backwards
        function outputbackwards(){
        for (i=this.length-1;i>=0;i--)
        //Attach custom method to string object
        The above code just added a whole new functionality to the default string object- the ability to output any text backwards! Here are a few examples:
        <script type="text/javascript">
        var message1="Welcome to my site!"
        var message2="Today is a beautiful day"
        !etis ym ot emocleW
        yad lufituaeb a si yadoT
        Web Development and Design Foundations with XHTML (5th Edition)

        Real example of prototype object feature of javascript. Following is the function which is used to trim spaces.
        String.prototype.trim = function ()
        return this.replace(/^\s*/, "").replace(/\s*$/, "");

        Sunday, November 28, 2010

        How to load a php extension in LINUX/UBUNTU

        PHP has a lot of extension. It may be possible that PHP has not compiled with all extensions. If some extension is missing and it is required for our application then PHP need to enable that.
        Enable an extension in Window environment is easy. Open php.ini file and remove the ; before the module and restart the server. But, to enable an extension in LINUX, it is not so easy. Steps are below:
        1. open synaptic with admin controls...    
                sudo synaptic
        2. Search your extension which you want to load using search tool
        3. Select extension(s) from the list.
        4. Click on apply. Selected extension(s) will be installed


        5. Now open php.ini file (/etc/php5/apache2/php.ini) and add following line
        ;for windows environment, use .dll

        ;for LINUX environment, use .so
        For example, to install curl,ffmpeg and json

        6. Save the file and restart the apache by writing following line in terminal window.
        sudo /etc/init.d/apache2 restart

        The most easiest way to install an extension in ubuntu flavor is below:
        Open Applications > Accessories > terminal
        write sudo apt-get install extension1 extension2 extension3.
        For example, to install curl extension, command should be like this
        sudo apt-get install curl libcurl3 libcurl3-dev php-curl