Thursday, October 21, 2010

SQL SYNTAX
Select Statement
SELECT "column_name" FROM "table_name"
Distinct
SELECT DISTINCT "column_name"
FROM "table_name"
Where
SELECT "column_name"
FROM "table_name"
WHERE "condition"
And/Or
SELECT "column_name"
FROM "table_name"
WHERE "simple condition"
{[AND/OR] "simple condition"}+
In
SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN('value 1','value 2',...)
Between
SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'value1' AND 'value2'
Like
SELECT "column_name"
FROM "table_name"
WHERE "column_name" LIKE {PATTERN}
Order By
SELECT "column_name"
FROM "table_name"
[WHERE "condition"]
ORDER BY "column_name" [ASC,DESC]
Count
SELECT COUNT("column_name")
FROM "table_name"
Group By
SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
Having
SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
HAVING (arithmetic function condition)
Create Table Statement
CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
...)
Drop Table Statement
DROP TABLE "table_name"
Truncate Table Statement
TRUNCATE TABLE "table_name"
Insert Into Statement
INSERT INTO "table_name" ("column1","column2",...)
VALUES ("value1","value2",...)
Update Statement
UPDATE "table_name"
SET "column_1"=[new value]
WHERE {condition}
Delete From Statement
DELETE FROM "table-name"
WHERE {condition}

Monday, October 18, 2010

SQL Operators

1) SQL Logical Operators

There are three Logical Operators namely, AND, OR, and NOT. These operators compare two conditions at a time to determine whether a row can be selected for the output. When retrieving data using a SELECT statement, you can use logical operators in the WHERE clause, which allows you to combine more than one condition.

Logical Operators	Description
OR	For the row to be selected at least one of the conditions must be true.
AND	For a row to be selected all the specified conditions must be true.
NOT	For a row to be selected the specified condition must be false.

2) Comparison Operators:

Comparison operators are used to compare the column data with specific values in a condition.
Comparison Operators are also used along with the SELECT statement to filter data based on specific conditions.
The below table describes each comparison operator.

Comparison Operators	Description
=	equal to
<>, !=	is not equal to
<	less than
>	greater than
>=	greater than or equal to
<=	less than or equal to

3) Comparison Keywords

There are other comparison keywords available in sql which are used to enhance the search capabilities of a sql query. They are "IN", "BETWEEN...AND", "IS NULL", "LIKE".

Comparision Operators	Description
LIKE	column value is similar to specified character(s).
IN	column value is equal to any one of a specified set of values.
BETWEEN...AND	column value is between two values, including the end values specified in the range.
IS NULL	column value does not exist.

SQL LIKE Operator

The LIKE operator is used to list all rows in a table whose column values match a specified pattern. It is useful when you want to search rows to match a specific pattern, or when you do not know the entire value. For this purpose we use a wildcard character '%'.

For example: To select all the students whose name begins with 'S'

 SELECT first_name, last_name 

FROM student_details 

WHERE first_name LIKE 'S%';

The output would be similar to:

first_name	last_name
-------------	-------------
Stephen	Fleming
Shekar	Gowda

The above select statement searches for all the rows where the first letter of the column first_name is 'S' and rest of the letters in the name can be any character.

There is another wildcard character you can use with LIKE operator. It is the underscore character, ' _ ' . In a search string, the underscore signifies a single character.

For example: to display all the names with 'a' second character,

 SELECT first_name, last_name 

FROM student_details 

WHERE first_name LIKE '_a%';

The output would be similar to:

first_name	last_name
-------------	-------------
Rahul	Sharma

NOTE:Each underscore act as a placeholder for only one character. So you can use more than one underscore. Eg: ' __i% '-this has two underscores towards the left, 'S__j%' - this has two underscores between character 'S' and 'i'.

SQL BETWEEN ... AND Operator

The operator BETWEEN and AND, are used to compare data for a range of values.

For Example: to find the names of the students between age 10 to 15 years, the query would be like,

 SELECT first_name, last_name, age 

FROM student_details 

WHERE age BETWEEN 10 AND 15;

The output would be similar to:

first_name	last_name	age
-------------	-------------	------
Rahul	Sharma	10
Anajali	Bhagwat	12
Shekar	Gowda	15

SQL IN Operator:

The IN operator is used when you want to compare a column with more than one value. It is similar to an OR condition.

For example: If you want to find the names of students who are studying either Maths or Science, the query would be like,

 SELECT first_name, last_name, subject 

FROM student_details 

WHERE subject IN ('Maths', 'Science');

The output would be similar to:

first_name	last_name	subject
-------------	-------------	----------
Anajali	Bhagwat	Maths
Shekar	Gowda	Maths
Rahul	Sharma	Science
Stephen	Fleming	Science

You can include more subjects in the list like ('maths','science','history')

NOTE:The data used to compare is case sensitive.

SQL IS NULL Operator

A column value is NULL if it does not exist. The IS NULL operator is used to display all the rows for columns that do not have a value.

For Example: If you want to find the names of students who do not participate in any games, the query would be as given below

 SELECT first_name, last_name 

FROM student_details 

WHERE games IS NULL

There would be no output as we have every student participate in a game in the table student_details, else the names of the students who do not participate in any games would be displayed.

Introduction of SQL

SQL stands for “Structured Query Language”

It is a query language used for accessing and modifying information in the database. IBM first developed SQL in 1970s. Also it is an ANSI/ISO standard. It has become a Standard Universal Language used by most of the relational database management systems (RDBMS).

Some of the RDBMS systems are: Oracle, Microsoft SQL server, Sybase etc.

Most of these have provided their own implementation thus enhancing it's feature and making it a powerful tool. Few of the sql commands used in sql programming are

SELECT Statement,

UPDATE Statement,

INSERT INTO Statement,

DELETE Statement,

WHERE Clause,

ORDER BY Clause,

GROUP BY Clause,

ORDER Clause, Joins, Views, GROUP Functions, Indexes etc.

In a simple manner, SQL is a non-procedural, English-like language that processes data in groups of records rather than one record at a time.

Few functions of SQL are:

store data
modify data
retrieve data
modify data
delete data
create tables and other database objects
delete data

Wednesday, October 13, 2010

Differences between SQL and MySQL

SQL stands for Structured Query Language.
It's a standard language for accessing and manipulating databases.
MySQL is a database management system, like SQL Server 2005, Oracle, Informix, Postgres etc.
MySQL is a RDMS (Relational Database Management System).
All types of RDMB use SQL.
SQL is used to manipulate database or to create a database. It's actually a common language.
MySQL is an actual computer application. You must need to download or collect it and install it.
MySQL is one of the most popular open source database management system.
MySQL has an SQL interpreter.
MySQL can be used as the back engine database for several kinds of applications and it's one of the best choice for many web programmers for web-base application.

---------------------------------------------------------------------------------------------------------------------------
SQL is a language used with databases, mySQL is a database application that uses SQL.

SQL is a common database computer language designed for the retrieval and management of data in relational database management systems (RDBMS) -- basically a standard interactive and programming language for querying and modifying data and managing databases. Very standard for uses ranging from the simplest Microsoft Access applications, up to complex multi-server distributed Oracle applications.

MySQL is a multithreaded, multi-user SQL database management system (DBMS) providing multi-user access to a number of databases. MySQL is commonly the back engine database for a great many applications, and often the database of choice for web-based applications.

Comparing the two is a little like comparing the English language to Tom Clancey's last book, one uses the other -- but from there the differences are many.

Friday, October 08, 2010

Difference between Trigger and a Stored Procedure

when you create a trigger you have to identify event and action of your trigger but when you create stored procedure you don't identify event and action.
trigger is run automatically if the event is occured but stored procedure don't run automatically but you have to run it manually
within a trigger you can call specific stored procedure but within a stored procedure you cann;t call a trigger

-----------------------------------

Actually triger in action which is performed automatically before or after a event occur and stored procedure is a procedure which is executed when the it is called. Stored procedure is module.

-----------------------------

Triggers are implicitly called by DB itself while Stored procedure has to be manually called by user.
Stored procedure can pass the parameters which is not a case with Triggers.
While creating a Trigger triggering event n action has to be specified which isn’t a case with Stored procedure.
A Trigger can call the specific Stored procedure in it but the reverse is not true.

Tuesday, September 21, 2010

Using stored procedures

Why Need:

Now that you know what stored procedures are, why use them? There are many reasons, but here are the primary ones:

To simplify complex operations (as seen in the previous example) by encapsulating processes into a single easy-to-use unit.
To ensure data integrity by not requiring that a series of steps be created over and over. If all developers and applications use the same (tried and tested) stored procedure, the same code will be used by all.
An extension of this is to prevent errors. The more steps that need to be performed, the more likely it is that errors will be introduced. Preventing errors ensures data consistency.
To simplify change management. If tables, column names, or business logic (or just about anything) changes, only the stored procedure code needs to be updated, and no one else will need even to be aware that changes were made.
An extension of this is security. Restricting access to underlying data via stored procedures reduces the chance of data corruption (unintentional or otherwise).
To improve performance, as stored procedures typically execute quicker than do individual SQL statements.
There are MySQL language elements and features that are available only within single requests. Stored procedures can use these to write code that is more powerful and flexible. (We'll see an example of this in the next tutorial).

In other words, there are three primary benefitssimplicity, security, and performance. Obviously all are extremely important. Before you run off to turn all your SQL code into stored procedures, here's the downside:

Stored procedures tend to be more complex to write than basic SQL statements, and writing them requires a greater degree of skill and experience.
You might not have the security access needed to create stored procedures. Many database administrators restrict stored procedure creation rights, allowing users to execute them but not necessarily create them.

Nonetheless, stored procedures are very useful and should be used whenever possible.

Note
Can't Write Them? You Can Still Use Them MySQL distinguishes the security and access needed to write stored procedures from the security and access needed to execute them. This is a good thing; even if you can't (or don't want to) write your own stored procedures, you can still execute them when appropriate.

About SP--------------------------------------------------------------------------------------------------
Using stored procedures requires knowing how to execute (run) them. Stored procedures are executed far more often than they are written, so we'll start there. And then we'll look at creating and working with stored procedures.

Executing Stored Procedures

MySQL refers to stored procedure execution as calling, and so the MySQL statement to execute a stored procedure is simply CALL. CALL takes the name of the stored procedure and any parameters that need to be passed to it. Take a look at this example:

• Input

CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage);

• Analysis

Here a stored procedure named productpricing is executed; it calculates and returns the lowest, highest, and average product prices.

Stored procedures might or might not display results, as you will see shortly.

Creating Stored Procedures

As already explained, writing a stored procedure is not trivial. To give you a taste for what is involved, let's look at a simple examplea stored procedure that returns the average product price. Here is the code:

• Input

CREATE PROCEDURE productpricing()
BEGIN
   SELECT Avg(prod_price) AS priceaverage
   FROM products;
END;

• Analysis

Ignore the first and last lines for a moment; we'll come back to them shortly. The stored procedure is named productpricing and is thus defined with the statement CREATE PROCEDURE productpricing(). Had the stored procedure accepted parameters, these would have been enumerated between the ( and ). This stored procedure has no parameters, but the trailing () is still required. BEGIN and END statements are used to delimit the stored procedure body, and the body itself is just a simple SELECT statement (using the Avg() function learned in Tutorial 12, "Summarizing Data").

When MySQL processes this code it creates a new stored procedure named productpricing. No data is returned because the code does not call the stored procedure, it simply creates it for future use.

Note
mysql Command-line Client Delimiters If you are using the mysql command-line utility, pay careful attention to this note.
The default MySQL statement delimiter is ; (as you have seen in all of the MySQL statement used thus far). However, the mysql command-line utility also uses ; as a delimiter. If the command-line utility were to interpret the ; characters inside of the stored procedure itself, those would not end up becoming part of the stored procedure, and that would make the SQL in the stored procedure syntactically invalid.
The solution is to temporarily change the command-line utility delimiter, as seen here:

DELIMITER //

CREATE PROCEDURE productpricing()
BEGIN
   SELECT Avg(prod_price) AS priceaverage
   FROM products;
END //

DELIMITER ;

Here, DELIMITER // tells the command-line utility to use // as the new end of statement delimiter, and you will notice that the END that closes the stored procedure is defined as END // instead of the expected END;. This way the ; within the stored procedure body remains intact and is correctly passed to the database engine. And then, to restore things back to how they were initially, the statement closes with a DELIMITER ;.
Any character may be used as the delimiter except for \.
If you are using the mysql command-line utility, keep this in mind as you work through this tutorial.

So how would you use this stored procedure? Like this:

• Input

CALL productpricing();

• Output

+--------------+
| priceaverage |
+--------------+
|    16.133571 |
+--------------+

• Analysis

CALL productpricing(); executes the just-created stored procedure and displays the returned result. As a stored procedure is actually a type of function, () characters are required after the stored procedure name (even when no parameters are being passed).

Dropping Stored Procedures

After they are created, stored procedures remain on the server, ready for use, until dropped. The drop command (similar to the statement seen Tutorial 21, "Creating and Manipulating Tables") removes the stored procedure from the server.

To remove the stored procedure we just created, use the following statement:

• Input

DROP PROCEDURE productpricing;

• Analysis

This removes the just-created stored procedure. Notice that the trailing () is not used; here just the stored procedure name is specified.

Tip
Drop Only If It Exists DROP PROCEDURE will throw an error if the named procedure does not actually exist. To delete a procedure if it exists (and not throw an error if it does not), use DROP PROCEDURE IF EXISTS.

Working with Parameters

productpricing is a really simple stored procedureit simply displays the results of a SELECT statement. Typically stored procedures do not display results; rather, they return them into variables that you specify.

New Term
Variable A named location in memory, used for temporary storage of data.

Here is an updated version of productpricing (you'll not be able to create the stored procedure again if you did not previously drop it):

• Input

CREATE PROCEDURE productpricing(
   OUT pl DECIMAL(8,2),
   OUT ph DECIMAL(8,2),
   OUT pa DECIMAL(8,2)
)
BEGIN
   SELECT Min(prod_price)
   INTO pl
   FROM products;
   SELECT Max(prod_price)
   INTO ph
   FROM products;
   SELECT Avg(prod_price)
   INTO pa
   FROM products;
END;

• Analysis

This stored procedure accepts three parameters: pl to store the lowest product price, ph to store the highest product price, and pa to store the average product price (and thus the variable names). Each parameter must have its type specified; here a decimal value is used. The keyword OUT is used to specify that this parameter is used to send a value out of the stored procedure (back to the caller). MySQL supports parameters of types IN (those passed to stored procedures), OUT (those passed from stored procedures, as we've used here), and INOUT (those used to pass parameters to and from stored procedures). The stored procedure code itself is enclosed within BEGIN and END statements as seen before, and a series of SELECT statements are performed to retrieve the values that are then saved into the appropriate variables (by specifying the INTO keyword).

Note
Parameter Datatypes The datatypes allowed in stored procedure parameters are the same as those used in tables. Appendix D, "MySQL Datatypes," lists these types.
Note that a recordset is not an allowed type, and so multiple rows and columns could not be returned via a parameter. This is why three parameters (and three SELECT statements) are used in the previous example.

To call this updated stored procedure, three variable names must be specified, as seen here:

• Input

CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage);

• Analysis

As the stored procedure expects three parameters, exactly three parameters must be passed, no more and no less. Therefore, three parameters are passed to this CALL statement. These are the names of the three variables that the stored procedure will store the results in.

Note
Variable Names All MySQL variable names must begin with @.

When called, this statement does not actually display any data. Rather, it returns variables that can then be displayed (or used in other processing).

To display the retrieved average product price you could do the following:

• Input

SELECT @priceaverage;

• Output

+---------------+
| @priceaverage |
+---------------+
| 16.133571428  |
+---------------+

To obtain all three values, you can use the following:

• Input

SELECT @pricehigh, @pricelow, @priceaverage;

• Output

+------------+-----------+---------------+
| @pricehigh | @pricelow | @priceaverage |
+------------+-----------+---------------+
| 55.00      | 2.50      | 16.133571428  |
+------------+-----------+---------------+

Here is another example, this time using both IN and OUT parameters. ordertotal accepts an order number and returns the total for that order:

• Input

CREATE PROCEDURE ordertotal(
   IN onumber INT,
   OUT ototal DECIMAL(8,2)
)
BEGIN
   SELECT Sum(item_price*quantity)
   FROM orderitems
   WHERE order_num = onumber
   INTO ototal;
END;

• Analysis

onumber is defined as IN because the order number is passed in to the stored procedure. ototal is defined as OUT because the total is to be returned from the stored procedure. The SELECT statement used both of these parameters, the WHERE clause uses onumber to select the right rows, and INTO uses ototal to store the calculated total.

To invoke this new stored procedure you can use the following:

• Input

CALL ordertotal(20005, @total);

• Analysis

Two parameters must be passed to ordertotal; the first is the order number and the second is the name of the variable that will contain the calculated total.

To display the total you can then do the following:

• Input

SELECT @total;

• Output

+--------+
| @total |
+--------+
| 149.87 |
+--------+

• Analysis

@total has already been populated by the CALL statement to ordertotal, and SELECT displays the value it contains.

To obtain a display for the total of another order, you would need to call the stored procedure again, and then redisplay the variable:

• Input

CALL ordertotal(20009, @total);
SELECT @total;

Building Intelligent Stored Procedures

All of the stored procedures used thus far have basically encapsulated simple MySQL SELECT statements. And while they are all valid examples of stored procedures, they really don't do anything more than what you could do with those statements directly (if anything, they just make things a little more complex). The real power of stored procedures is realized when business rules and intelligent processing are included within them.

Consider this scenario. You need to obtain order totals as before, but also need to add sales tax to the total, but only for some customers (perhaps the ones in your own state). Now you need to do several things:

Obtain the total (as before).
Conditionally add tax to the total.
Return the total (with or without tax).

That's a perfect job for a stored procedure:

• Input

-- Name: ordertotal
-- Parameters: onumber = order number
--             taxable = 0 if not taxable, 1 if taxable
--             ototal = order total variable

CREATE PROCEDURE ordertotal(
   IN onumber INT,
   IN taxable BOOLEAN,
   OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN

   -- Declare variable for total
   DECLARE total DECIMAL(8,2);
   -- Declare tax percentage
   DECLARE taxrate INT DEFAULT 6;

   -- Get the order total
   SELECT Sum(item_price*quantity)
   FROM orderitems
   WHERE order_num = onumber
   INTO total;

   -- Is this taxable?
   IF taxable THEN
      -- Yes, so add taxrate to the total
      SELECT total+(total/100*taxrate) INTO total;
   END IF;

   -- And finally, save to out variable
   SELECT total INTO ototal;

END;

• Analysis

The stored procedure has changed dramatically. First of all, comments have been added throughout (preceded by --). This is extremely important as stored procedures increase in complexity. An additional parameter has been addedtaxable is a BOOLEAN (specify true if taxable, false if not). Within the stored procedure body, two local variables are defined using DECLARE statements.

DECLARE requires that a variable name and datatype be specified, and also supports optional default values (taxrate in this example is set to 6%). The SELECT has changed so the result is stored in total (the local variable) instead of ototal. Then an IF statement checks to see if taxable is true, and if it is, another SELECT statement is used to add the tax to local variable total. And finally, total (which might or might not have had tax added) is saved to ototal using another SELECT statement.

Tip
The COMMENT Keyword The stored procedure for this example included a COMMENT value in the CREATE PROCEDURE statement. This is not required, but if specified, is displayed in SHOW PROCEDURE STATUS results.

This is obviously a more sophisticated and powerful stored procedure. To try it out, use the following two statements:

• Input

CALL ordertotal(20005, 0, @total);
SELECT @total;

• Output

+--------+
| @total |
+--------+
| 149.87 |
+--------+

• Input

CALL ordertotal(20005, 1, @total);
SELECT @total;

• Output

+---------------+
| @total        |
+---------------+
| 158.862200000 |
+---------------+

• Analysis

BOOLEAN values may be specified as 1 for true and 0 for false (actually, any non-zero value is considered true and only 0 is considered false). By specifying 0 or 1 in the middle parameter you can conditionally add tax to the order total.

Note
The IF Statement This example showed the basic use of the MySQL IF statement. IF also supports ELSEIF and ELSE clauses (the former also uses a THEN clause, the latter does not). We'll be seeing additional uses of IF (as well as other flow control statements) in future tutorials.

Inspecting Stored Procedures

To display the CREATE statement used to create a stored procedure, use the SHOW CREATE PROCEDURE statement:

• Input

SHOW CREATE PROCEDURE ordertotal;

To obtain a list of stored procedures including details on when and who created them, use SHOW PROCEDURE STATUS.

Note

Limiting Procedure Status Results SHOW PROCEDURE STATUS lists all stored procedures. To restrict the output you can use LIKE to specify a filter pattern, for example:

SHOW PROCEDURE STATUS LIKE 'ordertotal';

Monday, September 20, 2010

MySQL date calculation

e.g. 2 days ago till today:

select domain, count(*) as cnt from referrers where date >= current_date()-2 group by domain order by cnt;

Simple Date Calculations
Date calculations are relatively easy. The first function we're going to look at is the YEAR() function, which returns a year from a given date. For example:

mysql> SELECT YEAR('2003-03-31');
+--------------------+
| YEAR('2003-03-31') |
+--------------------+
| 2003 |
+--------------------+

We can perform simple arithmetic on a date using the '+' and '-' operators. For example, to find out which year is five years ahead of a given date, you can use:
mysql> SELECT YEAR('2003-03-31')+5;
+----------------------+
| YEAR('2003-03-31')+5 |
+----------------------+
| 2008 |
+----------------------+

And to find out which year was five years in the past:
mysql> SELECT YEAR('2003-03-31')-5;
+----------------------+
| YEAR('2003-03-31')-5 |
+----------------------+
| 1998 |
+----------------------+

Of course you don't have to hard-code the date. MySQL is quite capable of telling the date and time, using the NOW() function:
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2003-03-31 00:32:21 |
+---------------------+

or just the date with the CURRENT_DATE() function:
mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2003-03-31 |
+----------------+

There are also functions for the other date and time intervals; MONTH(), DAYOFMONTH(), HOUR(), MINUTE() and SECOND(). For example:
mysql> SELECT MONTH(NOW()) AS m,
DAYOFMONTH(NOW()) AS d,
HOUR(NOW()) AS h,
MINUTE(NOW()) AS m,
SECOND(NOW()) AS s;
+------+------+------+------+------+
| m | d | h | m | s |
+------+------+------+------+------+
| 3 | 31 | 1 | 53 | 38 |
+------+------+------+------+------+

The DAYOFMONTH() function is an exception to the naming conventions because there are a number of other ways to return the day. DAYOFMONTH() returns the day as a numeric from 1 to 31, but there is also DAYNAME() which returns the actual name of the day, DAYOFWEEK() which returns a number from 1 (Sunday) to 7(Saturday) and DAYOFYEAR() returning a number from 1 to 366. Some examples:

mysql> SELECT DAYNAME('2000-01-01');
+-----------------------+
| DAYNAME('2000-01-01') |
+-----------------------+
| Saturday |
+-----------------------+

mysql> SELECT DAYOFWEEK('2000-01-01');
+-------------------------+
| DAYOFWEEK('2000-01-01') |
+-------------------------+
| 7 |
+-------------------------+

mysql> SELECT DAYOFYEAR('2000-12-31');
+-------------------------+
| DAYOFYEAR('2000-12-31') |
+-------------------------+
| 366 |
+-------------------------+

One of the most commonly performed date calculations is one which calculates age. Unfortunately there isn't a function to do it, so you need to do a bit of thinking. Let's begin with doing some simple arithmetic - you can see when I'm running these queries from the earlier results (31 March 2003). First, we'll simply subtract the year of birth from the current year. If you're reading this article well after I wrote it, of course your results may be different:
mysql> SELECT YEAR(CURRENT_DATE)-YEAR('1971-01-01');
+---------------------------------------+
| YEAR(CURRENT_DATE)-YEAR('1971-01-01') |
+---------------------------------------+
| 32 |
+---------------------------------------+

If you were using this to return the age, the result would be correct. Someone born on the 1st of January 1971 would be 32 years old on the 31st of March 2003. But let's test for someone born on the 31st of December:
mysql> SELECT YEAR(CURRENT_DATE)-YEAR('1971-12-31');
+---------------------------------------+
| YEAR(CURRENT_DATE)-YEAR('1971-12-31') |
+---------------------------------------+
| 32 |
+---------------------------------------+

Here the age is wrong. This person would not have turned 32 yet. The problem is that the calculation above only checks the year part of the date. It comes down to subtracting 1971 from 2003, regardless of the month and day. The best way to do this is to compare whether the current month and day are larger than the birth month and day. If it is, a full year has passed, and the year portion of the calculation can be left. If it isn't, a full year hasn't passed, and you need to subtract one from the year portion. It sounds tricky, but it isn't really. Let's break it down slowly. It helps us greatly that MySQL evaluates a true expression to 1, and a false expression to 0. For example:
mysql> SELECT 23>19;
+-------+
| 23>19 |
+-------+
| 1 |
+-------+

mysql> SELECT 23<19;> SELECT RIGHT('abcdef',2);
+-------------------+
| RIGHT('abcdef',2) |
+-------------------+
| ef |
+-------------------+

This returns 2 characters, starting from the right of the string, thus 'ef'. If you ask for more characters than the string is long, MySQL simply returns the whole string:
mysql> SELECT RIGHT('abcdef',9);
+-------------------+
| RIGHT('abcdef',9) |
+-------------------+
| abcdef |
+-------------------+

So, to return the 'MM-DD' portion of a date, you need to return the five rightmost characters, for example:
mysql> SELECT RIGHT(CURRENT_DATE(),5);
+-------------------------+
| RIGHT(CURRENT_DATE(),5) |
+-------------------------+
| 03-31 |
+-------------------------+

Now we have everything we need to calculate an age. The query will have a portion that calculates the difference in years, and then we'll subtract either 0 or 1, depending on the month-day portion. Here is the full query:
mysql> SELECT YEAR(CURRENT_DATE()) - YEAR('1971-12-31')
- (RIGHT(CURRENT_DATE(),5)<'12-31') AS age;
+------+
| age |
+------+
| 31 |
+------+

Monday, September 06, 2010

library Management

Admin Module (admin/ librarian)

Login
Add Employee
Add Students
Add Books
Manage Employee
Manage Students
Manage Books
Entry Daily Issues

Client Module

Index Page
Student Book Issue information page.

Folder Structure

Library Management

Index file
student's book issue viewfile
Admin Folder
                    index file
                    add employee files
                    add student information
                    manage employee and students files.
image folder
css folder(contain css file)
js folder(contain javascript file)

Sunday, September 05, 2010

.Htaccess rewrites, Mod_Rewrite Tricks and Tips

Visit : http://www.askapache.com/htaccess/mod_rewrite-tips-and-tricks.html
Or
Read Below........

Mod_Rewrite Tips and Tricks are lot of .htaccess rewrite examples that show specific uses for creating .htaccess rewrites to do all kinds of cool and profitable stuff for your site. Htaccess Rewrites are enabled by using the Apache module mod_rewrite, which is one of the most powerful Apache modules and features availale. Htaccess Rewrites through mod_rewrite provide the special ability to Rewrite requests internally as well as Redirect request externally.

When the url in your browser’s location bar stays the same for a request it is an internal rewrite, when the url changes an external redirection is taking place. This is one of the first, and one of the biggest mental-blocks people have when learning about mod_rewrite… But I have a secret weapon for you to use, a new discovery from years of research that makes learning mod_rewrite drastically quicker and easier. It truly does or I wouldn’t be saying so in the introduction of this article.

Despite the tons of examples and docs, mod_rewrite is voodoo.
Damned cool voodoo, but still voodoo.
– Brian Moore

Note: After years of fighting to learn my way through rewriting urls with mod_rewrite, I finally had a breakthrough and found a way to outsmart the difficulty of mod_rewrite that I just couldn’t seem to master. The Mod_Rewrite RewriteCond/RewriteRule Variable Value Cheatsheet is the one-of-a-kind tool that changed the game for me and made mod_rewriting no-harder than anything else.

So keep that mod_rewrite reference bookmarked and you will be able to figure out any RewriteRule or RewriteCond, an amazing feat considering it took me a LONG time to figure this stuff out on my own. But that was before the craziness, one of the most challenging and productive .htaccess experiments I’ve done… An experiment so ILL it’s sick like a diamond disease on your wrist! $$$. That mod_rewrite experiment/tutorial was the culmination of many different advanced mod_rewrite experiments I had done in the past and included most of my very best .htaccess tricks. With the cheatsheet it’s no longer Voodoo.. Its just what you do. Now lets dig in!

Some handy links to help you navigate this page:

Htaccess rewrites TOC

If you really want to take a look, check out the mod_rewrite.c and mod_rewrite.h files.

Be aware that mod_rewrite (RewriteRule, RewriteBase, and RewriteCond) code is executed for each and every HTTP request that accesses a file in or below the directory where the code resides, so it’s always good to limit the code to certain circumstances if readily identifiable.

For example, to limit the next 5 RewriteRules to only be applied to .html and .php files, you can use the following code, which tests if the url does not end in .html or .php and if it doesn’t, it will skip the next 5 RewriteRules.

RewriteRule !\.(html|php)$ - [S=5]
RewriteRule ^.*-(vf12|vf13|vf5|vf35|vf1|vf10|vf33|vf8).+$ - [S=1]

.htaccess rewrite examples should begin with:

Options +FollowSymLinks
 
RewriteEngine On
RewriteBase /

Require the www

Options +FollowSymLinks
RewriteEngine On
RewriteBase /
RewriteCond %{HTTP_HOST} !^www\.askapache\.com$ [NC]
RewriteRule ^(.*)$ http://www.askapache.com/$1 [R=301,L]

Loop Stopping Code

Sometimes your rewrites cause infinite loops, stop it with one of these rewrite code snippets.

RewriteCond %{REQUEST_URI} ^/(stats/|missing\.html|failed_auth\.html|error/).* [NC]
RewriteRule .* - [L]
 
RewriteCond %{ENV:REDIRECT_STATUS} 200
RewriteRule .* - [L]

Cache-Friendly File Names

This is probably my favorite, and I use it on every site I work on. It allows me to update my javascript and css files in my visitors cache’s simply by naming them differently in the html, on the server they stay the same name. This rewrites all files for /zap/j/anything-anynumber.js to /zap/j/anything.js and /zap/c/anything-anynumber.css to /zap/c/anything.css

RewriteRule ^zap/(j|c)/([a-z]+)-([0-9]+)\.(js|css)$ /zap/$1/$2.$4 [L]

SEO friendly link for non-flash browsers

When you use flash on your site and you properly supply a link to download flash that shows up for non-flash aware browsers, it is nice to use a shortcut to keep your code clean and your external links to a minimum. This code allows me to link to site.com/getflash/ for non-flash aware browsers.

RewriteRule ^getflash/?$ http://www.adobe.com/shockwave/download/download.cgi?P1_Prod_Version=ShockwaveFlash [NC,L,R=307]

Removing the Query_String

On many sites, the page will be displayed for both page.html and page.html?anything=anything, which hurts your SEO with duplicate content. An easy way to fix this issue is to redirect external requests containing a query string to the same uri without the query_string.

RewriteCond %{THE_REQUEST} ^GET\ /.*\;.*\ HTTP/
RewriteCond %{QUERY_STRING} !^$
RewriteRule .* http://www.askapache.com%{REQUEST_URI}? [R=301,L]

Sending requests to a php script

This .htaccess rewrite example invisibly rewrites requests for all Adobe pdf files to be handled by /cgi-bin/pdf-script.php

RewriteRule ^(.+)\.pdf$  /cgi-bin/pdf-script.php?file=$1.pdf [L,NC,QSA]

Setting the language variable based on Client

For sites using multiviews or with multiple language capabilities, it is nice to be able to send the correct language automatically based on the clients preferred language.

RewriteCond %{HTTP:Accept-Language} ^.*(de|es|fr|it|ja|ru|en).*$ [NC]
RewriteRule ^(.*)$ - [env=prefer-language:%1]

Deny Access To Everyone Except PHP fopen

This allows access to all files by php fopen, but denies anyone else.

RewriteEngine On
RewriteBase /
RewriteCond %{THE_REQUEST} ^.+$ [NC]
RewriteRule .* - [F,L]

If you are looking for ways to block or deny specific requests/visitors, then you should definately read Blacklist with mod_rewrite. I give it a 10/10

Deny access to anything in a subfolder except php fopen

This can be very handy if you want to serve media files or special downloads but only through a php proxy script.

RewriteEngine On
RewriteBase /
RewriteCond %{THE_REQUEST} ^[A-Z]{3,9}\ /([^/]+)/.*\ HTTP [NC]
RewriteRule .* - [F,L]

Require no www

Options +FollowSymLinks
RewriteEngine On
RewriteBase /
RewriteCond %{HTTP_HOST} !^askapache\.com$ [NC]
RewriteRule ^(.*)$ http://askapache.com/$1 [R=301,L]

Check for a key in QUERY_STRING

Uses a RewriteCond Directive to check QUERY_STRING for passkey, if it doesn’t find it it redirects all requests for anything in the /logged-in/ directory to the /login.php script.

RewriteEngine On
RewriteBase /
RewriteCond %{QUERY_STRING} !passkey
RewriteRule ^/logged-in/(.*)$ /login.php [L]

Removes the QUERY_STRING from the URL

If the QUERY_STRING has any value at all besides blank than the?at the end of /login.php? tells mod_rewrite to remove the QUERY_STRING from login.php and redirect.

RewriteEngine On
RewriteBase /
RewriteCond %{QUERY_STRING} .
RewriteRule ^login.php /login.php? [L]

Fix for infinite loops

An error message related to this is

Request exceeded the limit  of 10 internal redirects due to probable configuration error. Use  'LimitInternalRecursion' to increase the limit if necessary. Use  'LogLevel debug' to get a backtrace.

or you may seeRequest exceeded the limit,probable configuration error,Use 'LogLevel debug' to get a backtrace, orUse 'LimitInternalRecursion' to increase the limit if necessary

RewriteCond %{ENV:REDIRECT_STATUS} 200
RewriteRule .* - [L]

External Redirect .php files to .html files (SEO friendly)

RewriteRule ^(.*)\.php$ /$1.html [R=301,L]

Internal Redirect .php files to .html files (SEO friendly)

Redirects all files that end in .html to be served from filename.php so it looks like all your pages are .html but really they are .php

RewriteRule ^(.*)\.html$ $1.php [R=301,L]

block access to files during certain hours of the day

Options +FollowSymLinks
RewriteEngine On
RewriteBase /
# If the hour is 16 (4 PM) Then deny all access
RewriteCond %{TIME_HOUR} ^16$
RewriteRule ^.*$ - [F,L]

Rewrite underscores to hyphens for SEO URL

Converts all underscores “_” in urls to hyphens “-” for SEO benefits… See the full article for more info.

Options +FollowSymLinks
RewriteEngine On
RewriteBase /
 
RewriteRule !\.(html|php)$ - [S=4]
RewriteRule ^([^_]*)_([^_]*)_([^_]*)_([^_]*)_(.*)$ $1-$2-$3-$4-$5 [E=uscor:Yes]
RewriteRule ^([^_]*)_([^_]*)_([^_]*)_(.*)$ $1-$2-$3-$4 [E=uscor:Yes]
RewriteRule ^([^_]*)_([^_]*)_(.*)$ $1-$2-$3 [E=uscor:Yes]
RewriteRule ^([^_]*)_(.*)$ $1-$2 [E=uscor:Yes]
 
RewriteCond %{ENV:uscor} ^Yes$
RewriteRule (.*) http://d.com/$1 [R=301,L]

Require the www without hardcoding

Options +FollowSymLinks
RewriteEngine On
RewriteBase /
RewriteCond %{HTTP_HOST} !^www\.[a-z-]+\.[a-z]{2,6} [NC]
RewriteCond %{HTTP_HOST} ([a-z-]+\.[a-z]{2,6})$     [NC]
RewriteRule ^/(.*)$ http://%1/$1 [R=301,L]

Require no subdomain

RewriteEngine On
RewriteBase /
RewriteCond %{HTTP_HOST} \.([a-z-]+\.[a-z]{2,6})$ [NC]
RewriteRule ^/(.*)$ http://%1/$1 [R=301,L]

Require no subdomain

RewriteEngine On
RewriteBase /
RewriteCond %{HTTP_HOST} \.([^\.]+\.[^\.0-9]+)$
RewriteRule ^(.*)$ http://%1/$1 [R=301,L]

Redirecting WordPress Feeds to Feedburner

Full article:Redirecting WordPress Feeds to Feedburner

RewriteEngine On
RewriteBase /
RewriteCond %{REQUEST_URI} ^/feed\.gif$
RewriteRule .* - [L]
 
RewriteCond %{HTTP_USER_AGENT} !^.*(FeedBurner|FeedValidator) [NC]
RewriteRule ^feed/?.*$ http://feeds.feedburner.com/apache/htaccess [L,R=302]
 
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]

Only allow GET and PUT Request Methods

Article: Request Methods

RewriteEngine On
RewriteBase /
RewriteCond %{REQUEST_METHOD} !^(GET|PUT)
RewriteRule .* - [F]

Prevent Files image/file hotlinking and bandwidth stealing

RewriteEngine On
RewriteBase /
RewriteCond %{HTTP_REFERER} !^$
RewriteCond %{HTTP_REFERER} !^http://(www\.)?askapache.com/.*$ [NC]
RewriteRule \.(gif|jpg|swf|flv|png)$ /feed/ [R=302,L]

Stop browser prefetching

RewriteEngine On
SetEnvIfNoCase X-Forwarded-For .+ proxy=yes
SetEnvIfNoCase X-moz prefetch no_access=yes
 
# block pre-fetch requests with X-moz headers
RewriteCond %{ENV:no_access} yes
RewriteRule .* - [F,L]

This module uses a rule-based rewriting engine (based on a regular-expression parser) to rewrite requested URLs on the fly. It supports an unlimited number of rules and an unlimited number of attached rule conditions for each rule, to provide a really flexible and powerful URL manipulation mechanism. The URL manipulations can depend on various tests, of server variables, environment variables, HTTP headers, or time stamps. Even external database lookups in various formats can be used to achieve highly granular URL matching.
This module operates on the full URLs (including the path-info part) both in per-server context (httpd.conf) and per-directory context (.htaccess) and can generate query-string parts on result. The rewritten result can lead to internal sub-processing, external request redirection or even to an internal proxy throughput.
Further details, discussion, and examples, are provided in the detailed mod_rewrite documentation.

Directives

If you aren’t already comfortable using mod_rewrite then I recommend this excellent mod_rewrite guide by one of my favorite mod_rewrite gurus that I’ve met.

htaccess Guide Sections

« Search Engine Friendly Redirects | .htaccess Tutorial Index | » Speed up your site with Caching and cache-control

« Blocking Bad Bots and Scrapers with .htaccess
Undetectable Sniffing On Ethernet »

Thursday, September 02, 2010

mod rewrite

what is mod_rewrite for?

Simply, mod_rewrite is used for rewriting a URL at the server level, giving the user output for that final page. So, for example, a user may ask for http://www.somesite.com/widgets/blue/, but will really be given http://www.somesite.com/widgets.php?colour=blue by the server. Of course, the user will be none the wiser to this little bit of chicanery.

What do I need to get mod_rewrite working?

There’s pretty much only one thing you’ll need to get mod_rewrite working for you, and that’s to have the mod_rewrite module installed on your Apache server!

For the purpose of this article, I’m going to assume that you don’t have access to view or edit the Apache server httpd.conf file, so the easiest way to check whether the mod_rewrite module is installed will be to look on your phpinfo page. If you’ve not already created one of these for yourself, just copy and paste the following code into an new text file using your favourite text editor, save it as phpinfo.php, and upload it to your server:

Load that page up in your web browser, and perform a search for “mod_rewrite”. All being well, you’ll find it in the “Apache loaded modules” section of the page. If it isn’t there, you’ll have to contact your hosting company and politely ask them to add it to the Apache configuration.

Assuming the mod_rewrite module is loaded, then you’re good to go!

A simple mod_rewrite example

So, let’s write a simple mod_rewrite example. This isn’t going to be anything fancy; we’re just going to redirect people who ask for alice.html to the page bob.html instead. First, let’s create the Alice and Bob pages. Below is Alice’s webpage - create a similar one for Bob.

(title)Alice's webpage(/title)
   
(html)
    (head)
       (title)Second mod_rewrite example(/title)
    (/head)
    (body)
               The requested page was:
         
          (/body)
(/html)

Upload both of these to your web server, and check that you can view both of them. Now comes the fun - we’re going to add a couple of lines to your .htaccess file. The .htaccess file is a text file which contains Apache directives. Any directives which you place in it will apply to the directory which the .htaccess file sits in, and any below it. To ours, we’re going to add the following:

RewriteEngine on
RewriteRule ^alice.html$ bob.html

Upload this .htaccess file to the same directory as alice.html and bob.html, and reload Alice’s page. You should see Bob’s page being displayed, but Alice’s URL. If you still see Alice’s page being displayed, then check you’ve followed the instructions correctly (you may have to clear your cache). If things still aren’t working for you, then contact your technical support people and ask them to enable mod_rewrite and the FileInfo override in their httpd.conf file for you

The structure of a RewriteRule

RewriteRule Pattern Substitution [OptionalFlags]

The general structure of a RewriteRule is fairly simple if you already understand regular expressions. This article isn’t intended to be a tutorial about regular expressions though - there are already plenty of those available. RewriteRules are broken up as follows:

RewriteRule

This is just the name of the command.

Pattern

A regular expression which will be applied to the “current” URL. If any RewriteRules have already been performed on the requested URL, then that changed URL will be the current URL.

Substitution

Substitution occurs in the same way as it does in Perl, PHP, etc. You can include backreferences and server variable names (%{VARNAME}) in the substitution. Backreferences to this RewriteRule should be written as $N, whereas backreferences to the previous RewriteCond should be written as %N. A special substitution is -. This substitution tells Apache to not perform any substitution. I personally find that this is useful when using the F or G flags (see below), but there are other uses as well.

OptionalFlags

This is the only part of the RewriteRule which isn’t mandatory. Any flags which you use should be surrounded in square brackets, and comma separated. The flags which I find to be most useful are:

F - Forbidden. The user will receive a 403 error.
L - Last Rule. No more rules will be proccessed if this one was successful.
R[=code] - Redirect. The user’s web browser will be visibly redirected to the substituted URL. If you use this flag, you must prefix the substitution with http://www.somesite.com/, thus making it into a true URL. If no code is given, then a HTTP reponse of 302 (temporarily moved) is sent.

A full list of flags is given in the Apache mod_rewrite manual.

A slightly more complicated mod_rewrite example

Let’s try a slightly more meaty example now. Suppose you have a web page which takes a parameter. This parameter tells the page how to be displayed, and what content to pull into it. Humans don’t tend to like remembering the additional syntax of query strings for URLs, and neither do search engines. Both sets of people seem to much prefer a straight URL, with no extra bits tacked onto the end.

In our example, you’ve created a main index page with takes a page parameter. So, a link like index.php?page=software would take you to a software page, while a link to index.php?page=interests would take you to an interests page. What we’ll do with mod_rewrite is to silently redirect users from page/software/ to index.php?page=software etc.

The following is what needs to go into your .htaccess file to accomplish that:

RewriteEngine on
RewriteRule ^page/([^/\.]+)/?$ index.php?page=$1 [L]

Let’s walk through that RewriteRule, and work out exactly what’s going on:

^page/: Sees whether the requested page starts with page/. If it doesn’t, this rule will be ignored.
([^/.]+): Here, the enclosing brackets signify that anything that is matched will be remembered by the RewriteRule. Inside the brackets, it says “I’d like one or more characters that aren’t a forward slash or a period, please”. Whatever is found here will be captured and remembered.
/?$: Makes sure that the only thing that is found after what was just matched is a possible forward slash, and nothing else. If anything else is found, then this RewriteRule will be ignored.
index.php?page=$1: The actual page which will be loaded by Apache. $1 is magically replaced with the text which was captured previously.
[L]: Tells Apache to not process any more RewriteRules if this one was successful.

Let’s write a quick page to test that this is working. The following test script will simply echo the name of the page you asked for to the screen, so that you can check that the RewriteRule is working.

(title)Second mod_rewrite example(/title)
   
   
      
         The requested page was:

Again, upload both the index.php page, and the .htaccess file to the same directory. Then, test it! If you put the page in http://www.somesite.com/mime_test/, then try requesting http://www.somesite.com/mime_test/page/software. The URL in your browser window will show the name of the page which you requested, but the content of the page will be created by the index.php script! This technique can obviously be extended to pass multiple query strings to a page - all you’re limited by is your imagination.

Conditional Statements and mod_rewrite

But what happens when you start getting people hotlinking to your images (or other files)? Hot linking is the act of including an image, media file, etc from someone else’s server in one of your own pages as if it were your own. Obviously, as a webmaster, there are plenty of times when you don’t want people doing that. You’ll almost certainly have seen examples where someone has linked to one image on a website, only for a completely different, “nasty” one to be shown instead. So, how is this done?

It’s pretty simple really. All it takes are a couple of RewriteCond statements in your .htaccess file.

RewriteCond statements are as they sound - conditional statements for RewriteRules. The basic format for a RewriteCond is RewriteCond test_string cond_pattern. For our purpose, we will set the test_string to be the HTTP_REFERER. If the test string is neither empty nor our own server, then we will serve an alternative (low bandwidth) image, which tells the person who is hotlinking off for stealing our bandwidth.

Here’s how we do that:

RewriteEngine on
RewriteCond %{HTTP_REFERER} !^$
RewriteCond %{HTTP_REFERER} !^http://(www\.)?somesite.com/.*$ [NC]
RewriteRule \.(gif|jpg|png)$ http://www.somesite.com/nasty.gif [R,L]

Here, the RewriteRule will only be performed if all the preceeding RewriteConds are fulfilled. In the second RewriteCond, [NC] simply means “No Case”, so it doesn’t matter whether the domain name was written in upper case, lower case or a mixture of the two. So, any requests for gif, jpg or png files from referers other than somesite.com will result in your “nasty” image being shown instead.

The [R,L] in the RewriteRule simply means “Redirect, Last”. So, the RewriteRule will visibly redirect output to “nasty.gif” and no more RewriteRules will be performed on this URL.

If you simply don’t want the hot linkers to see any image at all when they hot link to your images, then simply change the final line to RewriteRule \.(gif|jpg|png)$ - [F]. The - means “don’t rewrite the requested URL”, and the [F] means “Forbidden”. So, the hot linker will get a “403 Forbidden message”, and you don’t end up wasting your bandwidth.

Conclusion

mod_rewrite is an incredibly handy tool to have in your arsenal. This article only scratched the surface of what is possible with mod_rewrite, but should have given you enough information to go out and start mod_rewriting history yourself!

Pages

Thursday, October 21, 2010

Monday, October 18, 2010

1) SQL Logical Operators

SQL LIKE Operator

SQL BETWEEN ... AND Operator

SQL IN Operator:

SQL IS NULL Operator

Wednesday, October 13, 2010

Friday, October 08, 2010

Tuesday, September 21, 2010

Executing Stored Procedures

Creating Stored Procedures

Dropping Stored Procedures

Working with Parameters

Building Intelligent Stored Procedures

Inspecting Stored Procedures

Monday, September 20, 2010

Monday, September 06, 2010

Sunday, September 05, 2010

Some handy links to help you navigate this page:

Htaccess rewrites TOC

Loop Stopping Code

Cache-Friendly File Names

SEO friendly link for non-flash browsers

Removing the Query_String

Sending requests to a php script

Setting the language variable based on Client

Deny Access To Everyone Except PHP fopen

Deny access to anything in a subfolder except php fopen

Directives

htaccess Guide Sections

Thursday, September 02, 2010

what is mod_rewrite for?

What do I need to get mod_rewrite working?

A simple mod_rewrite example

The structure of a RewriteRule

A slightly more complicated mod_rewrite example

Conditional Statements and mod_rewrite

Conclusion