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

Avoid ugly border around images & links using css

Every web developer would come across this issue. In Firefox, when you'd clicked a image or link there you'll see a dotted border around the image or link. It makes ugly to the website. I googled around this issue & found a way to resolve it. If you see the image below you could easily identify the bug.




To resolve this issue just a single line css is enough.



<style type="text/css">
a:active, a:focus{outline: 0;}
</style>


Send Free SMS using PHP

Hi Friends,
Yesterday i read one article for sending free sms using php(http://www.aswinanand.com/2008/07/send-free-sms-web-service/). Using SOAP method they are sending sms using WAY2SMS.COM

You just need to have the following requirements
1) Need to register your mobile number to WAY2SMS.COM
2) Other person's(whom you want to send sms) mobile number
3) A message which you need to send

You need to pass your values to this link:
http://www.aswinanand.com/sendsms.php?uid=XXXXXXXXX&pwd=password&phone=XXXXXXXXX;XXXXXXXXX&msg=mycodings

In UID = "Your way2sms userid(only mobile number)"
In PWD = "Your way2sms password"
In PHONE = "Friends phone number(Note for the last number there should not be any semicolon';' for more numbers you need to seperate using comma ',')"
In Msg = "Your Msg"

Thats it. Now you can also send sms from your website. If you're not novice in PHP make a Form to submit the values and send it

For downloading sms package: Click Here

For Deployment in your own server : Click Here

Remove malware,iframe.inf virus from your website

Hi Developers,
For the past few months you would be plucking your hair for removing some of the malwares from your site. I too suffered a lot with these and now i learnt the lesson from them how to safeguard our website from these hackers.


The iframe virus are redirecting to chinese domain which was severely affected by malwares which will lead to theft of secured datas from your system. If you dint care for these virus then the virus will eat your whole site.

How to Remove Iframe virus?
Iframe tags will be written just below the body tag. Follow the steps to remove virus.
1. Login to your FTP & edit the file which you've got iframe tag.

2. Look for the iframe tag just below the Body or Head tag.

3. Remove the coding & overwrite the file.

4. Now right click the file and click properties/File attributes and make it to "444". So that no hackers have privilege to write the file with iframe code.

5. Once you've cleaned this, the other type of virus will slowly raise, that is it will search the files that are included on the index.php file (ie dbconnect.php, general.php, configure.php, common.php, functions.php, classes.php etc) and it will write a php coding at the top of the page where it will dynamically write the javascript code at the time of execution of the file in the web - browser. The script will redirect the page to gumblar.cn/rss?id=2

6. To remove these type of error carefully look into the above mentioned filename, you can easily find out the php coding at the top of the page. Just remove the coding and make sure it is write protected, so that the php coding wont be written.

7. Still you cant find the solution, just comment to this section. I'll reply ASAP.


Update:
Godaddy hosting had some security issues on the wordpress which they have installed. Please upgrade all your wordpress under the Godaddy hosting (Wordpress older version is having a security hole by which the malicious code will be injected in all the php files. For further information regarding the virus removal, please send a mail to smart2raise@gmail.com

Easy Multi-Language for your website

Hi Friends & Techies,
I came after a long back time with more tips & i'll share you more frequently in coming months. Now in the post i'll start sharing about how to create Multi-language website using php.

First we must plan for the multilingual website
1) Every time when we add new language it should support the website without anymore alteration in the Web pages
2) Must have unique variable for each & every page for the content [so there wont be redeclaring].
3) For more user friendly use more text and design your pages with css. Avoid using images [Until you need it].
4) After designing your template, put all the contents in main language. Make it aligned and look out for browser compatibility
5) Going for Multi-language option
6) Create a file called "defines.php"
7) In the defines.php you define language for file
if($_COOKIE['language']==""){
$lang = $_SERVER['HTTP_ACCEPT_LANGUAGE'];//Detecting Default Browser language
}
else {
$lang = $_COOKIE['language'];//Detecting if cookie was set
}
switch($lang){
case "en":
require("lang/english.php"); // where the content in english is stored
break;
case "cn":
require("lang/chinese.php"); // where the content in chinese is stored
break;
case "zh-CN":
require("lang/chinese.php");
break;
case "zh-cn":
require("lang/chinese.php");
break;
default:
require("lang/english.php");
break;
}
8) Include the above file in all the webpages in your website

include('includes/defines.php');

9) In all the webpages you must change/add the meta tag information
<1meta http-equiv="content-type" content="text/html; charset=UTF-8"> //Remove 1 in the meta tag

10) for creating a new language you must save the file in UTF-8 format.
Follow the steps to save it in UTF-8
a. Open the notepad (start->run->type "notepad")
b. Save the file as "chinese.php".
c. In the save option Enter file name as "chinese.php" , Save as Type as "All Files", In encoding as "UTF-8" ;
11) Now with as usual PHP Syntax create some unique variable.
12) For chinese translation Go to Google Translation.
13) Copy the translated text and paste it in notepad for a particular variable.
14) Create a webpage, include the defines.php file, print the variable defined in chinese.php file

For Demo: Multilingual Website

Thats it, you've created multilingual website. If you've any doubt, just comment to this post i'll reply as soon as possible.