Mysql Query Tips & Tricks

For Mysql Novice, this topic would be useful for you to build a website with minimizing number of queries.

1) Mysql for getting Date,Day, Monthname & year:

select MONTHNAME(datefield),DAYNAME(datefield), YEAR(datefield), DAY(datefield), MONTH(datefield) from tablename

USAGE:
This query will be useful for displaying date, day, year, monthname etc. By using this query we can avoid PHP arrays & PHP split functions

Explanation:
MONTHNAME: Shows full name of the month(i.e January, February)
DAYNAME: Shows full name of the Day(i.e Monday, Tuesday)
Year: Shows year(i.e 2009,2010)
Day: shows date(ie 1,2,3)
Month: Shows month(ie 12,11,10)

Tips:
If you need just 2 or 3 characters from the monthname or dayname you can use substring function
i.e: select substr(MONTHNAME(datefield),1,3) as mon from tablename

Note: datefield should have a datatype as Date only

2) Concatination Using mysql Query:
select CONCAT('http://mycodings.blogspot.com/','2009/05/remove-malwareiframeinf-virus-from-your.html') as link from tablename;

USAGE:
This query will be useful for concatinating two fields dynamically using mysql. In any case if you need to join two fields or need to add extra characters to your value then concatination function is best to save the execution time.

Explanation:
CONCAT() is mysql in-built function
It is used concat more than 2 strings or fields
The above query will result in http://mycodings.blogspot.com/2009/05/remove-malwareiframeinf-virus-from-your.html

3) For mysqldump using commandline argument
mysqldump --user=yourusername --password=yourpassword yourdatabase > filenametobesaved;

Usage:
It can be used in mysql command prompt or you can use it as a commandline argument in php
i.e: exec(mysqldump --user=yourusername --password=yourpassword yourdatabase > filenametobesaved);

Output will be saved in the path where you've run this commandline.

The argument will export the whole database to your system.

Explanation:
mysqldump - In-built function
yourusername - Username for the database
yourpassword - Password for the database
yourdatabase - Databasename which is to be exported
filenametobesaved - Filename which the exported to be saved