Insert record where not exist

If we want to insert record but do not want to make duplicates there are a number of ways to do it. The following scenerio is that user want to insert a record from a form and do not want to insert duplicates base on case id.

Insert into tbl_case (caseid, createdatetime) select '123456', '4/3/2010 12:00:00' from tbl_case where not exists (select caseid from tbl_case where caseid = '123456')

The trick part is the first select. It is actually not getting anything from the database. It won't work if we do not refer to the table this way.

The second select is just standard syntax using the "where not exists" clause.

PHP page as sort of IFrame

Sometimes when your page needs a menu across the top of the page and a number of web pages that perform different function, a standard way is to set the main page as frames or use iframes.

I have a different way of doing the same thing using PHP. The main page itself contains the menu. The menu should be at the top of the page. When you click on it then you submit a dummy form with a variable defining the action. The php code then uses a "Switch" function to determine which web page to "include". When the page loads your web page will appear inside the main page as if it is iframe yet remains part of the main page.

Doing this has advantages.

1. Session information always stays with the main page.

2. Javascript does not need the parent child relationship. It can call functions both ways.

3. You need not define a target frame when submitting the form.

4. The sub pages need not have the full web page header. Just what ever that is need to show the content.

5. You can have the footer residing in the main page. That is somthing frames cannot do.

6. You are not bothered by the iframe size issue and there will not be any multiple scroll bars to uglify your page.

Disadvantages

1. The main page keeps reloading itself everytime you submits a form.

2. You must always have an input that defines the menu action in your form so that the same page will load when submitted. A way out is to define a session variable that defines the menu action.

Database in PHP

I am always troubled by the code needed to connect to database and send a query. Previously I created a class to return the whole result set of odbc_exec so that it could accommodate the "update" and "select" query. But it still bothers me a lot to do a loop (odbc_fetch_row) for "select" query. I have discovered that using a callback function, I could actually avoid defining the loop itself. Although this will limit the code to just "select" query, it does make the whole database access code become just two line (actually three if you put the query statement as a variable).

The following is the class.

class myclass {

/*

Use extract($db_j); in $callback to export the keys and values of the array as variable

*/

public function run_sql_cb($sql,$db_con,$db_usr,$db_pwd,$callback){

if ($db_obj)

$result=odbc_next_result($db_obj);

$conn = odbc_connect($db_con,$db_usr,$db_pwd);

if (!$conn){

exit("Connection failed: " . $conn);

}

$db_obj=odbc_exec($conn,$sql);

if (!$db_obj){

exit("Sql failed");

}

while ($db_j = odbc_fetch_array($db_obj)) {

$callback($db_j);

}

}

}

The sample callback routine is as below

function caller($myobj){

extract($myobj);

echo $Mycount." ".$CNT."
";

}

and the calling code

$mc= new myclass;

$sql="select Mycount,CNT from sample.csv";

$dbclass->run_sql_cb($sql,"tester","","","caller");

The class is a simple routine to connect to a database using the ODBC connect code. I added the callback code by adding a callback on the dataset loop. The callback is done by just adding the callback function name to the class function. The class function will in turn just add a () to the variable passed and it becomes a callback function.

In the dataset loop, I use odbc_fetch_array so that I could pull a recordset as an associative array. I passed this array to the callback function.

In the callback function, I used a extract() function. Extract() function will take the associative array and make the key as variable name and the key value as the variable value. Since it is in the function, the scope of the variable is limited to the function thus does not affect the main routine variables.

The rest of the callback function is then up to you to add whatever code to manipulate the recordset.

Javascript Pivot

Creating pivots in HTML like that of Excel is very much harder. It take a bit of understanding of Javascript to do it. The following is a lazy way of showing a pivot

Save the following as pivottable. js file.

pivotTable =function (){

//first include this class

// create a new class var xx=new pivotTable()

//pass a tbody id to tblID as xx.tbID="idtext"

//add rows by calling xx.insertRaw tblindex is row value, thlPivot is pivot col and tblPivotValue is the numeric value of the pivot col

//finally call showTable as xx.showTable()

this.pivotList="";

this.tblID="";

this.pivotArray=new Array();

this.indexArray=new Array();

this.mainArray=new Array();

this.totalArray=new Array();

}

pivotTable.prototype = {

insertRaw : function(tblIndex,tblPivot,tblPivotValue){

if (typeof this.indexArray[tblIndex] =="undefined")

this.indexArray[tblIndex]=tblIndex;

if (typeof this.pivotArray[tblPivot] =="undefined")

this.pivotArray[tblPivot]=tblPivot;

if (typeof this.mainArray[tblIndex] == "undefined")

this.mainArray[tblIndex]=new Array();

if (typeof this.mainArray[tblIndex][tblPivot] =="undefined")

this.mainArray[tblIndex][tblPivot]=tblPivotValue;

else

this.mainArray[tblIndex][tblPivot]=this.mainArray[tblIndex][tblPivot]+tblPivotValue;

if (typeof this.totalArray[tblPivot] =="undefined")

this.totalArray[tblPivot]=tblPivotValue;

else

this.totalArray[tblPivot]=this.totalArray[tblPivot]+tblPivotValue;

},

showTable : function(){

myid=document.getElementById(this.tblID)

mytr=document.createElement("tr");

myth=document.createElement("th");

mytext=document.createTextNode("\u00a0")

myth.appendChild(mytext)

mytr.appendChild(myth)

for(myindex in this.pivotArray){

myth=document.createElement("th");

mytext=document.createTextNode(myindex)

myth.appendChild(mytext)

mytr.appendChild(myth)

}

myid.appendChild(mytr);

for (myx in this.indexArray){

mytr=document.createElement("tr");

mytd=document.createElement("th")

mytext=document.createTextNode(myx)

mytd.appendChild(mytext)

mytr.appendChild(mytd)

for (myy in this.pivotArray){

mytd=document.createElement("td");

if (typeof this.mainArray[myx][myy] == "undefined")

mytext=document.createTextNode("0");

else

mytext=document.createTextNode(this.mainArray[myx][myy]);

mytd.appendChild(mytext);

mytr.appendChild(mytd);

}

myid.appendChild(mytr);

}

mytr=document.createElement("tr");

mytd=document.createElement("th")

mytext=document.createTextNode("Total")

mytd.appendChild(mytext)

mytr.appendChild(mytd)

for(myp in this.pivotArray){

mytd=document.createElement("td")

mytext=document.createTextNode(this.totalArray[myp])

mytd.appendChild(mytext)

mytr.appendChild(mytd)

}

myid.appendChild(mytr)

}

}

Include the pivottable.js and the following in your web page.

mypivot=new pivotTable();

mypivot.tblID="mydiv";

mypivot.insertRaw("firstrow","Acol",1)

mypivot.insertRaw("secondrow","Acol",1)

mypivot.insertRaw("secondrow","Bcol",1)

mypivot.insertRaw("secondrow","Ccol",1)

mypivot.insertRaw("thirdrow","Dcol",5)

mypivot.showTable();

Create a table with a tbody. The tbody id is "mydiv". You will see a 4x4 table displayed.

Javascript Class

I have been writing Javascript codes for a long time. Although there is this "class" thing in Javascript, I have never come close to need to use it. However, recent coding requirements forced me to look at it closer.

There is a web page that needs to mark a check box list upon selecting a product from a drop down list. Each product has a specific list of items that need to be checked. Normally I would retrieve all the products and their items and make them into an associative array. When user selects a product the items tagged with the product will then populate the checkbox accordingly.

However, the products are duplicated with different plans. This means that I have to have a multi-dimension arrays. Now, if the data is huge, then my web page loading will be slow. So I though why not use "Ajax" technology to retrieve only the particular checkbox list. As it goes, everything goes smoothly with a slight delay as the data need to be retrieved across the network.

When the next request to amend the web page with the ability to check for duplicate product entry, I was thinking of not submitting the form then check for duplicates as I need to fill the form with user's submitted content should duplicates occur. Thus, the best choice is to just check for duplicates using "Ajax". I am faced with having to double coding of the same "Ajax" script. The script is tailored to a single process and is not adaptive to another process.

Since the basic codes are generic and only certain parameters like URL and Callback functions need to be specific, I though why not create an "Ajax" class to create instances of the Ajax codes so that I could use a single code with different callbacks to do the job. After all, only the URL and callbacks are process specific.

Thus, I began the learning of the "class" in Javascript. As it turn out, Javascript don't actually have "class". It only have "objects". However, the object behaves quite similar to "class". The "class" constructor is actually an "object" constructor. Basically the "class" is a "function" object.

function mySpecie(){

}

You create and instance of the function by

myInstane = new mySpecie();

Well, normally you will never need to create instances of the function since I could call the function and just pass parameters to it. However, there is always a need to set certain parameters that does not change. For example, I need to store particulars of a group of species. Instead of having to define that they are humans one by one, I could have set globally that the species is "human" then I can concentrate on the particulars that are specific to the species. Now simple functions can't store parameters. It has to be passed as parameters every time.

There is a way to set certain paramaters as fixed values in the function. This is done by the keyword "this".

function mySpecie(){
this.specie="human"
this.name="Jon";
this.sex="male";
}

In this way, you don't have to set the name and sex every time you call the function. Nice huh! What if I need to add/change ad hoc information during the running of the program? A "Class" has this ability to do it right? Javascript has this thing called "prototype" in functions. You could add or change any values to the function at anytime and it will remember the values when you call the function. You can set the values by

mySpecie.prototype.specieType = "notHuman"

So whenever you call the instance of mySpecie, you get to have the setting of specieType as "notHuman" instead.

In other words, you could set and change the parameters as and when you like.

As it goes, "class" can have functions. Well, Javascript can also have function in functions.

function mySpecie(){
this.name="Jon"
this.hello = function(){
echo (this.name)
}
}
myInstanc=new mySpecie();

When you set "myInstance.hello()", you get the reply as "Jon".

I also learn that you can set multiple parameter values without having to keep defining "prototype".

mySpecie.prototype = {
name : "Jon",
hello : function (){
echo (this.name)
}
}

One other thing I learn is that I could create a function and pass this function as part of the class.

function hello{
echo "Hi";
}
myInstace.prototype.hello = hello

Actually I could easily pass the function as a function parameter into the instance on the run. All I need to do is to set the instance's value "var hello=hello".

myInstance("xml.php",hello);

With the above methods, I could pass the function into the instance and use it as part of the class.

It is certainly easy now to use multiple instances of "Ajax". I could create the class and then set the URL and callback accordingly and still use one set of code. All I need to do is pass the parameters to the instances of "Ajax" class.

Sudoku.svg

Just for the fun of it, I have created a svg version of the sodoku game. It has simple checking facility to tell user that they entered the wrong number.

The game is a simple number entering grid of 9x9. It composed of 9 sets of 3x3 grid. The rule is simple. Just enter a number from 1 to 9 into the 9x9 grid such that the number cannot repeat itself within the individual 3x3 grid or any rows and columns in the 9x9 grid.

In order to ensure that the puzzle can be completed. I had a complete a set of coded grid data that is stored in the svg array. Each time I load the program, the codes are randomly subsituted by a random number. For example if my grid data is "A" and the random number is "2" then whenever I need to show the clue, Everytime I encounter "A" in the array, I replace it with 2. If on next load the random number is 3 then all the "A" will be replaced by 3. By permutation, I should have thousands of sets available based on one set of array.

The clues are displayed randomly. A loop of 15 were set for the 9x9 grid. Due to the random nature, any overlapse of the random number means one less clue will be shown. Maybe I should set each 3x3 grid with 2 clues each.

By mistake, the game I created also take into consideration the diagonal values in the 9x9 grid. I set it such that the two longest diagonal numbers must not repeat also. This actually make the game very difficult to solve. I had to comment the diagonal check out to make a standard game.

This svg can be found at http://pachome1.pacific.net.sg/~jhck/sodoku.svg.

AJAX

I used to create web pages using a form which has a target in a hidden iframe. When the page is loaded. The data in the returned page is then parsed into the form using javascript. There is a problem with it as there is no way I can tell whether the data retrieval is available.

Recently I read about AJAX (Asynchronous JavaScript and XML). It seems to address the above problem. The following is a simple script that I used to get it working. It has three parts.

1. Javascript.
2. HTML form (omitted here)
3. PHP (server side script)

1. Javascript

Include the following two javascript functions in your calling page

//-------------------------------------------------------------------------------
function doget(query,query1){
Query = "mypage.php?department="+query+"&bft="+query1
try
req = new ActiveXObject("Msxml2.XMLHTTP.3.0");
catch (e)
try
req = new ActiveXObject("Microsoft.XMLHTTP");
catch (e)
try
req = new XMLHttpRequest()
catch(e)
req = false;


if (req){
req.onreadystatechange = processReqChange;
req.open("GET", query, true);
req.setRequestHeader('Content-Type','text/xml');
req.send();
}
else
alert("Cannot start XmlHttpRequest Object")
}

//-------------------------------------------------------------------------------

function processReqChange() {
// only if req shows "complete"
if (req.readyState == 4) {
// only if "OK"
if (req.status == 200) {
dbhtml="<table border=1 cellspacing=0><tr><th>Day</th><th>No of failure</th><th>No Calls</th><th>%</th></tr>"
response = req.responseXML.documentElement;
xdatalength=response.getElementsByTagName('day').length
for (x=0;x<=xdatalength-1;x++){
dateday = response.getElementsByTagName('day')[x].firstChild.data;
statcnt = response.getElementsByTagName('count')[x].firstChild.data;
statmiss = response.getElementsByTagName('miss')[x].firstChild.data;

dbhtml=dbhtml+"<tr><td>"+dateday+"</td><td>"+statmiss+"</td><td>"+statcn
t+"</td><td>"+Math.round((statmiss*1)/(statcnt*1)*100)+"</td></tr>"
}
// a DIV tag with a name "mydiv" must be defined for this to work properly
document.myform.mydiv.innerHTML=dbhtml+</table>
}
else
alert("There was a problem retrieving the XML data:\n" + req.statusText);
}

}
//-------------------------------------------------------------------------------

2. HTML

Just any html form that has a clickable item with has a "onclick" propert to initiate the "doget" function in the javascript. The "processReqChange" function is a callback function (automatically invoked by AJAX).

3. PHP

The php script is as follows (the database query section is omitted).
The first 4 lines must be at the top of the php page.

<?php
header('Content-Type: text/xml');
echo '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
echo "<response>";

/// your sql statement here using the passed parameters

while (odbc_fetch_row($rs))
{
$statcnt = odbc_result($rs,"statcnt");
$statmiss = odbc_result($rs,"statmiss");
$clsdate = odbc_result($rs,"cdate");
echo "<day>".$clsdate."</day>";
echo "<count>".$statcnt."</count>";
echo "<miss>".$statmiss."</miss>";
}
echo "</response>";
?>


Using ColdFusion instead of PHP is very simple... Just convert the php page to ColdFusion to return the same information. No need to change the calling page.

Note also that req.open in the above example is made using "GET"
method. If you need to send a lot of parameters (like submitting a form to be saved into database, then use "POST" method. The req.send function then must contain your parameters as a string. E.g.
req.send("department=xx&bft=yy")

Another thing to note is that if you are saving the submitted data then you don't have to use "req.responseXML.documentElement". Just use "alert(req.responseText)" to send a windows messge to user on the status of saving. Your PHP can forget about constructing the XML. Just "echo" some messge upon success or failure.

Note that the above is not synchronous but it is not multithreaded either. It only works on one particular way thus it cannot be used in multiple process to get data.