Unicode Like Chinese Japanese Korean Characters In and Out MySQL Through PHP & Java

Both MySQL and PHP/Java support the Unicode, namely UTF-8. To be able to store into and retrieve these Unicode characters in MySQL database, one would need to do following things:

MySQL needs to be informed through PHP/Java. So, following code needs to be placed just after the connection. If the connection is centralized, this code is better to be in that centralized file:

PHP: mysql_query("SET NAMES 'UTF8'");
Java: stmt.execute("SET NAMES 'utf8'");

For all your PHP files generates HTML scripts, please include following code at top of the file:

header("Content-Type: text/html; charset=UTF-8");

For all your HTML files, following script is needed in the head of HTML:

‹ meta http-equiv="Content-Type" content="text/html; charset=UTF-8" ›

Done. Please note, in first piece of PHP code, it is UTF8 and in second PHP code and last HTML script it is UTF-8. Else, it won’t work properly.

If your codes include any of following piece, simply remove it:

PHP: mysql_query("SET CHARACTER SET 'UTF8'");
Java: stmt.execute("SET CHARACTER SET 'utf8'");

CSS and Link

CSS can be written two ways. The first way has selector and second way does not have selector but class selectors only.

First way:

sty
{ color: #333333;
}

First way covers every situation of class selectors. One for all. However, if a particular situation, say “hover” is needed to be excluded from the uniform of selector, it can be added as an overwrite:

sty
{ color: #333333;
}
sty:hover
{ color: #CCCCCC;
}

Second way:

sty:link
{ color: #333333;
}
sty:visited
{ color: #FFFFFF;
}
sty:hover
{ color: #CCCCCC;
}
sty:active
{ color: #333333;
}
Using second way, one must specify every situation. Else, it would inherited from the rule that is used by the element in which the link exists. For 2nd way, there is an order requirement: LoVe/HAte, else, it won't work.

The CSS can be not only used for link, but text as well. However, to enable class selectors work properly, the anchor must be exist:

‹a href="http://koncordpartners.blogspot.com" class="sty"›
This is the anchor started with letter “a”. If no link needed, use this href: href="#".
‹/a›

It also means applying class at table or cell/td level won'd work.


http://www.projectseven.com/tutorials/css/pseudoclasses/

Number of Elements of Array

Number of elements of array is very usual, which can be used as control for the loop or other purposes. Function to get it in JavaScript is varArray.length. In PHP it is count($ varArray) or sizeof($ varArray). Since sizeof() is just an alias of count(), it is suggested using count(), since sizeof() has other meaning in other languages.

The problem happens when array is undefined/not exist. In JavaScript .length would generate a fatal error. So, for save play, it is suggested always using follow codes:

var len = 0;
if ('undefined'!=typeof(varArray)) len = varArray.length;

In PHP count() would generate a return value of 0 together with a non-fatal error message, which would cause even more serious problem because of its non-fatal nature. Fortunately, PHP provides an easy non-fatal error message suppress method “@”:

@count($varArray);

It is very useful because it enables you to always associate @ with count(). And please do so.

For multidimensional array, the usage will be exactly same; and can be used for particular dimension, such as varArray[i] or $varArray[i].

Test Various “Nothings” in PHP

Following php codes is to test six different “nothing”variables together with popular functions, plus number 1 as control.

‹?php
$undefinedVar;
$nullVar = null;
$strEmpty = "";
$strChanged = "initial value";
$numZero = 0;
$numChanged = 1;
$numOne = 1;

$strChanged = null;
$numChanged = null;

echo "‹br/›11. undefinedVar: (".$undefinedVar.")";
echo "‹br/›12. nullVar: (".$nullVar.")";
echo "‹br/›13. strEmpty: (".$strEmpty.")";
echo "‹br/›14. strChanged: (".$strChanged.")";
echo "‹br/›15. numZero: (".$numZero.")";
echo "‹br/›16. numChanged: (".$numChanged.")";

echo "‹br/›21. is_null-undefinedVar: (".is_null($undefinedVar).")";
echo "‹br/›22. is_null-nullVar: (".is_null($nullVar).")";
echo "‹br/›23. is_null-strEmpty: (".is_null($strEmpty).")";
echo "‹br/›24. is_null-strChanged: (".is_null($strChanged).")";
echo "‹br/›25. is_null-numZero: (".is_null($numZero).")";
echo "‹br/›26. is_null-numChanged: (".is_null($numChanged).")";

echo "‹br/›31. isset-undefinedVar: (".isset($undefinedVar).")";
echo "‹br/›32. isset-nullVar: (".isset($nullVar).")";
echo "‹br/›33. isset-strEmpty: (".isset($strEmpty).")";
echo "‹br/›34. isset-strChanged: (".isset($strChanged).")";
echo "‹br/›35. isset-numZero: (".isset($numZero).")";
echo "‹br/›36. isset-numChanged: (".isset($numChanged).")";

echo "‹br/›47. TRUE: (".TRUE.")";
echo "‹br/›48. FALSE: (".FALSE.")";

echo "‹br/›51. empty-undefinedVar: (".empty($undefinedVar).")";
echo "‹br/›52. empty-nullVar: (".empty($nullVar).")";
echo "‹br/›53. empty-strEmpty: (".empty($strEmpty).")";
echo "‹br/›54. empty-strChanged: (".empty($strChanged).")";
echo "‹br/›55. empty-numZero: (".empty($numZero).")";
echo "‹br/›56. empty-numChanged: (".empty($numChanged).")";
echo "‹br/›57. empty-numOne: (".empty($numOne).")";

echo "‹br/›61. defined-undefinedVar: (".defined($undefinedVar).")";
echo "‹br/›62. defined-nullVar: (".defined($nullVar).")";
echo "‹br/›63. defined-strEmpty: (".defined($strEmpty).")";
echo "‹br/›64. defined-strChanged: (".defined($strChanged).")";
echo "‹br/›65. defined-numZero: (".defined($numZero).")";
echo "‹br/›66. defined-numChanged: (".defined($numChanged).")";

echo "‹br/›71. strlen-trim-undefinedVar: (".strlen(trim($undefinedVar)).")";
echo "‹br/›72. strlen-trim-nullVar: (".strlen(trim($nullVar)).")";
echo "‹br/›73. strlen-trim-strEmpty: (".strlen(trim($strEmpty)).")";
echo "‹br/›74. strlen-trim-strChanged: (".strlen(trim($strChanged)).")";
echo "‹br/›75. strlen-trim-numZero: (".strlen(trim($numZero)).")";
echo "‹br/›76. strlen-trim-numChanged: (".strlen(trim($numChanged)).")";
?›

unset() does not work for any one of them. Therefore, it had been excluded.

Test results in both Firefox and IE are identical, as follows:

11. undefinedVar: ()
12. nullVar: ()
13. strEmpty: ()
14. strChanged: ()
15. numZero: (0)
16. numChanged: ()
21. is_null-undefinedVar: (1)
22. is_null-nullVar: (1)
23. is_null-strEmpty: ()
24. is_null-strChanged: (1)
25. is_null-numZero: ()
26. is_null-numChanged: (1)
31. isset-undefinedVar: ()
32. isset-nullVar: ()
33. isset-strEmpty: (1)
34. isset-strChanged: ()
35. isset-numZero: (1)
36. isset-numChanged: ()
47. TRUE: (1)
48. FALSE: ()
51. empty-undefinedVar: (1)
52. empty-nullVar: (1)
53. empty-strEmpty: (1)
54. empty-strChanged: (1)
55. empty-numZero: (1)
56. empty-numChanged: (1)
57. empty-numOne: ()
61. defined-undefinedVar: ()
62. defined-nullVar: ()
63. defined-strEmpty: ()
64. defined-strChanged: ()
65. defined-numZero: ()
66. defined-numChanged: ()
71. strlen-trim-undefinedVar: (0)
72. strlen-trim-nullVar: (0)
73. strlen-trim-strEmpty: (0)
74. strlen-trim-strChanged: (0)
75. strlen-trim-numZero: (1)
76. strlen-trim-numChanged: (0)

Observed, included but not limited to this test:

1. Results in Firefox and IE are exactly same. However, if an null variable passed from JavaScript to PHP, in IE, it would be shown as a string "null". In a real case scenario, a variable is to pass from form feeding in HTML/JavaScript to PHP. However, it had only be assigned with value “null”. It supposes an integer data type. Unfortunately, in is_null() in PHP, it results not null, while echo shows “null” in IE and nothing in Firefox.
2. unset() does not work at all for all of them.
3. is_null() works fine. It however regards empty string as not null, which should be.
4. isset() works fine. It regards empty string as set.
5. is_null() is just opposite to isset().
6. empty() works for all, including regarding number 0 as empty.
7. defined() is to detect if a constant string exists, which does works fine here, since no constant string here.
8. strlen(trim()) does work fine except number 0, which should be.

Conclusion:

First of all, never use null in JavaScript where it supposed to be a string. Use '' instead. This is because in IE, when JavaScript passes string to PHP, null would become 'null'.

1. If one want to include everything, null, empty string, number 0, and even string “0”, the best approach is to use if(empty($var)).

2. If one wants to include null and empty string and exclude number 0, if(0==strlen(trim($var)) might be best approach. However, this approach is unable to detect the string "null".

3. If one wants to include null and number 0 and exclude empty string, the best approach maybe if(is_null($var) || 0==$var).

4. If one wants to only include null and exclude empty string and number 0, the best approach is if(is_null($var)). However, since both JavaScript and PHP does not tell the data type when declare a variable, it somehow easily to be mixed up the undefined variable or null variable with empty string. It is therefore suggested extra caution shall be applied to exclude empty string.

5. In most case people dealing with null would include undefined variable, null variable, empty string in JavaScript and PHP. For special case mentioned above for IE, it would includes string "null" as well, which isn't covered by Conclustion 2. So, the possible most save approach is put two together:

if(0==strlen(trim($var)) || "null"==$var)

6. To deal with undefined array, please refer to http://koncordpartners.blogspot.com/2009/12/number-of-elements-of-array.html.

http://ca2.php.net/manual/en/function.empty.php

Are “Title” and "Top" Keywords in JavaScript?

No, actually they is not. However, please do not use them as name or id in HTML. Following problem has been notices:

‹input id=”title” type=”text” value”Title is forbidden” ›

When try to get value by document.getElementById('title').value, it generate null in IE. If one exchanges the “title” with other word, it works fine.

In Chrome, one case use top() as function name, it does not work while it works fine with IE and Firefox.

Very special!

http://aptana.com/reference/html/api/JSKeywords.index.html

Days in the Month in T-SQL

Number of days in the month are same as the last day’s date number in the month. Unlike PL/SQL, in T-SQL there is no function called last_day(). Following are two methods to get it:

Method 1, if you know the date:

DECLARE @Date datetime
SET @Date = '2009/12/10'
SELECT DAY(DATEADD(d,-1,dateadd(m,1,@Date))) AS 'Last day of the month'

Method 2, if you know the year and month number:

SELECT DAY(DATEADD(D, -1, DATEADD(M, 1, CONVERT(DATETIME, (CONVERT(VARCHAR, Yer) + '-' + CONVERT(VARCHAR, Mth) + '-01'))))) AS 'Last day of the month'

http://www.bigresource.com/MS_SQL-last_day-function-s5q435mI.html

Contain Data Layout in SQL Part for Reporting Services

Business Intelligence in both ORACLE and SQL Server has offered standard tools and settings, such as cube and dimensions. However, for complicated reports, these tools may not be very useful. The orthodox approach to this issue is to have a customized OLAP database built. In some case, even with OLAP database, complicated queries still needed. This issue does also cause the further problem of data layout in report. Here is an approach to deal with these issues. Basically, this approach does contain the task of data layout in SQL part to avoid complex programming in Stored Procedures.

Let us say a report is required to present a daily summary of patient movement within a State, with certain criteria. It requires 5 columns: Date, Sending Facility, Receiving Facility, total transports between these two facilities in that day [Transports], and the Daily Subtotal for whole State. Indeed, Daily Subtotal and Transports can share same column. However, for better visual effect, it is required to have separate columns. GROUP BY and other analytic functions can be used to generate numbers for Transports and Daily Subtotal. However, for the sake of data layout in report, it is suggested only GROUP BY is used. As at today, almost all other analytic functions just generate undesired data layout, for instance, extra but unneeded columns from PIVOT.

The data was not located in a single table, so joins are necessary. Since the selection criteria (WHERE Conditions) are not strictly have one-to-one relationship, the joins therefore are Outer Joins to ensure needed records won’t be excluded. Here is FROM clause:

FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN ……

The sending facility and receiving facility indeed is in same table. So, it had been outer joined twice with flag as s and r.

Because of one-to-more relationship, it is for sure multiple rows will be generated for some records. So, mechanism to distinct records is needed. Unfortunately, DISTINCT function does not work with GROUP BY. So, an inner query is used strictly only selecting the essential fields together with DISTINCT:

( SELECT DISTINCT DATEADD(HH, 12, DATEDIFF(DD, 0, t.add_datetime)) Entry_Date
, m.mt_number [MT#]
, s.healthcare_facility_name Sending_Facility
, r.healthcare_facility_name Receiving_Facility
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN …
WHERE …
) AS Distinct_Q

Do not forget to add “AS [name_of_inner_query]” at the end of inner query, which is required by Transact-SQL. DATEDIFF(DD, 0, t.add_datetime) is used here to uniform the time during the date enabling the use of GROUP BY. DATEADD(HH, 12, …) will be explained late. If Distinct_Q contains less fields needed by next step, it is neccessary to join back to retrieve the missing fields.

Next step is to use GROUP BY:

( SELECT Entry_Date
, Sending_Facility
, Receiving_Facility
, COUNT(*) Daily_Transports
FROM
(…) AS Distinct_Q
GROUP BY Entry_Date
, Sending_Facility
, Receiving_Facility
) AS Detail_Q

So far, we generate all essential parts of the report except the Daily Subtotal. We can use same structure for Daily Subtotal. Since inner query Distinct_Q used twice, it is worthwhile to move this temporary resultset to the head with WITH:

WITH Distinct_Q
AS
(…
)

(SELECT DATEADD(HH, -1, Entry_Date) E_Date
, COUNT(*) Daily_Subtotal
FROM Distinct_Q
GROUP BY Entry_Date
) AS Daily_Subtotal_Q

Note, DATEADD(HH, -1, Entry_Date) is used again here.

We can use UNION to put two temporary resultsets Detail_Q and Daily_Subtotal_Q together:

SELECT Entry_Date [Entry Date]
, Transfer_Status [Transfer Status]
, Sending_Facility [Sending Facility]
, Receiving_Facility [Receiving Facility]
, CAST(Daily_Transports AS VARCHAR(3)) Transports
, '' [Daily Subtotal]
FROM
(…) AS Detail_Q
UNION
SELECT E_Date [Entry Date]
, '' [Transfer Status]
, 'Daily Subtotal' [Sending Facility]
, '' [Receiving Facility]
, '' Transports
, CAST(Daily_Subtotal AS VARCHAR(3)) [Daily Subtotal]
FROM
(…) AS Daily_Subtotal_Q

The purpose of CAST(… AS VARCHAR(3)) used here is to avoid the empty string '' automatically turns to 0 after UNION. Field [Daily Subtotal] in Detail_Q is string data type and Daily_Subtotal_Q is number. So does for field [Transports].

The final step to to add ORDER BY at the end:

ORDER BY [Entry Date] DESC
, [Transfer Status]
, [Sending Facility]
, [Receiving Facility]
, [Transports]
, [Daily Subtotal]
GO

It is time to explain why using DATEADD() function above. Since [Entry Date] is to be sorted DESCENT-ly, one hour difference as result of two DATEADD() functions could place row of ‘Daily Subtotal’ at the end of every rows for that day.

Here is completed SQL script:

WITH Distinct_Q
AS
( SELECT DISTINCT DATEADD(HH, 12, DATEDIFF(DD, 0, t.add_datetime)) Entry_Date
, m.mt_number [MT#]
, s.healthcare_facility_name Sending_Facility
, r.healthcare_facility_name Receiving_Facility
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN …
WHERE …
)
SELECT Entry_Date [Entry Date]
, Transfer_Status [Transfer Status]
, Sending_Facility [Sending Facility]
, Receiving_Facility [Receiving Facility]
, CAST(Daily_Transports AS VARCHAR(3)) Transports
, '' [Daily Subtotal]
FROM
( SELECT Entry_Date
, Sending_Facility
, Receiving_Facility
, COUNT(*) Daily_Transports
FROM Distinct_Q
GROUP BY Entry_Date
, Sending_Facility
, Receiving_Facility
) AS Detail_Q
UNION
SELECT E_Date [Entry Date]
, '' [Transfer Status]
, 'Daily Subtotal' [Sending Facility]
, '' [Receiving Facility]
, '' Transports
, CAST(Daily_Subtotal AS VARCHAR(3)) [Daily Subtotal]
FROM
(SELECT DATEADD(HH, -1, Entry_Date) E_Date
, COUNT(*) Daily_Subtotal
FROM Distinct_Q
GROUP BY Entry_Date
) AS Daily_Subtotal_Q
ORDER BY [Entry Date] DESC
, [Transfer Status]
, [Sending Facility]
, [Receiving Facility]
, [Transports]
, [Daily Subtotal]
GO

This blog is to illustrate how to manipulate the data layout in report simply by use SQL. You can also refer to http://koncordpartners.blogspot.com/2009/08/practical-case-of-transpose-query.html.


http://koncordpartners.blogspot.com/2010/12/special-function-of-order-by-clause-in.html