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.

SVG calendar control

I have just uploaded the above into http://www.hiew.per.sg. File name is calendar.svg There are a number of input controls written in activex, java, javascript etc. This control is written in SVG. It does exactly as what the name provides. The control will replace a Input field with the name "svgdate" with the selected date. You can always modify it to work with different date format or even the field name of the parent document.


Alternatively you can insert definition "top.myfunction = mysvgfunction" into the svg script and create a function called mysvgfunction in svg to provide the date value then call myfunction from the parent script to get the date value. Obviously you must store the date variable as a svg global value.

Below is a simple web page to store the date string from the svg when user selects a date.

<html>
<head>
</head>
<body>
<form action="">
<input name="svgdate" type="text" value="" size="30">
<EMBED name="emap" pluginspage="http://www.adobe.com/svg/viewer/install/" src="calendar.svg" width="140" height="140" type=image/svg+xml>
</body>
</html>

Insert Multiple record in one sql statement

There are instances that one needs to insert multiple records into a database. This is normally for those people having a group of items to be insterted.

Normal SQL statement does a single record insert like below

INSERT INTO mytable (Field1,Field2...) VALUES (value1, value2...)

This is good for a single record insert. What happens is that a person may select a group of items like shopping cart. In the shopping cart you have the item code, quantity and perhaps price as one record. But if user selects more than one item then it caused a database update issue. Usually the server side will use a grouping of the item in a list and do a loop to insert the records one by one.

The above method worked fine if you only have a small list of item to update. In the commercial world, the requirement can end up with thousands of items each with several fields to update. To do update using the above method most probably caused timeout problem on the server side. It has to loop thousands of insert statement within one submission.

The under mentioned method is not my own idea but is copied from the internet while searching for solutions. I would like to give credit to the person/s but has lost the information. Anyway, thanks to the availablilty of this information, it solved my problems.

The multiple record insert SQL statement runs like below

INSERT INTO mytable (Field1, Field2,...)
SELECT valuea1,valuea2,....
UNION ALL
SELECT valueb1,valueb2,...
UNION ALL
SELECT valuec1,valuec2,...


I have tested the SQL statement with MS SQL SERVER and it worked. Not sure if other database server can also work the same way.

This method does has its limitation though. Obviously string variables will ultimately has a length limit. Moreover, to parse huge lengths of strings may cause resource problems. After all, the manupulations are done in the server itself.