Friday 25 May 2012

Adding Days or Hours or Minutes or Seconds into date in MySQL (AddDate)

// The given query add a interval of 31 days to the '2012-04-15'.
ADDDATE('2012-04-15 16:30:30', INTERVAL 2 DAY); // Output: 2012-04-17 16:30:30
ADDDATE('2012-04-15 16:30:30', INTERVAL 3 HOUR); // Output: 2012-04-17 19:30:30
ADDDATE('2012-04-15 16:30:30', INTERVAL 45 MINUTE); // Output: 2012-04-17 17:15:30
ADDDATE('2012-04-15 16:30:30', INTERVAL 15 SECOND) // Output: 2012-04-17 16:30:45

//Sample MySQL Query
SELECT ADDDATE(date_user_created, INTERVAL 3 HOUR) as length_of_quote FROM users where user_id='23'



My Amazon Store

Wednesday 23 May 2012

Find the timestamp difference/two date difference in terms of day, hour, minute, second format using MySQL query

--
Find the timestamp difference/two date difference in terms of day, hour, minute, second format in MySQL query.
// MySQL convert timediff output to day, hour, minute, second format
SELECT 
CONCAT(TIMESTAMPDIFF(day,u.date_created, NOW()), 'Days ', 
 MOD( TIMESTAMPDIFF(hour,u.date_created, NOW()), 24), 'Hrs ',
 MOD( TIMESTAMPDIFF(minute,u.date_created, NOW()), 60), 'Mins ',
 MOD( TIMESTAMPDIFF(second,u.date_created, NOW()), 60), 'Secs ') as days_ago

/* OUTPUT */
1 day 23 hr 10 min 12 sec 

--

Wednesday 16 May 2012

Creating Cron-Jobs in CodeIgniter (CI) using CLI - CodeIgniter on the Command Line

To make a Cron Job work in CodeIgniter, you basically need to follow three steps;
  • Create a Model class
  • Create a Cron_Jobs Class
  • Set the Cron-Jobs command in the webserver.
Please, use the below example for the reference.
Step - 1: Create a Model Class.
class Warning_model extends CI_Model {
class Warning_model extends CI_Model {
	public function __construct()
	{
		parent::__construct();
	}
	
	public function addReferenceVATRecord()
	{
		//print_r($_SERVER);die;
		$data = array(	'valid_from' => date('Y-m-d H:i:s'),
						'type_id' => '1',
						'vat_rate' => '22.5',		
						'vat_calc' => '0.27',
						'updated' => date('Y-m-d H:i:s'),
						'updated_by' => '1'
					);
					
		$this->db->insert('reference_vat', $data);
		
		return $this->db->insert_id();
	}
}
/* End of file warning_model.php. */
/* Location: ./application/model/warning_model.php. */
Step - 2: Create a Cron_Jobs Controller Class.
if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Cron_Jobs extends CI_Controller {
	
	public function __construct()
	{
		parent::__construct();
		
		$this->load->model('Warning_model', 'm_warning');

		// Call the cron job in below format: 
		// # /usr/local/bin/php /home/admin/admincentre.trunk.rc/index.php cron_jobs cron_add_vat_record 
	}

	public function cron_update_taxes($action='Cron Jobs')
	{
		echo "Hello {$action}".PHP_EOL;
	}
	
	public function cron_add_vat_record()
	{
		if ( $this->input->is_cli_request() )
		{
			// echo 'Request From CLI';
			$id = $this->m_warning->addReferenceVATRecord();
			echo $id.' '.PHP_EOL; die;
		}
		else { 
			echo 'Sorry Guys, we are bit smart this time.'; die;
		}
		
		//echo $id.' '.PHP_EOL; die;
	}
}
/* End of file cron_jobs.php. */
/* Location: ./application/controller/cron_jobs.php. */
Step - 3: Set the Cron-Jobs command in the webserver.
// Use the Croj Job command in below format: 
# /usr/local/bin/php /home/admin/admincentre.trunk.rc/index.php cron_jobs cron_add_vat_record 

Friday 11 May 2012

Solution to the problem defining the multiple databases on CodeIgnieter

// Solution to the problem defining the multiple databases on config/database.php on CodeIgnieter
/** Defining Database 1 */
$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'dbusername_1';
$db['default']['password'] = 'dbpassword_1';
$db['default']['database'] = 'database_1';
$db['default']['dbdriver'] = 'mysql';
/* ----------
------- */
$db['default']['stricton'] = FALSE;

/** Defining Database 2 */
$db['manage']['hostname'] = 'localhost';
$db['manage']['username'] = 'dbusername_2';
$db['manage']['password'] = 'dbpassword_2';
$db['manage']['database'] = "database_2";
$db['manage']['dbdriver'] = "mysql";
/* ----------
------- */
$db['default']['stricton'] = FALSE;

/** Defining Database 3 */
$db['admin']['hostname'] = "localhost";
$db['admin']['username'] = "dbusername_3";
$db['admin']['password'] = "dbpassword_3";
$db['admin']['database'] = "database_3";
$db['admin']['dbdriver'] = "mysql";
/* ----------
------- */
$db['default']['stricton'] = FALSE;


PS: Please note that, all the databases username/password needs to be unique, if
they have shared the same username/password, then, there may be problem.
Actually, when I tried to use the same username/password for different databases,
I have found that the CodeIgniter is overriding my databases and I have solved it
by assigning unique username/password to each database.

Well, I am not sure, whether its CodeIgniter drawback or not. And, I thought it
would be helpful to share with you as well.

Cheers!