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]+$/