Database access utility in PHP

Added a class library to sourceforge.net to access to database using ODBC/PDO. http://sourceforge.net/projects/jh-php-class/.

The readme file and the PHP_basic_Class.htm will show you how to run the commands.

JH-JS-CLASS

Decided to post the whole Javascript class on to Sourceforge. Posting it here is not so convenient as the postings cannot allow for <,>, and &. Moreover, when user copied the codes, it might cause problems if the text copied breaks the code by inserting newline character.

In future, I will describe the code here and leave the code at sourceforge if the codes are some library I created and added to the basic_class.js file. The URL is http://sourceforge.net/projects/jh-js-class/

CalendarPopUP explained.

In my earlier post I have a calendar function listed. Although I have never tried to explain the codes that I have posted. The following is an attempt to do so.

The function has there parameters. First parameter is the ID of the input field. There is a problem with IE on getElementsByName thus I cannot use name of the input field for cross browser compatibility purpose.

Second parameter is the date format. The input accepts YMD, DMY, MDY format only. As said before, user can add their own formats. It is quite straight forward.

The third parameter is not user definable. It is intended for the popup to call back to $cP to change the month.

The overall functionality of this code is to create a popup window and insert a calendar using Javascript. When user clicks on the date in the calendar. This value is returned to the input field which calls the $cP function.

When user changes month, the popup calls back to $cp by passing back the same parameter plus a date which is one month before or after the current month. $cP will see the new parameter and replaces the popup with a new calendar.

The following is a line by line explanation of the function.

The function first creates the array for parsing the month information for display purpose.

It then check whether the third parameter is defined. This is used by the popup window to call back by passing a new date. If it is defined then the curdate is set to the new date otherwise curdate is the os date.

curmth and curyr gets the month and year of the curdate for use when choosing background of the calendar.

fdm gets the new date by setting the day of the month of curdate to 1. This change is for getting the weekday of the first day of the month.

ldm stores the last month's date. The if condition address a problem with "Date" function. It seems that "Date" function cannot take negative Month while trying to get last month date. If the month is already 0 it switch to reducing the year by one and set month to 11 otherwise just minus the month by 1.

ndm is the next month's date. Both ldm and ndm is used in the calendar for user to click on and change the month.

fdm is trying to set itself by deducting the day of the week from it. "setDate" is a javascript function to add days to a date. The days can be negative. (Interestingly even if you set the days to -0, it still deducts a day from the date.) The purpose of this function is to ensure that the first date chosen is the first row first column of the calendar. The first column is of course "Sunday".

The first day of the month may not be Sunday. Thus, I need to find out which day of the last month, counting back from the first day of the current month, is a "Sunday".

If I can find the first Sunday date then I could add the rest of the days to the calendar by filling them sequentially. Now I am ready to set the calendar.

The next few lines is just to create the document of the popup window. You can use document.write to create any DOM. Obviously, I am writing to the popup window. Thus I set obj to the opened window's document object.

It is quite tedious to use document.write. However, I have no choice as I do not want to create the actual web page physically.

After creating the necessary CSS, I create the the table. One of the lines compose the last month's date and next month's date with the current month displayed between them. This will allow user to change date by clicking on either side of the current month.

Next, we create the body of the calendar itself. A loop within a loop ensures that there are 5 rows of 7 columns created each filled with a date from fdm which is increased by 1 day each loop. The onclick attrbute will set the calling input field with the date value if clicked.

The iGray and iWhite class is to set the date background to gray if it is not current month.

The switch option is to set the format of the date according to the second parameter of the $cP function.

The fdm is increased by one day.

You would be curious to know what is the if condition doing in the first loop. Well, we all know that if the first day of the month is a "Saturday" and the month may be 30 or 31 days. 5 rows with 7 columns may not be enough to fill all the days of the month. It is therefore necessary to add one more row if fdm after the fifth row is greater than 29. That is to say, it is not already at the next month's date. 29 is chosen because if Saturday is 1, 30 and above will be on the 6th row. Feb 29 will never fall on the 6th row.

The rest of the code is to close the table and add a "close" for user to avoid choosing a date.

obj.close is there because if you don't close it. The window popup will hang for a considerable time before showing the content.

obj.focus is there if you happen to click on the main window while the popup window is still open. I can't help you if you minimize the popup window. Any subsequent calling of $cP will not wake it from minimize condition.

Calendar PopUp

There has been quite a number of Javascript Calendar PopUp available in the internet. Many of them are very professionally made. However, I seems to have trouble using them. They either do not work out-of-the-box or needs a few settings here and there to function properly.

What I wanted is to have a very simple utility that allows me to choose a date in a limited format. I do not want to have too many settings. My habit is always to make life easy for programmers by not bothering them with too much options in my library.

The following is my version of a CalendarPopUp.

var $cP=function(myid,query2,query3){

// must set the id value of the date input element to work properly

// call the funcstion and pass the id value and type (MDY,DMY or YMD). query3 is not user definable

//remember to set the date input element to read only

var months=['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December']

if (typeof(query3) != "undefined"){

var odate=query3.split("-")

var curdate=new Date(odate[0],odate[1],odate[2],0,0,0,0)

}

else

var curdate=new Date()

var curmth=curdate.getMonth()+1

var curyr=curdate.getFullYear()

var x,y

fdm=new Date(curdate.getFullYear(),curdate.getMonth(),1,0,0,0,0)

if(fdm.getMonth() !=0)

ldm=fdm.getFullYear()+"-"+(fdm.getMonth()-1)+"-"+fdm.getDate()

else

ldm=(fdm.getFullYear()-1)+"-11-"+fdm.getDate()

ndm=fdm.getFullYear()+"-"+(fdm.getMonth()+1)+"-"+fdm.getDate()

fdm.setDate(fdm.getDay()*-1)

var $winP=window.open("","cP","toolbar=0,location=0,drectories=0,menubar=0,scrollbars=0,resizable=0,width=250,height=200,status=0",true)

obj=$winP.document

obj.write('<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01//EN\" \"http://www.w3.org/TR/html4/strict.dtd\">')

obj.write('<html><head><title>Calendar</title>\n');

obj.write('<style type=\'text\/css\'>\n')

obj.write('table {width:100%;border-width: 3px;border-style:solid;border-spacing:0;border-collapse:collapse;}\n')

obj.write('td,th {font-size:12px;margin:0px;border-width: 2px 2px 2px 2px;border-style:solid;border-spacing:0;border-collapse:collapse;text-align:center;cursor:hand}\n')

obj.write('td:hover {background-color:yellow;font-weight:bold;cursor:pointer}\n')

obj.write('.hv:hover {background-color:yellow;font-weight:bold;cursor:pointer}\n')

obj.write('.igray {background-color:#BDBDBD}\n')

obj.write('.iwhite {background-color:white}\n')

obj.write('p {width:100%;text-align:center;margin:0}\n')

obj.write('<\/style>\n')

obj.write('</head><body>');

obj.write('<table>');

obj.write('<tr><th class=\'hv\' onclick=window.opener.$cP(\"'+myid+'\",\"'+query2+'\",\"'+ldm+'\")>&lt;</th><th colspan=5>'+months[curdate.getMonth()]+" "+ curdate.getFullYear()+'</th><th class=\'hv\' onclick=window.opener.$cP(\"'+myid+'\",\"'+query2+'\",\"'+ndm+'\")>&gt;</th></tr>')

obj.write('<tr><th>S</th><th>M</th><th>T</th><th>W</th><th>T</th><th>F</th><th>S</th></tr>')

var myhtml=""

fdm.setDate(fdm.getDate()+1)

for (x=1;x<=5;x++){

myhtml +="<tr>"

for(y=1;y<=7;y++){

newmth=fdm.getMonth()+1

newyr=fdm.getFullYear()

if (curmth != newmth || curyr != newyr)

cal='igray'

else

cal='iwhite'

switch (query2){

case "MDY":

sdm=(fdm.getMonth()+1)+"/"+fdm.getDate()+"/"+fdm.getFullYear()

break

case "DMY":

sdm=fdm.getDate()+"/"+(fdm.getMonth()+1)+"/"+fdm.getFullYear()

break

default:

sdm=fdm.getFullYear()+"-"+(fdm.getMonth()+1)+"-"+fdm.getDate()

}

myhtml +="<td class='"+cal+"' onclick='window.opener.document.getElementById(\""+myid+"\").value=\""+sdm+"\";self.close()'>"+fdm.getDate()+"</td>\n"

fdm.setDate(fdm.getDate()+1)

}

myhtml +="</tr>"

if (x==5 && fdm.getDate() > 29)

x -=1

}

obj.write(myhtml)

obj.write('</table>');

obj.write('<p><a href="javascript:self.close()">close</a></p>');

obj.write('</body></html>');

obj.close();

obj.focus();

}

There is no preset available. The function is complete by itself. Just call the function and pass the necessary parameter and that is all.

In your form you would set the date input field as below example

<input type="text" name="mydate" id="mydate" value="" readonly onclick="$cP(this.id,'YMD')">

When user clicks on the input, the calendar will popup for user to choose a date.

User can navigate by month to choose different months using the "<" and ">" on both sides of the month indicator.

The second parameter of the $cP function has three formats. "YMD" displays "YYYY-MM-DD" format. "DMY" displays "DD/MM/YYYY" format. Finally, "MDY" displays "MM/DD/YYYY" format.

I do not want to add more options beside the three that I always use. It is up to individuals to add their own options. Adding it is very simple. Just go to the place where the switch(query2) is and add another case option.

The above is tested on IE7. Although I could not make the size of the popup less than that which IE7 limited. It worked just fine. Even the css hover worked after I set the DTD to "strict".

On Firefox, the window features seems to have a mind of its own. It does not allow me to hide a number of features which I turned off. However, this is firefox's preferred setting and since it does not hamper the functionality, it is acceptable despite having a bit too much unnecessary things.

On Chrome, only the address line is still visible.

I did not have other browsers installed thus does not test those.

Form Validation update

Previous post on the same subject saw a list of functions. Below is a modified update.

var errMsg="";

var $vF= function(qtype,query,query2,startw,endw){

//use $vF() without param to echo errMsg and clears it for the next form validation.

var tempMsg="";

var varRegEx=/.+/;

var testvalue="";

if (startw==null)

startw=""

if (endw==null)

endw=""

var regExAry={

"Empty":" must not be empty\n",

"SqlOK":" must not be empty or contains tab, enter, quotation marks, ampersand or semicolon character\n",

"Email":" is not valid Email\n",

"URL":" is not valid URL\n",

"Number":" is not valid Number\n",

"Alpha":" must not be empty and is Alphabet and space only\n",

"Integer":" is not valid Integer\n",

"Range":" is not between " +startw+ " and "+ endw+"\n",

"Zip":" is not valid Post Code\n",

"Positive":" is not positive number\n",

"Begin":" must start with "+startw+" and is "+endw+" digits\n",

"Date":" is not valid Date (DMY, MDY, or YMD)\n",

"YMD":" is not valid Date (DMY, MDY, or YMD)\n",

"Phone":" is not a valid phone number\n",

"Pwd":" must start with an Alphabet followed by at least one each of upper case, lower case, number and special characters. Length must be "+startw + " to "+endw+" characters\n",

"Radio":" must have a selecstion\n",

"Select": " must have a selection\n",

"CheckBox":" must be checked\n",

"CheckBoxList":" must have at least one item checked\n",

"AgteB":" must be greater than or equal "+endw+"\n",

"AeqB":" must be same as "+endw+"\n",

"Fixed":" must be exactly "+startw+" characters\n",

"Maxlen":" must not exceed "+startw+" characters\n",

"Name":" must be proper name with caps on every first character of word\n"

}

if (qtype!="Radio" && qtype !="Select" && typeof(qtype) != "undefined")

testvalue=query.value

switch (qtype){

case "Empty":

varRegEx=/.+/

break

case "SqlOK":

if (query.nodeName != "TEXTAREA")

varRegEx=/[\0\f\n\r\t\v'"&;]/

else

varRegEx=/[\0'"&;]/

break

case "Email":

varRegEx = /^([a-z0-9]+)([_\-.][a-z0-9]+)*@+([a-z0-9]+)([_\-.][a-z0-9]+)*[.]+[a-z]{2,4}$/i;

break

case "URL":

varRegEx = /^(HTTPS?|FTP):\/\/[a-z0-9_-]+(\.[a-z0-9_-]+)*\.[a-z]{2,4}(\/.+)*\/?$/i;

break

case "Alpha":

varRegEx = /(^[a-z ]+$)/i;

break

case "Number":

varRegEx = /(^[+-]?[0-9]+\.[0-9]{1,}$)|(^[+-]?[0-9]+$)/;

break

case "Integer":

varRegEx = /^[+-]?[0-9]+$/;

break

case "Range":

var locRegEx = /^[0-9]+$/;

var localtest=locRegEx.test(testvalue)

if (localtest){

testvalue=testvalue*1

if (testvalue < startw || testvalue > endw)

testvalue=""

}

else

testvalue="true"

break

case "AgteB":

// B is actually less than A so no need a "Less Than" test.

var locRegEx = /^[0-9]+$/;

var swv=startw.value*1

var localtest=locRegEx.test(testvalue)

if (localtest){

testvalue=testvalue*1

if (testvalue < swv)

testvalue=""

}

else

testvalue="true"

break

case "AeqB":

if (testvalue != startw.value)

testvalue=""

break

case "Zip":

varRegEx = /^[0-9]{6}$/;

break

case "Fixed":

varRegEx = new RegExp("^(.|\n){"+startw+"}$")

break

case "Maxlen":

varRegEx = new RegExp("^(.|\n){1,"+startw+"}$")

break

case "Positive":

varRegEx = /(^[+]?[0-9]+\.[0-9]+$)|(^[+]?[0-9]+$)/;

break

case "Begin":

startw +=""

var myrlen=endw-startw.length

startw = startw.replace(/\\/g,"\\\\")

startw = startw.replace(/\//g,"\/")

startw = startw.replace(/\-/g,"\-")

varRegEx = new RegExp("(^"+startw+"[0-9]{"+myrlen+"}$)","");

break

case "Date":

varRegEx = /^((0?[1-9]|1[0-2])[\/-](0?[1-9]|(1[0-9]|2[0-9])|3[01])[\/-][12][0-9]{3})|((0?[1-9]|[12][0-9]|3[01])[\/-](0?[1-9]|1[0-2])[\/-][12][0-9]{3})|([12][0-9]{3}[\/-](0?[1-9]|1[0-2])[\/-](0?[1-9]|[12][0-9]|3[01]))$/;

break

case "YMD":

varRegEx = /^[12][0-9]{3}[\/-](0?[1-9]|1[0-2])[\/-](0?[1-9]|[12][0-9]|3[01])$/;

break

case "Phone":

varRegEx = /^(\+?65)?[689]{1}[0-9]{7}$/

break

case "Pwd":

varRegEx = new RegExp("(?=^.{"+startw+","+endw+"}$)[a-zA-Z]+(?=.*[A-Z])(?=.*[a-z])(?=.*\\d)(?=.*[^a-zA-Z0-9])")

break

case "Name":

varRegEx = /^[A-Z][a-z]+( [A-Z][a-z.]+)*( [A-Z]+)*$/

break

case "Radio":

for (radb=0;radb<query.length;radb++){

if (query[radb].checked == true)

testvalue="true"

}

break

case "Select":

if (query.selectedIndex != 0)

testvalue="true"

break

case "CheckBox":

if (query.checked == false)

testvalue=""

break

case "CheckBox":

if (query.checked == false)

testvalue=""

break

case "CheckBoxList":

var qform=query.form

var x

var qcnt=0

for (x=0;x<qform.elements.length;x++){

if (qform.elements[x].name == query.name){

if(qform.elements[x].checked == true)

qcnt=1

}

}

if (qcnt==0)

testvalue=""

break

default:

if(errMsg !="")

alert(errMsg)

errMsg=""

return 0

}

if ((varRegEx.test(testvalue)== false && qtype !="SqlOK") ||(qtype=="SqlOK" && varRegEx.test(testvalue)==true))

errMsg +=query2+regExAry[qtype]

return 0

};

The use of the above is quite straightforward. There is no setting required. Just perform the validation straight away. There is only a caveat that you should be aware of . The errMsg string will keep concatenating every time you run the function until you run the function without parameters.

Basically you should do the following.

obj=document,formname

$vF("Empty",obj.StartDate,"Registration Date")

$vF("Email",obj.Email,"Email")

$vF()

The function has 3-5 parameters. Most of the functions only uses 3. Javascript can accept null input parameters. The first parameter tells the function what test you are performing on the next parameter which is the form input element name to be tested. The third parameter is the user friendly name for the input element.

Fourth and fifth parameter is used for start/stop, max/min number for range test. It is also used to define a second input element name and its friendly name respectively. Some of the fourth parameter contains a number and there is no fifth parameter. This is usually used for testing for a fixed length of input.

You need not perform the test only once per input element. More test can be performed on the same input. For example, you perform a test on an input to ensure that it is a integer number and then perform another test on the same element to ensure that the number is exactly 6 digits.

At times you may want to perform condtional validation test. That is to say the input may not have entry but if it has an entry then the validation is performed, then you have to enclose the function with a condition test to ensure that the validation is bypassed if there is no input. The reason being all validation requires input to have a value (except the empty test).

$vF() will output the failed validation messages. At the same time it clears the errMsg content so that you could perform new set of tests.

Validating checkbox with same name

Normally validating checkbox with the same name is pretty simple. However, to submit to PHP you would want to use the name like xx[]. This then posts a problem in form validation as we cannot use document.form.xx[][1].checked to check if the element is checked.

It turned out that it is quite simple. Just treat the name as name and use the method below

document.form["xx[]"][1].checked

Javascript Regex

The following regex is valid.

myreg=/\w{2,3}/

However, if you use it to test "abcd", it still return true unless you test it with "a".

It turns out that you must code it as

myreg=/^\w{2,3}$/

just to get it return the right answer.

I have a headache with "^" and "$". I really don't know whether to use it or not. It seems like not every regex I used have to include them.

The trick is try without them first. If fail, then adds them in.

Creating Regex with literal backslash

Backslash in Regex has special meanings. So how do I create a Regex expression that contains a literal backslash.

It is quite simple. Just use "\\" instead of "\" in the Regex.

Now come the problem. I need to define a string variable and pass it to a function to create a Regex in the function.

For example, I need to create a string "A\" . The string can be defined as myvar="A\\" in javascript. Up till now it is fine. Now if I set

myRegex=new RegExp("^"+myvar,"")

in the function and test it, the script crashes.

It turned out that I must define the string as myvar="A\\\\" so that the RegExp can see the string as "A\\" and parse it as a literal "A\".

So much trouble to just create a backslash in Regex.

Javascript Form Validation

I used to do a script for every input to check their validity even to just check if it is blank.

The following is an easier and more streamline way of doing the same thing.

function isEmpty(query,query2){

if (query.value=="")

return query2+" must not be empty\n"

else

return ""

}

function isEmail(query,query2){

var emailRegEx = /^([a-z0-9][a-z0-9_.-]*[a-z0-9])+@+([a-z0-9][a-z0-9-]*[.])+[a-z0-9]{2,4}$/i;

if (emailRegEx.test(query.value)== false)

return query2+ " is not valid Email\n"

else

return ""

}

function isNumber(query,query2){

var numRegEx = /(^[+-]?[0-9]+\.[0-9]{1,}$)|(^[+-]?[0-9]+$)/;

if (numRegEx.test(query.value)== false )

return query2+ " is not valid Number\n"

else

return ""

}

function isInteger(query,query2){

var numRegEx = /^[+-]?[0-9]+$/;

if (numRegEx.test(query.value)== false)

return query2+ " is not valid Integer\n"

else

return ""

}

function isPlusNumber(query,query2){

var numRegEx = /(^[+]?[0-9]+\.[0-9]+$)|(^[+]?[0-9]+$)/;

if (numRegEx.test(query.value)== false)

return query2+ " is not Positive Number\n"

else

return ""

}

function isRadio(query,query2){

var testrad=false

for (radb=0;radb<query.length;radb++){

if (query[radb].checked == true)

testrad=true

}

if (testrad)

return ""

else

return query2 +" no selection\n"

}

function isSelect(query,query2){

// provided selected item 0 =not valid selection.

if (query.selectedIndex == 0)

return query2 +" no selection\n"

else

return ""

}

function isCheckBox(query,query2){

if (query.checked==true)

return ""

else

return query2+" must be checked\n"

}

function isPhone(query,query2){

// for Singapore only

var numRegEx = /(^[689]{1}[0-9]{7}$)|(^\+65[689]{1}[0-9]{7}$)/;

if (numRegEx.test(query.value)== false )

return query2+ " is not valid Phone Number\n"

else

return ""

}

function chksmt(){

obj=document.noname

ecode=""

ecode +=isEmpty(obj.Adr,"Address")

ecode +=isPlusNumber(obj.Pcode,"Postal Code")

ecode +=isInteger(obj.Cno,"Contact No")

ecode +=isEmail(obj.Email,"Email")

if (ecode !=""){

alert(ecode)

return 0

}

obj.submit()

}

Javascript Regex with numbers

Javascript Regex is a powerful tool. However, there is something you must take note when doing validation with numbers.

if your regex is /[0-9]+/ and your intention is to ensure that the input is just numbers. You would be surprised that when you enter ".1" or "1." it still passes.

I am not sure what happened but isNaN also behaves this way.

To ensure that only numbers is valid you would have to set the regex as /^[0-9]+$/

Deleting whole table with Identity Field

If you delete the whole table data by using "delete from mytable" SQL command or manually deleting all records through SQL Server Manager, The Identity field sequential number will not reset.

If you want to reset the Identity field sequential number, you should use "truncate table mytable" instead.

PHP PDO Prepare with date field

I was sending a date like '12/31/2010' to the database using ODBC database extension without problem.

Recently, I just tried to be more secure by avoiding SQL Injection attack by using PDO::Prepare. It worked just fine except when I need to update a date field. It failed to update without warning.

Unlike the ODBC series, the PDO does not report database errors by default. It took me quite some time before I realize that the date has to be in "Y-m-d" format.

By the way, to get PHP to show the PDO error you can set

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT); 

$sql = "SELECT * FROM booksa"; 

$q = $conn->query($sql) or die("ERROR: " . implode(":", $conn->errorInfo()));  

Getting element from DOM tree

If we want to get a cell element from a table, we would get the id of the table first. With the ID then we will navigate through the rows and cols to get to the cell.

A simple way to get this is

mycell = tblid.childNodes[x].childNodes[w]

Unfortunately, it is not that simple. Usually when people write html, they tends to spread out the tags in different lines for easier visibility. Often you would do this

<tr>

<td>text<td>

<tr>

In IE you would not have a problem as IE would ignore the whitespaces. However, other browsers are more specific. They will not ignore the whitespaces.

If you use the same simple code, you will not get the element that you wanted. You would have to find a way to ignore the whitespaces like IE.

The following is a simple code to do it.

function getcell(tbdid,params){

var z;

var zz;

addEval=tbdid

for (y=0;y<params.length;y++){

zz=-1

for (z=0;z<params[y];z++){

if (addEval.childNodes[z].nodeType != 1)

zz +=1

zz+=1

}

addEval=addEval.childNodes[zz]

}

return addEval

}

mycell=getcell(tblid,[x,w])

Note that x and w are numbers starting with 1 instead of 0.

Close top window automatically without prompt

In IE6 I used to do this

window.opener="x"

window.close()

The window will close nicely without prompting.

In IE7 it will not work. Instead use the following

window.open('','_self','');

window.close()

SetTimeout to temporarily disable buttons

Normally you would call the function and pass a callback function name to it so that at the time out event your function will be called.

Now what if I want to use the same call back function to act on different DOM elements? For example, I want to disable the button for a few seconds when I click on it. The disabling is to prevent user from clicking on it multiple times. The problem is that there are a few buttons of similar nature that acts differently on clicking. Instead of having to create different callback functions for each button, I want to use the same function for all the buttons.

It turned out that this could be done.

In your INPUT create a "onclick" event by calling a function and pass "this" as parameter.

In the called function do the following

myattribute=document.createAttribute("disabled")

myattribute.nodeValue=true

param.setAttributeNode(myattribute)

settimeout(function(){mycallback(param)},1000)

The first three lines will disable the button. The fourth line will call the timeout with mycallback as function. It is actually a dynamic function that calls mycallback function by passing the INPUT object as parameter. This means that the script still creates different functions for each button but it will not be created till user clicks on it. Coding wise this is easier to maintain than you create all the functions manually.

mycallback will then be able to act on the "param" (which is the "INPUT" object) by calling

param.removeAttribute("disabled")

Get date only from ODBC datetime in PHP

I always have problem with retrieving datetime field from ODBC. PHP will default to display it as "yyyy-mm-dd hh:mm:ss" format. The data is also converted to text. That means that you would not be able to do date calculation with it.

Normally I would use strtotime() to convert the date to timestamp first then perform the date manipulation. There are times when the date part of the datetime field is required. In which case I will have to include in the sql "convert(varchar,mydatefield,120)". Problem is MSSQL format 120 is "yyyy-mm-dd hh:mm:ss". There is no format that retrieves only "yyyy-mm-dd".

Fortunately the "convert" function allows truncation. So instead of using "convert(varchar,mydatefield,120)". I would use "convert(varchar(10),mydatefield,120)". The output will be a nice date only result.

If you do $date1-$date2, you will probably get a result like 1814400 because the returned result is in milliseconds. you need to divide the result by 60*60*24 to get days only result.

Excel connect to database

One of my project requires user to use Excel to fill in a form. Once it is filled, it will send the data to a database and returns a ID. Although it is not ideal for such a connection to be done in Excel, It is acceptable if the action is just to insert records. I set the access rights to just able to read and insert. The data is not sensitive and confidential.

Here is how I wrote the macro. Note that the getting and returning of ID part is not included here so as not to confuse reader.

sub updatedb()

Dim MyConn As ADODB.Connection

Dim MyRS As ADODB.Recordset

Set MyConn = New ADODB.Connection

MyConn.Open "DSN=odbcdsn;Uid=userid;Pwd=userpass"

SQL="insert int mytable (a,b) values ('" & ActiveSheet.Range("E1").Value & "','" & ActiveSheet.Range("F1").Value & "')"

Set MyRS = MyConn.Execute(Sql)

Set MyRS = Nothing

Set MyConn = Nothing

End Sub

Brio Working hour calculation

Brio query does not even have day of week not to mention networkingdays. The following is my own code to do the calculation. The calculation actually calculate the working hours between the two date excluding the off office hours and week ends. Unfortunately it is too much work to do a holiday exclusion.

Note that date5 is the earlier date and date6 is the latter date.

var date5 = CreateDate

var date6 = CreateDate2

var date1 = ToDate(ToChar(date5,"dd/mm/yyyy"));

var date2 = Nvl(date6,Sysdate());

var date3 = ToDate(ToChar(date2,"dd/mm/yyyy"));

var datecnt = date3-date1;

if (datecnt > 0){

var date4=date1

var offset=0

if (datecnt > 1 ){

for (x=1;x<datecnt;x++){

date4=date4+1

var nmonday=NextDay(date4,"Sunday");

var wkday = 7-(nmonday-date4);

if (wkday == 6) {

offset = offset + 0.25

} else {

if (wkday !=0) {offset = offset + 0.5}

}

}

}

var nmonday=NextDay(date1,"Sunday");

var wkday = 7-(nmonday-date1);

dateb=date5-date1

if (dateb < 9/24) {date5 = date1 +9/24}

if (dateb > 21/24) {date5 = date1 + 21/24}

if (dateb > 15/24 && wkday ==6) {date5 = date1 + 15/24}

if (wkday == 0){ datea = 0 } else {

if (wkday == 6) {datea=(date1+15/24)-date5} else {datea =(date1+21/24)-date5}

}

offset = offset+datea

var dateb = date6-date3

if (dateb < 9/24) {dateb = 9}

if (dateb > 21/24) {dateb = 21}

dateb= dateb-9/24;

(offset+dateb)*24*60

} else {(date6-date5)*24*60}

Change Oracle Password

Just a note to remind myself how to change the password for GCSS.

Goto "Start", "Run"

type

sqlplus username/password@tnsnameconnection

type

password

enter old password

enter new password an confirm new password.

A message will appear on success.

CTRL-C to exit.

Junk Mail Proof Letter Box and SingPost

Regarding the Junk Mail Proof Letter Box and SingPost. Town Council simply ask me to opt out (according to their reply to me). They are not going to stop SingPost.

According to ST reporter Irene Tham in her article "Locked out of Competition?", IDA replies "it is not an area that the IDA policies."

SingPost has a reply in Stomp. They are obligated to deliver "addressed or unaddressed."

So what is the result? No where to turn to.

Well in that case we should be obligated to return it via the "return mail" letter box as the junk mail is "wrongly addressed". Don't throw it away as it is deemed by SingPost as legitimate. If you throw it away you are unethical. You are throwing away an admail which is "not addressed to you" and which SingPost deemed it as legitimate "mail".

Since the admail is not addressed to you, it should be a wrong delivery as it is not addressed to you. You are, therefore, obligated to return the wrongly delivered mail to the sender via SingPost. In this way, you keep the lobby area of your HDB Block clean and did a good civil service. SingPost can't complain that you throw junks into the "return letter" letter box otherwise they slap themselves.

SingPost and Junk Proof Letter Box

It used to be quite disgusting when your fellow residents simply drop the junk mail on to the floor in front of the Letter Box. Like myself, they are also so sick of the countless daily dosage of the junk mail. I still remember the first day when I moved in and checked the letter box. It was jam packed with junk.

The installation of "junk mail proof letter box" seemed to be a timely relief.

Actually the new letter box come as a surprise. There was no letter informing us of the change. One day you walked to the letter box and find empty and void at the place where it used to be. Then you notice some stickers with arrow pointing you to the new location.

The newly moved letter box looks totally shameful. It was dusty and dirty all over. Dust was even found inside the letter box. How the dirt gets into the letter box is anybody's guess. Since my letter box was actually at the side of the letter box set, I think the worker actually puts his hand into my letter box as a grip support.

It goes on for a few week till there is a letter informing us to collect the new letter box. I was quite glad when the keys was collected. It signals the new era of "No Junk Mail".

When I get the first ever junk mail from the letter box. I was quite surprised. How did the junk mail get into the box. The lid on my letter box is openable but is latched and can only be unlatched from the inside after you opened the letter box with your key.

On the next day, I happened to go downstairs and a post delivery man was putting in mails. I realize that he actually put in a junk mail into the letter boxes. I also noticed that there are quite a lot of fliers on his bike when he moved the bicycle pass me. Now, how did this happen? Then I realize that SingPost was privatized. This is, perhaps, another way they could earn money.

Since my new letter box is junk mail proof type, I would think that it is designed to prevent junk mails from getting in. Even the mail delivery person has no rights to put in junk mails right?

I wrote in to SingPost to check out. Their reply is candid. They simply say that they do put in "admail" and even indicate to me the schedule of what junk mail they put in.

Stop there! Why are they doing it? Is it not my letter box is now "junk mail proof"? On what authority can they put in "junk mail" while all others are now not able? If they can put in one, what is stopping them from not putting a whole bundle of it. In which case, what then is the "junk mail proof letter box" is for huh?

Does the Town Council install the "junk mail proof letter box" to benefit us or SingPost? Surely the former is the case. If SingPost can bypass that purpose and enjoy the monopoly of it then I may ask again "who benefits from the junk mail proof letter box"?

I am writing to MP, Town Council, forum, blogs, facebook etc. to highlight this strange behavior of SingPost. Hopefully more people can query the various authorities of this oddity.

Enter Foreign Language for some applications

Modern program usually allows Unicode displays and input. However, many programmer still does not cater to Il8n (Internationalization). How then could I input and read Simplified Chinese instead of English in these programs.

Microsoft did provide some help however limited it is. You could set one language as the "system locale". According to Microsoft help "the system locale is the language that is used to display menus and dialog boxes for programs that do not use Unicode."

Setting it is simple.

1. Go to "Control Panel".
2. Click to open "Regional and Language Options".
3. Click "Administrative" tab.
4. Under "Language for non-unicode programs 'system locale'", click "change system locale".
5. Choose the language of your choice and click "ok".

The above applies to Vista only. Also, you would have installed the Simplified Chinese IME. If you can't get it from Microsoft. Go to Google and search for "google IME". Unfortunately, there are only two IME available. The other one is Japanese IME.

I am not sure if Google selectively show IME available for the region. I sure hope so.

Testing Ajax

I normally create Ajax that calls a PHP which generates the XML code.

If the PHP page generates the XML nicely, Ajax will be able to process the data accordingly. However, if there is a PHP error, Ajax will not return the correct result.

Testing the called page is a headache if you use IE.

For example, if the page has a missing parameter

IE will show "The following tags were not closed:yourxmltag....". If you try to "view source", IE will tell you "The xml source file is unavailable for viewing".

Firefox will at least tell you that "SQL failed". At lease Firefox shows the php error when "view source".

SSIS Editing

When I start to use SQL SERVER 2005, I have no idea how to change the legacy DTS packages migrated from the old version of SQL Server to a SSIS package. It took me a long time to find myself around how to edit SSIS before I manage to create one SSIS package. In the process, I actually have to install Visual Studio (visual basic).

I changed a new laptop recently. To my surprise, the visual studio express series is available but I don't seems to be able to download it. There is no link to download it at all even though the Microsoft MSDN download shows the page.

Searching the internet actually turns out the fact that SQL Server itself has the tool to edit SSIS without installing Visual Studio externally. The installation is in the install disk 2 (tools). It is under Client Components, Business Intelligence Development Studio. When you select this option, the installation will actually install Visual Studio and with it the BDIS.

IF ELSE using Ternary condition.

I used to code simple if/else condition codes for simple operations. For example,

if ($x > $y)

$mycolor="red";

else

$mycolor="green";

echo "$myvalue";

It is just a if/else condition.

I have just discovered that there is a simpler method to do so. This is by the Ternary conditioning ?: code.

The example can be made simpler as follows

$mycolor= ($x > $y") ? "red" : "green";

echo "$myvalue";

It sure makes thing easier and less coding.

Dollar Sign function in JS

I have seen function like $("myid") in a number of javascript libraries. It worked fine for most browsers. JQuery, Prototype etc all uses this type of function name or name prefixed with $ sign. By itself it is no big deal. However, you cannot mix different libraries that uses functions that has $ signs or function names that prefixed with $. Inevitably, you may come across with functions with the same names across different libraries.

The following is a basic example of the use of $ function.

function $(idname){

return document.getElementById(idname)

}

var mytext=$("myid").innerHTML

Many Javascript Libraries have much more elaborate functionalities using this type function name.

Running a SSIS package from the web.

I have some data to import into SQL Server so I done it manually in SQL Server Management and save the process as a SSIS package. It was save as "SQL server".

It took me quite sometime to find out where is it stored. It was found in MSDB database. I actually have to start SQL Management under the "Integration" service to see that.

The bad news is that I can't edit it at all.

Ok, then I save it as file. and I am able to edit it with SQL Management "file open" method. And I actually noticed that I cannot create new SSIS package. I must have missed some steps in the "new Project" creation process.

Back to the original issue. Now that I have it created, I want to run it in an ad hoc way rather than schedule it. I then realize that I could not do it in Stored Procedure. It require XP_CMDSHELL to be enabled. According to internet, this is not a good security practice.

Other netizens recommended to create a SQL job to run the SSIS but don't schedule it. A Stored Procedure is created to run the job by using "sp_run_job 'myjob'".

I then create a php page to run the stored procedure by its name.

In this way, I could actually execute the SSIS package in an ad hoc way.

My question is - why have to make it so complicated to run a ssis package?

Javascript associative array testing

Try the following to see if it worked.

myary=new Array();

myary["xx"]=1

if ( myary == "")

alert("myary is null/undefined/nothing")

if (typeof myary =="object")

alert("is obj")

It returns two alerts. But it is not correct as myary did have a value.

change myary["xx"]=1 to myary[0]=1

You will get only the second alert.

try the original script but this time change if(myary == "") to if(myary === "")

again only the second alert is shown.

Checking an array to see if it is empty is kind of awkward. It is not null or "undefined" as you already defined it as array.

Depending on the type of array, the results may be different between using "== " and "===" to compare with "" (empty).

1. If there is no value defined, "==" returns "true" whereas "===" return "false" and the length returned is 0.

2. If there is a valued defined (normal array), both test shows "false". The length returned one or greater.

3. if there is a value defined for associative array, "==" returns "true" whereas "==="return" false. The length returned is always 0.

As you can see, "===" always return false regardless of whether the array has a value. Using this to test whether the array is empty will not be fruitful.

On the other hand "==" actually returns "true" for associative arrays if it has a value. But then if the array has no value, "==" also returns "true".

The only sure way that I know is to use the following method to test

mytruefalse=false

for(x in myary){

mytruefalse=true

break

}

Fixed menu again

Although the fixed menu worked fine with IE7, Firefox and chrome, when IE 6 is used, the same age old problem of SELECT showing over DIV still exists when you scroll the window.

I have just noticed that when the web page is shown with the SELECT positioned just behind the DIV, it actually works. But when you scroll the SELECT again go on top of the DIV.

I have a stupid but working solution to this. Just do the following.

Create a javascript right at the end of the page which will only appears if the browser is IE 6 and below using the

<!--[if lte IE 6]>... <![endif]--> syntax to ensure that the script will not be used for IE7 and above.

Add the following in the script

mytime =setInterval("goforit()",100)

function goforit() {

hf=document.getElementById("hiframe")

hf.style.display="none";

hf.style.display=""

Finally add a IFRAME between the two DIV. with style similar to the second DIV. The SRC should be <html></html> but replace the greater than and lesser than with the escape code for the two character. The height and width should be same as the second div. Give it and ID (hiframe in the script example).

It will be good to hide the IFRAME if it is not IE 6 and below or other browsers.

Fixed menu

Previously I have not found any fixed menu or floating menu using just pure CSS. I just come across this link http://ryanfait.com/resources/fixed-positioning-in-internet-explorer/ that actually makes a fixed menu work even for IE6.

Dynamic floating DIV

I have been having trouble with floating DIV previously. It does not have any problem when running. However, in IE it always appear behind INPUT's. No matter how much I tried, it just don't go away. After searching the web, there seems to be no solution - at least for IE6 and below.

Besides this issue, the CSS for "position" also caused a lot of headache. "Position:fixed" does not work for IE7 (not sure about IE8 and above). "Position:absolute" has to be used. The problem is that this CSS does not make the DIV stay at the defined position.

I have read from post by others that there is only one choice for IE. Use "Position:absolute" and then add a javascript "window.onscroll" event and set the top and left style property of the DIV element using the scrollTop and scrollLeft offsets respectively.

It is not the best solution for IE as when you scroll the DIV actually jumps a bit. It is still better than nothing right?

Positioning the div itself on the top of the screen is no big issue. What if you want to position the DIV somewhere at the bottom of the page? Surely you have to take into consideration of the height of the DIV to ensure that it does not goes beyond the display.

It will be much more difficult if your DIV changes its size dynamically. Fortunately there is a element.offsetHeight/offsetWidth to help. It is the actual height/width of the element even if you set the style.height to auto.

One more thing. If you want to set the DIV to be at a fixed percentage of the window size instead of a fixed pixle value, you have a problem if your DIV is near the bottom of the window. When you resize the window, the div may shifts accordingly and get hidden or shifts up too high.

Therefore, you will need to offset the percentage with the offsetHeight of the DIV. To do that, you will need to know the height of the window.

The problem is that the height of the window can mean different thing in different browsers. This is especially true for different IE versions and whether it is set to quirk mode.

A easy way out is to do this.


var root = (document.compatMode == "CSS1Compat"?
document.documentElement: document.body);
var myHeight = 0;
if( typeof( window.innerWidth ) == 'number' ) {
myHeight = window.innerHeight;
} else
myHeight=root.clientHeight

Doing offset is then easy if the height of the window is known. First find out what is the pixel by multiplying the window size with the percent. If the pixle value plus the offsetHeight of the DIV is greater than the window size, then just set it to the window size minus the offsetHeight so that the DIV can be shown all the time.

PHP PDO

I used the "prepare" statement in the PDO. It worked fine if I have all the variables defined. However, if there is a interger variable not defined. It does not complaint. It just don't insert record. I am not sure about a string variable. Maybe PHP will actually insert a "" instead.

Dynamic add style to element

Previously I have problem setting the styles of elements from Javascript. I end up changing the className of the element to achieve the desired effect. Recently, I saw from some one's blog saying that using the following method can actually change element style dynamically.

document.getElementById("myid").style.cssText="font-size:20px;font-weight:bold;"

This method works for both IE (tested in IE6,IE7) and Firefox (ver 3.6).

Note that you actually replaces the old style with your new style using this method.

To add style to existing styles do this

document.getElementById("myid").style.cssText +="color:red"

I noticed that IE7 does not add new style element to the existing style if you don't prepend a ";"

Note that it still replaces the old style if the added element already exists.

Dynamic add elements with a twist

I used to create tables and add rows and columns dynamically to it using javascript. At other times, I also need to remove all elements from a select tag and add a custom list of options to it.

It is quite tedious to add elements manually. Thus, I thought why not add it using functions.

On trying, I noticed that basically I need a element id to add the elements for both types of requirement. Then, it comes to my mind to try new ways of doing it using classes. Furthermore, I would not want to repeat the part where I get the id of the element.

Thus, I created a "class" which has a nested class in it so that I could use syntax like

addElements("id").addtd(myarray)

Well, the syntax looks funny but it actually works. I use "closures" to achieve this. The code is as belows.

/*

General Syntax - AddElements("yourid").functionname(params)

params :-

1. array - a list of items defined by an array e.g. ["a","b","c","d","e"]

2. element,assocarray - and element and an assocarray e.g "span",{'class':'myclass','id':'myid'}

3. text - any text appropriate for the docid property involved

For the assocarray, it is a good practice to use single quotes even for the keys. If no property required then use "" instead.

Available functions:-

addtd(array) - add a table row pair ("TR") with TD populated by the items in the array

replOpt(array) - remove all the <select> options and populate the options from the array.

addE(element,assocarray) - add the element and set the property populated by the items in assocarray. See params 2 above

addStyle(text) -adds a style to the docid itself. e.g. "font-weight:bold;font-size:30px"

replStyle(text) -replaces a style to the docid itself. e.g. "color:red"

addText(text) - similar to addStyle but add the text into the textnode of the element involved.

replText(text) - similar to addText but replaces the content of the textnode of the element

*/

var addElements = function(docref) {

tbdid=document.getElementById(docref)

var addDomE = {

addtd: function(nodearray){

trid=document.createElement("tr");

for (ndtxt=0;ndtxt<>

tdid=document.createElement("td")

txtid=document.createTextNode(nodearray[ndtxt])

tdid.appendChild(txtid)

trid.appendChild(tdid)

tbdid.appendChild(trid)

}

},

replOpt: function(nodearray){

for (ndtxt=tbdid.length-1;ndtxt>-1;ndtxt--){

tbdid.remove(ndtxt)

}

selnid=document.createElement("option")

selnid.value=""

selnid.text="Please select"

selnid.selected=true

if(navigator.appName =="Microsoft Internet Explorer")

tbdid.add(selnid)

else

tbdid.appendChild(selnid)

for (ndtxt=0;ndtxt<>

selnid=document.createElement("option")

selnid.text=nodearray[ndtxt]

selnid.value=nodearray[ndtxt]

if(navigator.appName =="Microsoft Internet Explorer")

tbdid.add(selnid)

else

tbdid.appendChild(selnid)

}

},

addE: function (htmltag,params){

myelement=document.createElement(htmltag)

if (params != ""){

for (addEval in params){

if (addEval!="html"){

myattribute=document.createAttribute(addEval)

myattribute.nodeValue=params[addEval]

myelement.setAttributeNode(myattribute)

} else {

mytxtnode=document.createTextNode(params[addEval])

myelement.appendChild(mytxtnode)

}

}

}

tbdid.appendChild(myelement)

},

addStyle: function(params){

params = ";"+params

tbdid.style.cssText +=params

},

replStyle: function(params){

tbdid.style.cssText = params

},

addText: function(params){

mytxtnode=document.createTextNode(params)

tbdid.appendChild(mytxtnode)

},

replText: function(params){

mytxtnode=document.createTextNode(params)

mynodes=tbdid.childNodes

while (tbdid.hasChildNodes()){

tbdid.removeChild(tbdid.lastChild)

}

tbdid.appendChild(mytxtnode)

}

}

return addDomE;

}

;

In the HTML side you have to first setup one tbody element (not table element) with id to add td elements . For select element, you just use the select id.

Next , you create an array of td element or option element. Finally just use one of the following to do the trick

addElements("myid").addtd(myary);

addElements("selid").replOpt(myary);

addtd is for adding td elements. replOpt is for options element.

Zen Coding

Found a nice feature packed addon to Komodo. The name is "Zen Coding". It allows for short cuts in HTML editing. It is a real help when you are tired of typing the code like <script type="text/javascript"></script> .

Using the Zen Coding, I just type "script" and hit the short cut key for the commands and it expands nicely.

There are hundreds of short cuts available in "Zen Coding". Probably I will only use a few of it but it is still worth it as my laziness makes typing some of the codes a real pain in the past.

Give up with odbc_prepare

Previously I wrote about odbc_prepare's problem on sending wildcards "%" to "char" type field. I could solve that without problem.

I then faced another buggy issue. This time it involved alias. It seems like php randomly choose to pop up warning "column not found" with some of the alias but not all. This is totally not cool.

In the end, I give up on odbc. I switched to PDO.

There is another issue awaiting me. This is the SQL "in" operator. I have not figure out how to send "select * where myfield in ('a','b','c'). It is not a problem if I code the sql statement with every value in the list as a variable. However, this is hard work as the list may be very long.

Obviously, in PHP you could get a list of keys and values from arrays but is there no easier way to do this in PDO?

odbc_prepare with parameters

Recently I was trying to use the odbc_prepare with parameters. It worked just fine till I used wildcards in the parameter. Some how it always return "No rows found".

The query goes like this

$pquery=odbc_prepare($conn,"select * from mytable where mytitile like ?");

$result=odbc_execute($pquery,array("title%"));

Interestingly, if the field size is small, let says 4, I could use "xx%%" and the query produce results. If I just use "xx%" it returned "No rows found".

Searching the internet, I could not find informations that pertain to the exact problem. However, while doing a search for "mssql parameter wildcard", I came across a microsoft news group article about wildcards in field type "char" and "varchar".

It looked like mssql responds differently to wildcards in "char" type fields and "varchar" type fields when binding parameters.

"%" will work just like "_" in "char" type fields whereas "varchar" type fields will treat them differently.

The solution to "char" type fields wildcard searching using "%" is simple. Just use "cast(expression as Varchar)". So your prepare statement will look like

$pquery=odbc_prepare($conn,"select * from mytable where cast(mytitile as varchar) like ?");

If you have control of the table, I should suggest you changing the field to varchar instead.

Ajax XMLHttpRequest object in IE

It is quite hateful using XMLHttpRequest across browsers. This is especially severe in the case of IE.

In IE7 and above or other browsers you have to use the standard

obj = new XMLHttpRequest();

In IE6 or below, depending on versions of MSXML installed, you will have to use

obj = new ActiveXObject("Msxml2.XMLHTTP");

or

obj = new ActiveXObject("Microsoft.XMLHTTP");

Dynamic add option to Select Element

//first clear the content

obj=document.getElementByID("mysel")

mysize=obj.length

for (x=mysize-1;x>-1;x--){

obj.remove(x)

}

myobj=document.createElement("option")

myobj.text=myary[x]

myobj.value=myary[x]

myobj.selected =true

if (navigator.appName=="Microsoft Internet Explorer")

obj.add(myobj)

else

obj.appendChild(myobj)

Funny behavior of my IE

When I run a php script that contains a javascript script that sorts a table, it worked fine for quite sometime till I added a `part description` field. Now it seems like certain data cannot be shown without the `IE timeout due to script take longer than usual` error. After I accepted the termination of the script, the sorting script no longer runs.

Funny thing is that this issue only appears on my IE. I tried on at leat 3 other IE on other machines and they all shows the table without error. What in the world happened?

Since it is system generated error and that I know it was caused by the data in `part description` field, I decided not to `repair` my IE but concentrate on making the field workable with the javascript.

Well, it is quite simple. I simply remove any character that is not allowed by me using preg_replace command. It cut short all the necessity to replace any character that caused the javascript error. The regexp code is [^a-zA-Z0-9\\-\\.,\\/_ &:;] with a `si` parameter.

Obviously, I did not solve my IE problem. But who cares when it does not complain anymore.

Join table from two source but same server

Previously I discussed about using OPENROWSET. If the database is from the server, there is an easier way of joining the tables. Below is the example.

select top 1 case_id,cso_no from otherdb.dbo.tbl_case

inner join

delivery.dbo.cdrecord on case_id =cso_no

Notice that the database name was included with the owner `dbo` indicated also. However, use this method sparingly. It is not efficient to perform queries over different databases. Moreover, you have to make sure that the user rights are exactly the same unless you are, of course, dbo.

Self Join

Self join is an interesting subject. Why join to the same table? There are situations where you need to get some information from the same table but not from the same row of record. The following may help you to understand.

In a company, I have a employee table listing the employee information. In the employee table I list the employee information plus the manager\'s id ( to save space). Now I want to list the employee information but want to know the manager\'s name instead of id.

Since the manager is also an employee listed in the table, the only way to get the information is via self joins. The following is the sql.

select a.employee_name as employee,b.employee_name as manager from tbl_employee as a inner join tbl_employee as b on a.manager_id = b.employee_id

Here you can see that it is no differen from any other joins except that the same table is used.

Wildcards in SQL Server

Wildcards are useful when searching for items using LIKE clause. MSSQL allows a few wildcard specifier. The most commonly used is the % character. There are some other wildcards available in MSSQL. The underscore character `_` is used for a single character wildcard. the `[]` wildcard indicates that any single character set or range specified in it will be a wild card. If the first character is ^ in the `[]` set or range, it is equivalent to single character `not`matching.

Now we do have literal characters that uses the wildchard character. How do we defile the literal characters in the search? it is quite simple. Simply enclose the character between `[]`. That will include the `[` and `]` also. Another character is the single quote. Well, simply add another single quote to it. Finally there is an ESCAPE clause where you could define a escape character. Normally the escape character is a backslash. just add ESCAPE `\' to the end of the where clause.

In Oracle, it has quite similar syntax except that it does not have `[]`. It does uses ESCAPE clause exactly like MSSQL. I think we should use the ESCAPE clause as standard except under special circumstances then we use the `[]` wildcard.

Recursive search

In MSSQL 2005 there is a recursive search facility. The clause used is [with]. Example is below.

with PartSearch (Part_number,Replacement_part, level) as {

select Part_number,Replacement_part, 0 as level from wfm_parts p

union all

select p.Part_number, p.Replacement_part, ph.level+1 from wfm_parts p

inner join PartSearch ph on ph.part_number = p.Replacement_part }

select Part_number,Replacement_part, level from PartSearch where level > 0

Obviously, you have to use stored_procedures to do it.

PartSearch is a pseudo name created. It can be any name. The above is not tested as I do not have a good database sample to run the example. The syntax should be correct.

Output Clause in MSSQL 2005

In MSSQL when you insert or delete a record, you don`t get a returned recordset. It is always a headache to first determine what is the record that was deleted or inserted when there is a auto increment index present.

Starting fom MSSQL 2005 there is an interesting command which resembles MYSQL mysql_insert_id() function. It has more functionality than MYSQL. It can be used in INSERT, DELETE, UPDATE clause to get the id involved.

For example

insert into tbl_test_insert (case_id) output INSERTED.ID, INSERTED.case_id values(`123456789`)

The query is the same as any INSERT query except there is some extra clause inserted in between. The clause is OUTPUT INSERTED.ID, INSERTED.case_id. This clause tells the query to output values defined by INSERTED. The value is actually the record or the data that was inserted/deleted into the table.

It comes very handy if you have to insert/delete a main table and then insert into a sub table that has the same id as the main record. The id in the main record is auto increment identity column so that it could be an index. You will not know what is the value till the record is inserted. By inserting/deleting a record in the main table you will need to get the id value so that you could perform insert/delete on the sub table.

I used to insert a UUID when inserting the main table then query the main table to get the id by finding the UUID. Now I can just use the insert statement then simply do a odbc_fetch_row() to get the id with just one query. Neat.

Just to let you know this- use DELETED.xx if you are doing delete query. For UPDATE you use INSERTED for those fields you updated and use DELETED for those fields you replaced.

SQL FIRST aggregate function

As you all knows very well that the FIRST aggregate function does not exists in sql server.

There are actually tweaks to make it work. Heres how.

select case_id, RowNumber from (select case_id, ROW_NUMBER() over (partition by case_id order by subcase_id) as RowNumber from tbl_case) as tblcase where RowNumber=1

The above sql actually returns the first subcase information since i use ORDER BY SUBCASE_ID in PARTITION BY.

This is one of those odd cases where you do a select from a derived table.

This query is particularly useful when you happen to have duplicate subcase_id. It will return unique rows.

Obviously, if you just do like the example above, it is kind of going the long way. A simple DISTINCT and a WHERE clause can do much better job than that. But if you have duplicate subcase_id then your DISTINCT,WHERE method may not work as per expected. Moreover, you could make use of the derived table to do MAX and MIN aggregate function on other fields across all subcases.

PARTITION BY works similar to BRIOQUERY SUM BY method. It does not require a GROUP BY which is somtimes very obstructing to the AGGREGATE function. For example you want to do an aggregate but also want to include the case_title field. That is impossible for normal query with GROUP BY since case_title is almost unique to each case. After getting the aggregated results then you use the above SELECT FROM DERIVED TABLE method to do further filtering to get only the results you want.

SQL SERVER AGGREGTE OVER CLAUSE

Just discovered a new way of doing max and min on different date like brioquery (sum by) syntax.

select distinct case_id, max(close_date) over (partition by case_id) as maxdate, min(open_date) over (partition by case_id) as mindate from wfm_subcase

Previously I had to use a derived table to get each of the two dates. Now it can be done in a single clean sql statement.

There is a catch if you uses sql server 2005. You will get error message if you use query designer to edit the query. Since the query designer mode is always the default mode when you create a view, just create a normal query without the over clause then save the view. Next, open the view in edit mode (not design mode) and change the query to a over clause query.

Find duplicate cases in SQL sever

Many a times we want to check if there are duplicates in a table.

select case_id, count(case_id) as casecnt from wfm_case group by case_id having (count(case_id) >1)

Derived Table

`Derived table` in mssql is equivalent to `inline view` in Oracle or `subselect` in Postgres. Its purpose is to let us use a `select` query as a source with an alias. Example

Select * from (select case_id,open_date from tbl_data) AS S

It looked stupid to have such a query which does not help in anything. However, its very much more useful than just that.

For example. You wish to get the last close date from a subcase while retrieving a main case data. This could only be achieved if you create a view to get the last close date or create a temporary table to get the last close date. Once you can get the data from the view or temp table, you join it to main case to get the final result.

Creating temp table means you have to do a number of steps to achieve it and have to remember to remove the temp table. Creating a view is easier as it will not need maintenance after that. However, you have to do one query in two places and you cannot create the query on the run. Is it at all possible to use just one query to get the desired results?

`Derived table` is the answer. The following is a working example from wfm (our server) database.

SELECT M.Case_id, S.close_date
FROM dbo.T_CASE AS M LEFT OUTER JOIN
(SELECT case_acc_id, MAX(close_date) AS close_date
FROM dbo.T_SUBCASE
GROUP BY case_acc_id) AS S ON M.Case_id = S.case_acc_id