Herikstad Solutions: November 2009

Friday, November 20, 2009

Character sets UTF-8 and ISO-8859-1 in HTML and PHP

Character sets can bring much agony to programming for the web. Here are some useful functions and ideas about handling charsets.

There are two main charsets: UTF-8(Unicode) and ISO-8859-1. UTF-8 lets one handle more characters than ISO-8859-1 (such as arabic and chinese characters).

HTML files that should handle unicode characters must have this set in the header:

<head>
   <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>

Here one could also use 'iso-8859-1' instead.

If you need to convert between ISO-8859-1 and UTF-8 in PHP the functions utf8_encode and utf8_decode are useful. utf8_encode will convert from ISO-8859-1 to UTF-8 and utf8_decode from UTF-8 to ISO-8859-1.
The use of the functions is:

$utf8string =  utf8_encode( $iso-8859-1string );
$iso-8859-1string =  utf8_decode( $utf8string );

In addition to UTF-8 there are two other Unicode standards UTF-16 and UTF-32. The difference is how many bytes are used to store a character and thus how many different characters can be stored. UTF-32 is in little use, and so is UTF-16, but UTF-16 is used in more places.

To convert between these Unicode charsets use iconv. It is used like this:
$utf16string = iconv("utf-8", "utf-16", $utf8str);
or
$utf8string = iconv("utf-16", "utf-8", $utf16str);

incov can also be used to convert from other character sets such as ISO-8859-1 like this:
$utf16string = iconv("iso-8859-1", "utf-16", $iso-8859-1string);

Solutions found at: http://www.php.net/manual/en/function.utf8-decode.php, http://php.net/manual/en/function.utf8-encode.php, http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_21916597.html, and http://www.php.net/manual/en/function.iconv.php.

Wednesday, November 11, 2009

Time comparison in SQL

Time on the form 2009-12-31 22:12:34 can be hard to compare exactly. Therefore it is useful to have some ways of simplifying this.

Using DATE_FORMAT is useful.
SELECT * FROM table WHERE DATE_FORMAT(column, '%Y%m%d') = '20091231'
or
SELECT * FROM table WHERE DATE_FORMAT(column, '%Y-%m-%d') = '2009-12-31'
will give you rows with column value of that day.

The following can also be used apparantly:

WHERE DateDiff(dd, OrderDate, '01/01/2006') = 0
WHERE Convert(varchar(20), OrderDate, 101) = '01/01/2006'
WHERE Year(OrderDate) = 2006 AND Month(OrderDate) = 1 and Day(OrderDate)=1
WHERE OrderDate LIKE '01/01/2006%'
WHERE OrderDate >= '01/01/2006'  AND OrderDate < '01/02/2006'

and

WHERE OrderDate BETWEEN '01/01/2006' AND '01/02/2006'

In addition, getting values where date is less than or larger than a given difference from now or a given time. On can use INTERVAL:
SELECT * FROM table WHERE createdate < DATE_SUB(NOW(),INTERVAL 3 DAY)
will give rows where createdate is before 3 days ago.

Solutions found at http://www.w3schools.com/SQL/func_date_format.asp and http://demiliani.com/blog/archive/2006/01/19/3384.aspx

Monday, November 2, 2009

Time functions in PHP

Getting time in the right format can be a challenge. The default format is unix timestamp (a number giving the number of seconds since January 1 1970 00:00:00 UTC). However one often want to extract the date in a certain format for display purposes or for use in databases.

There are some functions that are useful for doing this. They are date(),strtotime(), mktime().

mktime will return the unix timestamp from the arguments passed into it, e.g. one can give it minutes, hours, days,months, years:
mktime(0,0,0,3,20,2004) which is "20/03/2004 00:00:00" will be 1079737200.

date will return a string formatted according to the given format. Several arguments can be given, including "Y" for 2009 while "y" gives 09 etc. Examples:

$today = date("F j, Y, g:i a");                 // March 10, 2001, 5:16 pm
$today = date("m.d.y");                         // 03.10.01
$today = date("j, n, Y");                       // 10, 3, 2001
$today = date("Ymd");                           // 20010310

date("d, m, Y", mktime(0,0,0,3,20,2004)) // 20, 03, 2004
date("d, m, Y", strtotime("now")) // 03, 11, 2009 )

Or
currentWeek = date("W", strtotime("now")) //45 for 03.11.2009

strtotime will take a string and try to convert it into a unix timestamp. An example is "now" which will give the time right now in unix timestamp. Other examples are strtotime("10 September 2000") and strtotime("+1 day").

Sources: http://php.net/manual/en/function.date.php, http://php.net/manual/en/function.strtotime.php, http://php.net/manual/en/function.mktime.php