Tuesday, September 1, 2009

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

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

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)

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;

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.

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;

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.

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


Naviya Nair said...

Great Article..
PHP Training in Chennai
Online PHP Training
Online PHP Training India
PHP Training Chennai
PHP Training institute in Chennai

Bhavya Kumar said...

My friend Suggest me this blog and I can say this is the best blog to get the basic knowledge.Thank you so much for this Selenium Training in Chennai

Anonymous said...

Amazing Blog! This is the most useful post, those who are looking for study related information. Vinay Hari Education Consultant in Jalandhar

CIIT Noida said...

CIITN is the best SAP training and placement Institute in noida. Contestant will analyze in keeping with modern-day enterprise trends by way of certified and industry experts & SAP training Institute in Noida. They provide a 100% placement help with the best training of IT cousrses.

If you are looking best sap training in Noida then you can join CIITN. One of the premier training and development institute for SAP Training Program in Noida. The company provides 6 weeks/ 6 Months Training for SAP. They provide training in all SAP Modules like SAP HANA,SAP B1, SAP FICO etc. Just come for free demo class and then join SAP and move up in your career .

SAP Training Institute in Noida
sap training in noida
sap institute in noida
sap course in noida
best sap training institute in noida

preeti verma said...


colleges in greater noida


colleges in greater noida


colleges in greater noida

cyber news said...

Thanks for such important information.keep up the good work.Ethical Hacking training is based on current industry standards that helps attendees to secure placements in their dream jobs at MNCs. Indian Cyber Army Provides Best Ethical Hacking Training in India.Indian Cyber Army credibility in Ethical hacking training & Cybercrime investigation training is acknowledged across nation as we offer hands on practical knowledge and full assistance with basic as well as advanced level ethical hacking & cybercrime investigation courses