Using bit.ly API with PHP

bit.ly – A service to shorten long URL’s. Generally this type of service is used for twitter. Here is a PHP function to shorten the URL’s with bit.ly API

function shorten($url) {
$version = "2.0.1";
$login = "YOUR LOGIN NAME";
$appkey = "YOUR APP KEY";
//create the URL
$api_url = 'http://api.bit.ly/shorten?version='.$version.'&longUrl='.urlencode($url).'&format=xml&login='.$login.'&apiKey='.$appkey;
//call the API
$curl = curl_init();
curl_setopt($curl, CURLOPT_URL, $api_url);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
$response = curl_exec($curl);
curl_close($curl);
//parse the XML response and return the url
$xml_object = new SimpleXMLElement($response);

return $xml_object->results->nodeKeyVal->shortUrl;
}
?>

NOTE: Above function will not works if CURL is not installed on your server.

How to list tables, columns in MYSQL ?

To list all the tables in the current database

SHOW TABLES

To list columns for a given table (users is the table name)

SHOW COLUMNS FROM users

(OR)

DESC users

To search for particular columns in a table

SHOW COLUMNS FROM users WHERE FIELD LIKE '%name%'

Selecting Random rows in MYSQL result set

To select random rows from a mysql result set use rand() function with order by clause.  Below is an example

$query = "SELECT * FROM posts ORDER BY RAND() LIMIT 10";

In the above query limit can be increased or decreased depending on the number of rows in your result set.

Starting MYSQL Server at boot time in Linux

In order to start your MYSQL Server at boot time run the following command in su (or) sudo mode

chkconfig mysqld on

To check your mysql service status

service mysqld status

To start mysql service

service mysqld start

To stop mysql service

service msyqld stop

To restart mysql service

service msyqld restart

array_sum for multi dimensional arrays in PHP

Here is a single line code to sum a particular elements in a multi-dimensional array. I too got from GOOGLE only, and this functions really made my day :)
Usage:

In the above example $vendor_data is multi dimensional array, and i want sum of the element ‘CategoryCount’.
Hope this single line of code helps you..

Importing Gmail Contacts Using CURL and PHP

Here is the code to import Gmail address book using curl and php. This is very helpful if we want to import the contacts into our site from Gmail. Give a try and please comment on this script.

<?php
$location = "";
$cookiearr = array();
$csv_source_encoding='utf-8';

#function get_contacts, accepts as arguments $login (the username) and $password
#returns array of: array of the names and array of the emails if login successful
#otherwise returns 1 if login is invalid and 2 if username or password was not specified
function get_contacts($login, $password)
{
#the globals will be updated/used in the read_header function
global $csv_source_encoding;
global $location;
global $cookiearr;
global $ch;

#check if username and password was given:
if ((isset($login) && trim($login)=="") || (isset($password) && trim($password)==""))
{
#return error code if they weren't
return 2;
}

#initialize the curl session
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL,"https://www.google.com/accounts/ServiceLoginAuth?service=mail");
curl_setopt($ch, CURLOPT_REFERER, "");
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);
curl_setopt($ch, CURLOPT_HEADERFUNCTION, 'read_header');

#get the html from gmail.com
$html = curl_exec($ch);

$matches = array();
$actionarr = array();

$action = "https://www.google.com/accounts/ServiceLoginAuth?service=mail";

#parse the login form:
#parse all the hidden elements of the form
preg_match_all('/]*name\="([^"]+)"[^>]*value\="([^"]*)"[^>]*>/si', $html, $matches);
$values = $matches[2];
$params = "";

$i=0;
foreach ($matches[1] as $name)
{
$params .= "$name=" . urlencode($values[$i]) . "&";
++$i;
}

$login = urlencode($login);
$password = urlencode($password);

#submit the login form:
curl_setopt($ch, CURLOPT_URL,$action);
curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $params ."Email=$login&Passwd=$password&PersistentCookie=");

$html = curl_exec($ch);

#test if login was successful:
if (!isset($cookiearr['GX']) && (!isset($cookiearr['LSID']) || $cookiearr['LSID'] == "EXPIRED"))
{
return 1;
}

#this is the new csv url:
curl_setopt($ch, CURLOPT_URL, "http://mail.google.com/mail/contacts/data/export?exportType=ALL&groupToExport=&out=GMAIL_CSV");
curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);
curl_setopt($ch, CURLOPT_HTTPGET, 1);

$html = curl_exec($ch);
$html = iconv ($csv_source_encoding,'utf-8',$html);

$csvrows = explode("\n", $html);
array_shift($csvrows);

$names = array();
$emails = array();
foreach ($csvrows as $row)
{
if (preg_match('/^((?:"[^"]*")|(?:[^,]*)).*?([^,@]+@[^,]+)/', $row, $matches))
{
$names[] = trim( ( trim($matches[1] )=="" ) ? current(explode("@",$matches[2])) : $matches[1] , '" ');
$emails[] = trim( $matches[2] );
}
}

return array($names, $emails);
}

#read_header is essential as it processes all cookies and keeps track of the current location url
#leave unchanged, include it with get_contacts
function read_header($ch, $string)
{
global $location;
global $cookiearr;
global $ch;
global $csv_source_encoding;

$length = strlen($string);

if (preg_match("/Content-Type: text\\/csv; charset=([^\s;$]+)/",$string,$matches))
$csv_source_encoding=$matches[1];

if(!strncmp($string, "Location:", 9))
{
$location = trim(substr($string, 9, -1));
}
if(!strncmp($string, "Set-Cookie:", 11))
{
$cookiestr = trim(substr($string, 11, -1));
$cookie = explode(';', $cookiestr);
$cookie = explode('=', $cookie[0]);
$cookiename = trim(array_shift($cookie));
$cookiearr[$cookiename] = trim(implode('=', $cookie));
}
$cookie = "";
if(trim($string) == "")
{
foreach ($cookiearr as $key=>$value)
{
$cookie .= "$key=$value; ";
}
curl_setopt($ch, CURLOPT_COOKIE, $cookie);
}

return $length;
}

#function to trim the whitespace around names and email addresses
#used by get_contacts when parsing the csv file
function trimvals($val)
{
return trim ($val, "\" \n");
}
function getContacts($username = null, $password = null){

$login = $username;
$password = $password;

$resultarray = get_contacts($login, $password);

foreach($resultarray as $res){
$emailArray['email'] = $res;
}
return $emailArray;

}

print_r(getContacts("GMAIL_USERNAME","GMAIL_PASSWORD"));
?>

How to preserve ordering in MYSQL while using WHERE IN clause ?

To preserve the ordering of result set while using IN clause of mysql use the following query format
Lisiting 1

SELECT * FROM foobar WHERE id IN(345,876,1,334,99543) ORDER BY FIELD(id,345,876,1,334,99543);

With the above query the result set will be in the above mentioned order
If we use the below query

Lisiting 2

SELECT * FROM foobar WHERE id IN(345,876,1,334,99543);

the result set order will ascending w.r.t id.

So to preserve the order of the result set use Lisiting 1 query

How to convert varchar to date data type in MYSQL ?

Recently i encountered with a problem of inserting employee data into the mysql database with their date of joining column given in the excel sheet. I have data some thing like(01.12.2002) in the excel file.. I have to insert the same in the database with date format(Y-m-d). I googled some time and got the solution. Below is the query to convert varchar data type to date.
To view in date format
SELECT date_of_joining,STR_TO_DATE(‘date_of_joining’,'%d.%m.%Y’) FROM employees

To update the information
UPDATE employees set date_of_joining = STR_TO_DATE(‘date_of_joining’,'%d.%m.%Y’)

Here goes the explanation of the above query
STR_TO_DATE(‘date_of_joining’,'%d.%m.%Y’)
STR_TO_DATE – MYSQL in built function to convert string to date format

%d.%m.%Y – here in my case ‘.’ is the separator in the given excel sheet. If you have separator other than ‘.’ replace the separator in the above string.
For example if the separator is ‘/’
then your query should be

UPDATE employees set date_of_joining = STR_TO_DATE(‘date_of_joining’,'%d/%m/%Y’)

P.S: Please backup your data while updating the information

Multiple Databases, Database Switching in CAKEPHP

This is a small tip but more powerfu l and useful tweak for the developers who will often change the database configuration details while moving to developement environment to live env and vice versa.

<?php
class DATABASE_CONFIG {
    var $development = array(
        'driver' => 'mysql',
        'persistent' => false,
        'host' => 'localhost',
        'port' => '',
        'login' => 'your_local_username',
        'password' => 'your_local_password',
        'database' => 'your_local_database',
        'schema' => '',
        'prefix' => '',
        'encoding' => ''
    );
    var $production = array(
        'driver' => 'mysql',
        'persistent' => false,
        'host' => 'your_remote_server',
        'port' => '',
        'login' => 'your_remote_username',
        'password' => 'your_remote_password',
        'database' => 'your_remote_database',
        'schema' => '',
        'prefix' => '',
        'encoding' => ''
    );
    var $default = array();
    function __construct(){
        $this->default = ($_SERVER['SERVER_ADDR'] == '127.0.0.1') ? $this->development : $this->production;
        }
    function DATABASE_CONFIG(){
        $this-> __construct();
    }
}
?>

By the above script you can automatically switch your datbases according to the server.
And one more useful tip
we often used to change the value of debug constant in core.php file Right.... ?
 Below is the small tip for that. By this, debug will be set to zero automatically
in live environment and it will be set to '2' in the development environment.
In app/core.php replace with following code :

FIND: 
Configure::write('debug',2);

REPLACE WITH :

if($_SERVER['SERVER_ADDR'] == '127.0.0.1'){
        Configure::write('debug', 2);
    }else{
        Configure::write('debug', 0);
    }

Turnoff autocomplete or disable cache in HTML pages

In some pages(like creditcard payment page or any other authentication page) we have to avoid the drop down which was appeared below the input text elements. This is active by default in a web browser.

To avoid the autocomplete of input text elements add autocomplete=”off” attribute to your input element.
For example..

<input id=”creditcardnumber” name=”creditcardnumber” type=”text”  autocomplete=”off”/>

Follow

Get every new post delivered to your Inbox.