Archive for February, 2010

MySQL Useful Functions

Posted: February 16, 2010 in Uncategorized

Date Functions

Before looking at the date functions in detail it is worth revisiting the various date datatypes to gain a better understanding of the limitations of date formatting.

Date Datatypes

There are 5 MySQL date datatypes these are:

Datatype Format Info
DATETIME YYYY-MM-DD HH:MM:SS This stores both date and time.
DATE YYYY-MM-DD This only stores the date
TIMESTAMP(length) Varies See Below
TIME HH:MM:SS This stores only the time
YEAR YYYY Stores only the year

The timestamp datatype is somewhat different as it stores the time that a row was last changed. The format also varies according to the length. For example to store the same information as DATETIME, you would specify a length of 14 whereas to store the DATE you would specify a length of 8.

Timestamp Definition Format
TIMESTAMP(2) YY
TIMESTAMP(4) YYYY
TIMESTAMP(6) YYMMDD
TIMESTAMP(8) YYYYMMDD
TIMESTAMP(10) YYMMDDHHMM
TIMESTAMP(12) YYMMDDHHMMSS
TIMESTAMP(14) YYYYMMDDHHMMSS

In the ‘cds’ table we have used the DATE for the ‘bought’ field.

mysql> SELECT cds.title, cds.bought 
    -> FROM cds;
+------------------------------+------------+
| title                        | bought     |
+------------------------------+------------+
| A Funk Odyssey               | 2001-10-10 |
| Now 49                       | 2001-10-15 |
| Eurovision Song contest 2001 | 2000-09-08 |
| Abbas Greatest Hits          | 2000-11-05 |
| Space Cowboy                 | 2001-10-10 |
| Sign of the times            | 1987-11-07 |
| The White Album              | 1994-07-20 |
| The Hits                     | 1993-10-07 |
| westlife                     | 2000-06-09 |
+------------------------------+------------+
9 rows in set (0.02 sec)

DATE_FORMAT()

This function allows the developer to format the date anyway that they wish by specifying a sequence of format strings. A string is composed of the percentage symbol '%' followed by a letter that signifies how you wish to display part of the date. These are some of the more common strings to use:
String Displays Example
%d The numeric day of the month 01....10....17....24 etc
%D The day of the month with a suffix 1st, 2nd, 3rd.... etc
%m The numeric month 01....04....08....11 etc
%M The Month name January....April....August etc
%b The Abbreviated Month Name Jan....Apr....Aug....Nov etc
%y Two digit year 98, 99, 00, 01, 02, 03 etc
%Y Four digit year 1998, 2000, 2002, 2003 etc
%W Weekday name Monday.... Wednesday....Friday etc
%a Abbreviated Weekday name Mon....Wed....Fri etc
%H Hour (24 hour clock) 07....11....16....23 etc
%h Hour (12 hour clock) 07....11....04....11 etc
%p AM or PM AM....PM
%i Minutes 01....16....36....49 etc
%s Seconds 01....16....36....49 etc
There are more, but that should be enough for now. There are a couple of things to note. Upper and Lowercase letters in the string make a difference and also that when arranging these strings into a sequence you can intersperse 'normal' characters. For example: The sequence '%d/%m/%y', with forward slashes separating the strings, would be displayed as 01/06/03. The next stage is to use the function DATE_FORMAT() to convert a stored time to a format we want. Syntax:
DATE_FORMAT(date, sequence)

Thus to change the format of the cds.bought field to DD-MM-YYYY we specify the field as the date and the sequence as '%d-%m-%Y'.

DATE_FORMAT(cds.bought, '%d-%m-%Y')

This function is then incorporated into our SQL statement in place of the exiting cds.bought field.

mysql> SELECT cds.title, DATE_FORMAT(cds.bought, '%d-%m-%Y') 
    -> FROM cds;
+------------------------------+-------------------------------------+
| title                        | DATE_FORMAT(cds.bought, '%d-%m-%Y') |
+------------------------------+-------------------------------------+
| A Funk Odyssey               | 10-10-2001                          |
| Now 49                       | 15-10-2001                          |
| Eurovision Song contest 2001 | 08-09-2000                          |
| Abbas Greatest Hits          | 05-11-2000                          |
| Space Cowboy                 | 10-10-2001                          |
| Sign of the times            | 07-11-1987                          |
| The White Album              | 20-07-1994                          |
| The Hits                     | 07-10-1993                          |
| westlife                     | 09-06-2000                          |
+------------------------------+-------------------------------------+
9 rows in set (0.00 sec)

Extraction Functions

As well as using DATE_FORMAT() there are other functions that allow you to extract specific information about a date (year, month, day etc). These include:
Function Displays Example
DAYOFMONTH(date) The numeric day of the month 01....10....17....24 etc
DAYNAME(date) The Name of the day Monday.... Wednesday....Friday etc
MONTH(date) The numeric month 01....04....08....11 etc
MONTHNAME(date) The Month name January....April....August etc
YEAR(date) Four digit year 1998, 2000, 2002, 2003 etc
HOUR(time) Hour (24 hour clock) 07....11....16....23 etc
MINUTE(time) Minutes 01....16....36....49 etc
SECOND(time) Seconds 01....16....36....49 etc
DAYOFYEAR(date) Numeric day of the year 1.....366
To give an example of one of these you can use DAYNAME() to work out which day you were born on. To do this you can specify the date directly to the function without referring to any tables or field. So for my birthday (20th July 1973):
mysql> SELECT DAYNAME('1973-07-20'); 
+-----------------------+
| DAYNAME('1973-07-20') |
+-----------------------+
| Friday                |
+-----------------------+
1 row in set (0.00 sec)
Advertisements

Transaction in MYSQL

Posted: February 15, 2010 in Uncategorized

Do not forget to use Transaction, when you are writing any Stored-Procedure or function in MYSQL. By Transactionm you can make save points, so that, when a problem exists, it will be rolled back. Saying simply, your sql statements are not committed, till it reaches the last line.

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;
Transaction is well described at http://www.informit.com/articles/article.aspx?p=29312

What Are Transactions?

A transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail. A good example would be a banking transaction, specifically a transfer of $100 between two accounts. In order to deposit money into one account, you must first take money from another account. Without using transactions, you would have to write SQL statements that do the following:
  1. Check that the balance of the first account is greater than $100.
  2. Deduct $100 from the first account.
  3. Add $100 to the second account.
Additionally, you would have to write your own error-checking routines within your program, specifically to stop the sequence of events should the first account not have more than $100 or should the deduction statement fail. This all changes with transactions, for if any part of the operation fails, the entire transaction is rolled back. This means that the tables and the data inside them revert to their previous state.

Properties of Transactions

Transactions have the following four standard properties, usually referred to by the acronym ACID:
  • Atomicity ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.
  • Consistency ensures that the database properly changes states upon a successfully committed transaction.
  • Isolation enables transactions to operate independently of and transparent to each other.
  • Durability ensures that the result or effect of a committed transaction persists in case of a system failure.
In MySQL, transactions begin with the statement BEGIN WORK and end with either a COMMIT or aROLLBACK statement. The SQL commands between the beginning and ending statements form the bulk of the transaction.

Using POP3 with PHP

Posted: February 12, 2010 in Uncategorized

We all know, using SMTP (Sending Mail with PHP). This article covers, how to use POP3 (Receive Mail). Here is the code;

$mbox = imap_open(“{pop.gmail.com:995/pop3/ssl}”, “userid@gmail.com”, “password”);

echo “reached here\n”;

echo “Mailboxes\\n”;

$folders = imap_listmailbox($mbox, “{pop.gmail.com:995}”, “*”);

if ($folders == false) {
echo “Call failed
\n”;
} else {
foreach ($folders as $val) {
echo $val . ”
\n”;
}
}

echo “HEADERS IN INBOX”;

foreach ($headers as $val) {
echo $val . ”
\n”;
}
}

Best Ways to Take MYSQL Backup using PHP

Posted: February 11, 2010 in Uncategorized

There are at least three ways to backup your MySQL Database :

  1. Execute a database backup query from PHP file.
  2. Run mysqldump using system() function.
  3. Use phpMyAdmin to do the backup

Execute a database backup query from PHP file

Below is an example of using SELECT INTO OUTFILE query for creating table backup :

<?php
include ‘config.php’;
include ‘opendb.php’;

$tableName  = ‘mypet’;
$backupFile = ‘backup/mypet.sql’;
$query      = “SELECT * INTO OUTFILE ‘$backupFile’ FROM $tableName”;
$result = mysql_query($query);
include ‘closedb.php’;

?>

<?php
include ‘config.php’;
include ‘opendb.php’;

$tableName  = ‘mypet’;
$backupFile = ‘mypet.sql’;
$query      = “LOAD DATA INFILE ‘backupFile’ INTO TABLE $tableName”;
$result = mysql_query($query);
include ‘closedb.php’;
?>

<?php
include ‘config.php’;
include ‘opendb.php’;

$backupFile = $dbname . date(“Y-m-d-H-i-s”) . ‘.gz’;
$command = “mysqldump –opt -h $dbhost -u $dbuser -p $dbpass $dbname | gzip > $backupFile”;
system($command);

include ‘closedb.php’;

?>

MY-SQL Backup using PHP

Posted: February 11, 2010 in Uncategorized
backup_tables(‘localhost’,’username’,’password’,’blog’);
/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = ‘*’)
{
$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);
//get all of the tables
if($tables == ‘*’)
{
$tables = array();
$result = mysql_query(‘SHOW TABLES’);
while($row = mysql_fetch_row($result))
{
$tables[] = $row[0];
}
}
else
{
$tables = is_array($tables) ? $tables : explode(‘,’,$tables);
}
//cycle through
foreach($tables as $table)
{
$result = mysql_query(‘SELECT * FROM ‘.$table);
$num_fields = mysql_num_fields($result);
$return.= ‘DROP TABLE ‘.$table.’;’;
$row2 = mysql_fetch_row(mysql_query(‘SHOW CREATE TABLE ‘.$table));
$return.= “\n\n”.$row2[1].”;\n\n”;
for ($i = 0; $i < $num_fields; $i++)
{
while($row = mysql_fetch_row($result))
{
$return.= ‘INSERT INTO ‘.$table.’ VALUES(‘;
for($j=0; $j<$num_fields; $j++)
{
$row[$j] = addslashes($row[$j]);
$row[$j] = ereg_replace(“\n”,”\\n”,$row[$j]);
if (isset($row[$j])) { $return.= ‘”‘.$row[$j].'”‘ ; } else { $return.= ‘””‘; }
if ($j<($num_fields-1)) { $return.= ‘,’; }
}
$return.= “);\n”;
}
}
$return.=”\n\n\n”;
}
//save file
$handle = fopen(‘db-backup-‘.time().’-‘.(md5(implode(‘,’,$tables))).’.sql’,’w+’);
fwrite($handle,$return);
fclose($handle);
}

backup_tables(‘localhost’,’username’,’password’,’blog’);

/* backup the db OR just a table */function backup_tables($host,$user,$pass,$name,$tables = ‘*’){

$link = mysql_connect($host,$user,$pass); mysql_select_db($name,$link); //get all of the tables if($tables == ‘*’) { $tables = array(); $result = mysql_query(‘SHOW TABLES’); while($row = mysql_fetch_row($result)) { $tables[] = $row[0]; } } else { $tables = is_array($tables) ? $tables : explode(‘,’,$tables); } //cycle through foreach($tables as $table) { $result = mysql_query(‘SELECT * FROM ‘.$table); $num_fields = mysql_num_fields($result); $return.= ‘DROP TABLE ‘.$table.’;’; $row2 = mysql_fetch_row(mysql_query(‘SHOW CREATE TABLE ‘.$table)); $return.= “\n\n”.$row2[1].”;\n\n”; for ($i = 0; $i < $num_fields; $i++)  { while($row = mysql_fetch_row($result)) { $return.= ‘INSERT INTO ‘.$table.’ VALUES(‘; for($j=0; $j<$num_fields; $j++)  { $row[$j] = addslashes($row[$j]); $row[$j] = ereg_replace(“\n”,”\\n”,$row[$j]); if (isset($row[$j])) { $return.= ‘”‘.$row[$j].'”‘ ; } else { $return.= ‘””‘; } if ($j<($num_fields-1)) { $return.= ‘,’; } } $return.= “);\n”; } } $return.=”\n\n\n”; } //save file $handle = fopen(‘db-backup-‘.time().’-‘.(md5(implode(‘,’,$tables))).’.sql’,’w+’); fwrite($handle,$return); fclose($handle);}

How BTREE Index works ?

Posted: February 11, 2010 in Uncategorized

Reference: http://mattfleming.com/node/192

A team member thought we should add an index on a 90 million row table to improve performance. The field on which he wanted to create this index had only four possible values. To which I replied that an index on a low cardinality field wasn’t really going to help anything. My boss then asked me why wouldn’t it help? I sputtered around for a response but ended up telling him that I’d get back to him with a reasonable explanation.

Now I’m not a DBA by any stretch but I’ve learned about database optimization and performance on the job from some really bright folks. I didn’t have a very good grasp on how indexes worked though. So I did some research on the topic.

There are several kinds of indexes that databases use. Sybase IQ has like 20 different kinds, Oracle and DB2 appear to have two. The main type of index out there is a b-tree; this is the type that most people mean when they say database index.

What is a b-tree?

In a tree, records are stored in locations called leaves. The starting point is called the root. The maximum number of children per node is called the order of the tree. The maximum number of access operations required to reach the desired leaf (data stored on the leaf) is called the depth (level). Oracle indexes are balanced b-trees; the order is the same at every node and the depth is the same for every leaf.

real tree (in nature) b-tree
grows up grows down
main trunk root
branch node
leaf leaf

Binary Tree ——— Balanced b-tree

The bigger the order, the more leaves and nodes you can put at a certain depth. This means that there are fewer levels to traverse to get to the leaf (which contains the data you want). In the example above and all balanced b-trees, the number of hops to a leaf == depth.

How does a b-tree help with database access?

Most indexes are too large to fit into memory, which means that they are going to be stored on disk. Since I/O is usually the most expensive thing you can do in a computer system, these indexes need to be stored in an I/O efficient way.

A b-tree is a good way to do this. If we make the nodes the size of a physical I/O block, it would take one I/O to move to a lower depth in the tree. In the example below, an index was created on a first name kind of field.

DB Index Example

If every level were an I/O it would take 3 I/Os to find Mary (or any other leaf).

How good is the index?

Now back to the original point I was trying to make– low cardinality fields make bad indexes. Why is this the case? The answer here is really about selectivity.

                unique index values
selectivity = -----------------------
                total number records

A primary key is highly selective. If there are 1000 rows, there will be 1000 unique keys in the index. Eacy unique key will return at most 1 row. The index will be 100% selective (1000/1000).. the best you can get.

Now let’s say we have an index on a low cardinality thing like gender. If we had 1000 records, the selectivity is in the database is 2/1000 =.2%. Said in another way, 500 records come back per unique key (1000 records / 2 uniques).

Note: this seems to assume an even distribution of data (e.g. 500 male, 500 female). Things might be different if you had 999 males, and 1 female.

Hand-wavy rule

10% selectivity is the minimum selectivity necessary for a b-tree index to be helpful.

Where to get stuffs ?

Posted: February 4, 2010 in Uncategorized

Many of my friends, oftern ask, where to use web stuffs like, icons, manuals, softwares. I am providing some links here, which may help you;

1. To download icons, visit http://wwww.iconfinder.net

2. User SnagIt, to capture screen, any particular region of a screen and to prepare video manuals. Here is the link;
http://www.techsmith.com/screen-capture.asp

3. For making UML diagrams, use http://www.umlet.com or http://www.visual-paradigm.com

4. For making technical documents like DFD, ERD, FLOW CHART, PSD, e.t.c., use Microsoft Visio, which is also available in bundle with Microsoft Office-2007.

Choosing Credit/Debit Card

Posted: February 3, 2010 in Uncategorized

I am providing you some useful information on surcharges so that you can judge, which card you can use for making payment online;

CREDIT CARDS

American Express: 2.7 percent
Bank of America: 3 percent
Barclaycard/Juniper: 2 to 3 percent
Capital One: 0 percent
Citibank/Diners: 3 percent
Diners Club: 3 percent
HSBC: 3% (most)
JP Morgan Chase: 3% (most)
US Bank: 3 percent
USAA: 1 percent
Wells Fargo: 3 percent

DEBIT CARDS

Bank of America(a) : $0/0 percent
Bank of America: $5/1 percent
Citibank(b): $0/1 percent
Citibank $1.50/1 percent
JP Morgan Chase: $3/3 percent
US Bank $2/1 percent
USAA: $0/1 percent
Wells Fargo: $5/0 percent

Joke-2

Posted: February 3, 2010 in Uncategorized

A little kid asks his father, “Daddy, is God a man or a woman?”

“Both son. God is both.”

After a while the kid comes again and asks, “Daddy, is God black or white?”

“Both son, both.”

The child returns a few minutes later and says, “Daddy, is Michael Jackson God?”

This is an actual job application a 17 year old boy submitted at a McDonald’s fast-food establishment in Florida… and they hired him because he was so honest and funny!

NAME: Greg Bulmash

SEX: Not yet. Still waiting for the right person.

DESIRED POSITION: Company’s President or Vice President. But seriously, whatever’s available. If I was in a position to be picky, I wouldn’t be applying here in the first place.

DESIRED SALARY: $185,000 a year plus stock options and a Michael Ovitz style severance package. If that’s not possible, make an offer and we can haggle.

EDUCATION: Yes.

LAST POSITION HELD: Target for middle management hostility.

SALARY: Less than I’m worth.

MOST NOTABLE ACHIEVEMENT: My incredible collection of stolen pens and post-it notes.

REASON FOR LEAVING: It sucked.

HOURS AVAILABLE TO WORK: Any.

PREFERRED HOURS: 1:30-3:30 p.m., Monday, Tuesday, and Thursday.

DO YOU HAVE ANY SPECIAL SKILLS?: Yes, but they’re better suited to a more intimate environment.

MAY WE CONTACT YOUR CURRENT EMPLOYER?: If I had one, would I be here?

DO YOU HAVE ANY PHYSICAL CONDITIONS THAT WOULD PROHIBIT YOU FROM LIFTING UP TO 50 LBS?: Of what?

DO YOU HAVE A CAR?: I think the more appropriate question here would be “Do you have a car that runs?”

HAVE YOU RECEIVED ANY SPECIAL AWARDS OR RECOGNITION?: I may already be a winner of the Publishers Clearing house Sweepstakes.

DO YOU SMOKE?: On the job no, on my breaks yes.

WHAT WOULD YOU LIKE TO BE DOING IN FIVE YEARS?: Living in the Bahamas with a fabulously wealthy dumb sexy blonde super model who thinks I’m the greatest thing since sliced bread. Actually, I’d like to be doing that now.

DO YOU CERTIFY THAT THE ABOVE IS TRUE AND COMPLETE TO THE BEST OF YOUR KNOWLEDGE?: Yes. Absolutely.

SIGN HERE: Aries.

Joke Of The Day-1

Posted: February 3, 2010 in Uncategorized

One man (lets call him Johnny) came to gun shop.
J(ohnny):I want a pistol
S(alesman):Choose from this wall (points at wall full of pistols)
J: (points at biggest pistol) I want this,
S: An .44 Magnum? And for what purpose?
J: For shooting cans.
S: (points on smaller handgun) For shooting cans is the best this one.
J: (points again on .44) No, I want this one.
S: And what cans will you shoot at?
J: Um…Mexi-cans, Portori-cans, Afri-cans…

A woman gets on a bus with her baby. The bus driver says: “That’s the ugliest baby that I’ve ever seen. Ugh!” The woman goes to the rear of the bus and sits down, fuming. She says to a man next to her: “The driver just insulted me!

The man says: “You go right up there and tell him off – go ahead, I’ll hold your monkey for you.”

A doctor says to his patient, “I have bad news and worse news”.

“Oh dear, what’s the bad news?” asks the patient.

The doctor replies, “You only have 24 hours to live.”

“That’s terrible”, said the patient. “How can the news possibly be worse?”

The doctor replies, “I’ve been trying to contact you since yesterday.”

There was an inebriated driver who was pulled up by the police. When the cop opened the door, the driver fell out.
“YOU’RE DRUNK!” exclaimed the police officer.
“Thank God for that!” said the drunk, “I thought the steering had gone.”

A priest is walking down the street one day when he notices a very small boy trying to press a doorbell on a house across the street.
However, the boy is very small and the doorbell is too high for him to reach.
After watching the boy’s efforts for some time, the priest moves closer to the boy’s position.
He steps smartly across the street, walks up behind the little fellow and, placing his hand kindly on the child’s shoulder leans over and gives the doorbell a sold ring.
Crouching down to the child’s level, the priest smiles benevolently and asks, “And now what, my little man?”
To which the boy replies, “Now we run!”

Q: Why do birds fly south in the winter?
A: Because it’s too far to walk!