Tuesday, November 1, 2011

Using Regular Expressions in Mysql

Using Regular Expressions in queries

A very interesting and useful capability of MySQL is to incorporate Regular Expressions (regex) in SQL queries. The regular expression support in MySQL is extensive. Let's take a look at using Regular Expressions in queries and the supported meta characters.

A simple example of using Regular Expressions in a SQL query would be to select all names from a table that start with 'A'.

SELECT name FROM employees WHERE name REGEXP '^A'

A slight modification in the above example to look for names starting with 'A' or 'D' or 'F' will look like this.

SELECT name FROM employees WHERE name REGEXP '^(A|D|F)'

If we want to select all names ending with 'P', then the SQL query goes like this

SELECT name FROM employees WHERE name REGEXP 'P$'

Checking only for numbers

age FROM employees WHERE age REGEXP '^[0-9]+$'

Contains a specific word, for example the skill PHP in skill sets

name FROM employees WHERE skill_sets REGEXP '[[:<:]]php[[:>:]]'

Fetching records where employees have entered their 10-digit mobile number as the contact number.

name FROM employees WHERE contact_no REGEXP '^[0-9]{10}$'

Wednesday, August 10, 2011

Hire Me....

Tuesday, March 1, 2011

CSS Specific for Internet Explorer

#1 IE Conditional Comments

IE conditional comment is probably the most commonly used to fix the IE bugs for specific versions (IE6, IE7, IE8). Below are some sample code to target different versions of Internet Explorer:

&lt!--[if IE 8]> = IE8
<!--[if lt IE 8]> = IE7 or below
<!--[if gte IE 8]> = greater than or equal to IE8

EXAMPLE: <!--[if IE 8]>
<style type="text/css">
/* css for IE 8 */

<!--[if lt IE 8]>
<link href="ie7.css" rel="stylesheet" type="text/css" />

#2 CSS Rules Specific to Explorer (IE CSS hacks)

Another option is to declare CSS rules that can only be read by Explorer. For example, add an asterisk (*) before the CSS property will target IE7 or add an underscore before the property will target IE6. However, this method is not recommended because they are not valid CSS syntax.

IE8 or below: to write CSS rules specificially to IE8 or below, add a backslash and 9 (\9) at the end before the semicolon.
IE7 or below: add an asterisk (*) before the CSS property.
IE6: add an underscore (_) before the property.

background: gray; /* standard */
background: pink\9; /* IE 8 and below */
*background: green; /* IE 7 and below */
_background: blue; /* IE 6 */

#3 Conditional HTML Class

The third option, which was founded by Paul Irish, is to add an CSS class with the IE version to the HTML tag by using IE conditional comments. Basicially, it checks if it is IE, then add a class to the html tag. So to target specific IE version, simply use the IE class as the parent selector (eg. .ie6 .box). This is a clever way and it doesn't cause any validation errors.

<!--[if lt IE 7 ]> <html class="ie6"> <![endif]-->
<!--[if IE 7 ]> <html class="ie7"> <![endif]-->
<!--[if IE 8 ]> <html class="ie8"> <![endif]-->
<!--[if IE 9 ]> <html class="ie9"> <![endif]-->
<!--[if (gt IE 9)|!(IE)]><!--> <html> <!--<![endif]-->

Monday, February 7, 2011

Different Key In RDBMS

* Alternate key - An alternate key is any candidate key which is not selected to be the primary key

* Candidate key - A candidate key is a field or combination of fields that can act as a primary key field for that table to uniquely identify each record in that table.

* Compound key - compound key (also called a composite key or concatenated key) is a key that consists of 2 or more attributes.

* Primary key - a primary key is a value that can be used to identify a unique row in a table. Attributes are associated with it. Examples of primary keys are Social Security numbers (associated to a specific person) or ISBNs (associated to a specific book).
In the relational model of data, a primary key is a candidate key chosen as the main method of uniquely identifying a tuple in a relation.

* Superkey - A superkey is defined in the relational model as a set of attributes of a relation variable (relvar) for which it holds that in all relations assigned to that variable there are no two distinct tuples (rows) that have the same values for the attributes in this set. Equivalently a superkey can also be defined as a set of attributes of a relvar upon which all attributes of the relvar are functionally dependent.

* Foreign key - a foreign key (FK) is a field or group of fields in a database record that points to a key field or group of fields forming a key of another database record in some (usually different) table. Usually a foreign key in one table refers to the primary key (PK) of another table. This way references can be made to link information together and it is an essential part of database normalization



Let's start with MyISAM since it is the default engine with MySQL. MyISAM is based on the older but proven ISAM code but has been extended to be fully-featured while retaining the reliability. Data in MyISAM tables is split between three different files on the disk. One for the table format, another for the data, and lastly a third for the indexes.

The maximum number of rows supported amounts to somewhere around ~4.295E+09 and can have up to 64 indexed fields per table. Both of these limits can be greatly increased by compiling a special version of MySQL.

Text/Blob fields are able to be fully-indexed which is of great importance to search functions.

Much more technical information can be found on MySQL's MyISAM Manual Page.


InnoDB is relatively newer so the scene than MyISAM is so people are still weary about its use in environments than run fine under MyISAM. InnoDB is transaction-safe meaning data-integrity is maintained throughout the entire query process. InnoDB also provides row-locking, as opposed to table-locking, meaning while one query is busy updating or inserting a row, another query can update a different row at the same time. These features increase multi-user concurrency and performance.

Another great feature InnoDB boasts is the ability to use foreign-key constraints. FK constraints allows developers to ensure that inserted data referencing another table remains valid. For example, if you had an authors table and a books table and you wanted to insert a new book while referencing the author. The author would have to exist in the authors table before inserting them in the books table because a foreign key was specified in the books table. At the same time you would not be able to delete an author from the authors table if they had any entries associated with them in the books table. More on this in a later article...

Because of its row-locking feature InnoDB is said to thrive in high load environments. Its CPU efficiency is probably not matched by any other disk-based relational database engine.


MyISAM in most cases will be faster than InnoDB for run of the mill sort of work. Selecting, updating and inserting are all very speedy under normal circumstances. It is the default engine chosen by the MySQL development team which speaks to its integrity, reliability, and performance.

InnoDB, or the OSX of the database-engine world, has emerged with some nifty features and created a niche for itself very quickly. Boasting features like row-level locking, transaction-safe queries, and relational table design are all very temping. The first two features really shine in a table that is constantly getting hammered like a logs, or search engine-type table. Since queries happen in the blink of an eye (faster actually) table-level locking(MyISAM) is sufficient in most other normal cases.

InnoDB recovers from a crash or other unexpected shutdown by replaying its logs. MyISAM must fully scan and repair or rebuild any indexes or possibly tables which had been updated but not fully flushed to disk.

Decision Matrix

Is your table is going to be inserted, deleted, and updated much much more than it is going to be selected?
If you need full-text search MyISAM
If you prefer/require relational database design InnoDB
Is disk-space or ram an issue? MyISAM
In Doubt? MyISAM

There is no winner.

REMEMBER! It's OK to mix table types in the same database! In fact it's recommended and frequently required. However, it is important to note that if you are having performance issues when joining the two types, try converting one to the other and see if that fixes it. This issue does not happen often but it has been reported.

New Features of PHP 5.3.0

PHP development team has just announced the availability of PHP version 5.3.0.
This release comes with major improvements as compared with the previous versions existing in PHP 5.x series.
PHP version 5.3.0 has many new features, as well as bug fixes (in this release over 140 bugs were repaired).
In the migration guide intended for the users of PHP 5.2.0 who want to upgrade to PHP 5.3.0,
there are explained the main differences between versions 5.2.0 and 5.3.0 of PHP.

Among the wide range of new features added in PHP 5.3.0, support for namespaces, Late Static Bindings, jump labels and native Closures (Lambda/Anonymous functions) was introduced. Two new magic methods are also supported in PHP 5.3.0: __callStatic and __invoke.

A garbage collection for cyclic references enabled by default and mysqlnd PHP native replacement for libmysql are optional. In the same time, now you are allowed to use dynamic access to static methods.

The code execution performance is improved. On the other hand, Windows support includes VC9 and experimental X64 binaries, as well as portability features to other compatible platforms.

Other key features of PHP 5.3.0 are:

-More consistent float rounding
-Deprecation notices are now handled via E_DEPRECATED (part of E_ALL) instead of the E_STRICT error level
-Several enhancements to enable more flexiblity in php.ini (and ini parsing in general)
-New bundled extensions: ext/phar, ext/intl, ext/fileinfo, ext/sqlite3, ext/enchant

The complete source code and Windows binaries of PHP 5.3.0 are available for free download,

Interface in PHP

Interface in PHP

PHP Interface:

PHP does not support multiple inheritance directly, to implement this we need Interface. It is much similar to Interface of Java.

In PHP, signature of the method are declared in the Interface body, and the body part of the method is implemented in derived class. Variables are declared as constant and it can not be changed in the child classes.

We use implement keyword to extend this kind of class, at the same time we can implement more than one interface and one interface can be implemented by another interface.

All methods declared in an interface must be public and the variables should be constant.

This is mandatory that we must declare the body part of the method in the derived class otherwise an error message will be generated.


interface Inter{

const a="This is constant value";

public function disp(); }

class A implements Inter{

function show(){

echo self::a."

public function disp(){

echo "Inside the disp function";}}

$a=new A();





This is constant value
Inside the disp function