Playing with date

Since I easily forget about MySQL date function. I wrote this to myself.

I have a field name `created` with DATETIME format like 2010-05-24 11:24:05

Using the MySQL function
SELECT NOW() give you the latest time like 2010-05-24 11:24:05

SELECT CURDATE() give you the current date like 2010-05-24

So I available command is. Lets say the current time is 2010-05-24 11:24:05
SELECT YEAR( NOW()); will return 2010
SELECT MONTH( NOW()); will return 05
SELECT DAY( NOW()); will return 24
SELECT TIME( NOW()); will return 11:24:05
SELECT HOUR( NOW()); will return 11
SELECT MINUTE( NOW()); will return 24
SELECT SECOND( NOW()); will return 05

*Note
SELECT YEAR('2010-05-24 11:24:05'); will also return 2010


To select a record in the same month
SELECT * FROM table WHERE YEAR(`created`) = YEAR(CURDATE()) AND MONTH(`created`) = MONTH(CURDATE())

Another format is like this
SELECT name, created FROM customer
WHERE created BETWEEN '2010-01-04 00:00:00' AND '2010-01-06 00:00:00';

*Take note the field registerdate format is DATETIME()
*Take note that the date range in the between must be from low to high

These is a function to take the 'current amount from stock forecast'. Let say with these situation
15 March 2011, Gold price will be 2.0
20 March 2011, Gold price will be 2.1
30 March 2011, Gold price will be 2.2

Today is 25 March 2011. So the SQL will return "Gold price will be 2.1"
So these is the SQL

"SELECT `goldprice` FROM `vtiger_goldforecast` WHERE `datestart` <=CURDATE() ORDER BY `vtiger_goldforecast`.`datestart` DESC LIMIT 1";

Update 1.2 (25 Nov 2011) - Select a record within a date
Case: The table has start date and end date. You want to retrieve a record where today is still withing the start and end date

SELECT * FROM special eventWHERE NOW( ) BETWEEN startdate AND enddate


Thats for today, version 1.1
Going to update later

Javascript get screen browser size resolution

You might find it everywhere but I just keep this link for my own reference.
Normally it would be like this

screen.height
screen.width

*There is a difference using screen with the link given

But this tutorial is more focus on Window size and scrolling
http://www.howtocreate.co.uk/tutorials/javascript/browserwindow

Creating a popup
http://www.blazonry.com/javascript/windows.php


As for concolusion. I create a popup handle

//Create a popup
function popup( link, senderId)
{
//Get the browser resolution
var width = getBrowserWidth();
var height = getBrowserHeight();

//alert( width + "=" + height + " - " + screen.width + "=" + screen.height);

//Reduce the screen by 20%
width = width * 0.8;
height = height * 0.8;

//Put the popup at the middle of the page
var left = (screen.width/2)-(width/2);
var top = (screen.height/2)-(height/2);

//alert(senderId);

popupWindow = window.open( link, "popupWindow", "width="+width+",height="+height+",top="+top+",left="+left+",scrollbars=yes,toolbar=no,location=no,menubar=no,resizable=no,directories=no");
popupWindow.focus();
}


//Change the innerHTML based on the given Id
function changeInnerHTML( elementName, theValue)
{
var theElement = window.opener.document.getElementById(elementName);
theElement.innerHTML = theValue;
}


//Change the value based on the given Id
function changeValue( elementName, theValue)
{
var theElement = window.opener.document.getElementById(elementName);
theElement.value = theValue;
}


//Get the browser width
function getBrowserWidth()
{
var myWidth = 0;

if( typeof( window.innerWidth ) == 'number' ) {
myWidth = window.innerWidth; //Non-IE
}
else if( document.documentElement && ( document.documentElement.clientWidth || document.documentElement.clientHeight ) ) {
myWidth = document.documentElement.clientWidth; //IE 6+ in 'standards compliant mode'
}
else if( document.body && ( document.body.clientWidth || document.body.clientHeight ) ) {
myWidth = document.body.clientWidth; //IE 4 compatible
}

return myWidth;
}


//Get the browser height
function getBrowserHeight()
{
var myHeight = 0;

if( typeof( window.innerWidth ) == 'number' ) {
myHeight = window.innerHeight; //Non-IE
}
else if( document.documentElement && ( document.documentElement.clientWidth || document.documentElement.clientHeight ) ) {
myHeight = document.documentElement.clientHeight; //IE 6+ in 'standards compliant mode'
}
else if( document.body && ( document.body.clientWidth || document.body.clientHeight ) ) {
myHeight = document.body.clientHeight; //IE 4 compatible
}

return myHeight;
}

Found out problem
1. Not really 'at center' if the browser zoom is not 100% (happen on FF as I found it out)

Want to use this code? its free and 'as is'

CakePHP iframe

I very new with iframe so i just job it down to myself

lets says I have a controller name customers, inside it has 3 function which is index, add and edit.

Inside the index.ctp, i put an iframe into it

like this

<a href="customers/edit/1" target="test">HREF</a>

<iframe name="test" width="500" height="200" frameborder="1" src="customers/add"></iframe>

Tho cool part is, it will load the customer/add into the iframe and when I click the hyper link, the customers/edit/1 will load

Install and bypass window media player 11 validation

If you using a pirate Window XP and inside it is using Window media player 9, you might be want to upgrade it into window media player 11.

THe problem is during the installation the installer will detect your Windows is a pirate version.

To encounter this problem I simply found an easy solution at http://www.tech-recipes.com/rx/2577/wmp11_install_windows_media_player_11_without_activating_bypass_wga/

window media player is a better player from window because of its lightweight and nice interface. Playlist and music manager is also available.

Well, from the link given I simply give a short tutorial (repeating the same thing)

1. Download the window media player 11 at microsoft website (image below is the installer I downloaded)



2. Extract the installer wmp11-windowsxp-x86-enu.exe


3. After extract, It supposed to looks liek this


4. Run the wmfdist11.exe


5. Run the wmp11.exe


6. Restart you PC

6. And the WMP 11 is installed

Thats it

CakePHP utf8 special character chinese character

i involve in some database with a chinese character.
It turns out my find('all') give me "????" for the chinese character

After some google, this is because database encoding. Its need to be utf-8
So this is the link thats help me http://nik.chankov.net/2007/10/01/cakephp-and-character-set-in-the-database/

Conclusion is, inside the database.php put the 'encoding'

class DATABASE_CONFIG {

var $default = array(
'driver' => 'mysql',
'persistent' => false,
'host' => 'localhost',
'login' => 'user',
'password' => 'password',
'database' => 'project_name',
'prefix' => '',
'encoding' => 'utf8'
);
}

What will happen actually is before any SQL query is executed.
It will execute this code first, I think.

"SET NAMES 'utf8'"

Just to remind something. If there is a case you table Collation/charset is not utf-8 means you will not be able to use Chinese character. So here is the tweak

ALTER TABLE 'tblcustomers' COLLATE utf8_general_ci;

And still, my website did not display the chinese character.
So I end it with

<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=utf8"$gt;

and the problem is solve

Yeah!

PHP Error

Got this from Mark Baker at forums.devshed.com
where his signature says, which is use full if you don't have PHP debugging tool

error_reporting(E_ALL);
ini_set('display_errors', 1);

Store Password Manager

If there a case you have a many password to remember, the only solution is by storing it.

When talk about storing password in you PC, there always be a security issue.

in this case, I'm using KeePass as a Password manager

KeePass is free and highly encrypted password manager. More feature is describe in the website

You can get the link here http://keepass.info/index.html

MYSQL Export

I involve in some project where I need to do Export mysql table into .csv file

First of all, the table structure

CREATE TABLE IF NOT EXISTS `contacts` (
`id` int(20) unsigned NOT NULL auto_increment,
`name` varchar(200) NOT NULL,
`phonenumber` varchar(50) NOT NULL,
`group` varchar(50) NOT NULL,
`created` datetime default NULL,
`modified` datetime default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Contact detail' AUTO_INCREMENT=5 ;

INSERT INTO `contacts` (`id`, `name`, `phonenumber`, `group`, `created`, `modified`) VALUES
(1, 'Mr Number 1', '0126784669', 'Customer', '2010-05-12 09:50:57', '2010-05-12 09:50:57'),
(2, 'Mrs Testing', '0136785186', 'Customer', '2010-05-12 09:50:57', '2010-05-12 09:50:57'),
(3, 'Mr Bone', '0184531256', 'Staff', '2010-05-12 09:52:41', '2010-05-12 09:52:41'),
(4, 'Mr Gray', '0104578996', 'Staff', '2010-05-12 09:52:41', '2010-05-12 09:52:41');

Here is how to export the file into .csv
This code is in many blog and other tutorial

SELECT *
INTO OUTFILE "C:/Documents and Settings/asipo/My Documents/Downloads/contacts.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM contacts
WHERE `group` = 'Customer';

But most of the website I found, does not show how to create a .csv file with a extra column header description into it.
So this is how I do it

SELECT 'Id', 'Name', 'Phone Number', 'Group', 'Created', 'Modified'
FROM `contacts` LIMIT 1
UNION
SELECT *
INTO OUTFILE "C:/Documents and Settings/asipo/My Documents/Downloads/contacts.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n'
FROM `contacts`

As a result, you going to see somthing like this


Thats all