jqgrid - Enable / Disable custom button depends on a selected row

//Add a custom button to Grid, and give it an id, an example here is 'save1'

jQuery("#grid1").jqGrid('navGrid',"#pager1", {edit:false,add:false,del:false})
.navButtonAdd('#pager1',{
caption:"Save",
buttonicon:"ui-icon-disk",
position:"last",
id: "save1",
onClickButton: function(){
...
}
});

//Construct the onSelectRow event of the grid
jQuery("#grid1").jqGrid({
...
onSelectRow: function(rowid){
if (rowid==2) {
$("#save1").removeClass('ui-state-disabled'); //Enable
} else {
$("#save1").addClass('ui-state-disabled'); //Disable
}
},
...
});

jqgrid datatype:xml sql server driver, example of adding/amending footer/summary

*** javascript (prepare the grid) ***
jQuery("#grid1").jqGrid({
url:"server.php?q=1",
datatype:"xml",
colNames:[...'Amount'...],
colModel:[
...
{name:'amt',index:'amt', width:30, align:"right",sorttype:"float", formatter:"number"}
...
],
...
footerrow : true,
userDataOnFooter : true
...
});
//

*** server.php (construct the result set for the grid) ***

echo "<rows>";
...
$l_totalamt=0;
while ($row = sqlsrv_fetch_array($stmt)){
echo "<row id="".$row['id']."'>";
echo "<cell></cell>";
...
echo "<cell>".$row['amt']."</cell>";
$l_totalamt=$l_totalamt+$row['amt'];
...
echo "</row>";
}
echo "<userdata name="amt">".$l_totalamt."</userdata>"; //construct the footer
echo "</rows>";
//

*** javascript (amend the footer, 'amount total' in this example)***
...
var dataIds = $('#grid1').jqGrid('getDataIDs');
var totalamt=0;
for (var i = 0; i < dataIds.length; i++) {
//Get row data
var data = $('#grid1').jqGrid('getRowData', dataIds[i]);
totalamt = totalamt+parseFloat(data.amt);
}
jQuery('#grid1').jqGrid('footerData','set',{amt:totalamt});

jqgrid with php and sqlserver

Since the legacy system was developed in Powerbuilder, datawindow was heavily in used; the users used to work with grid-like interface. Thus, I need to look for a implementation that works with the web development and found that jqgrid has many nice features that it can be applied, after I tried its demo.

However, it lacked of examples that was working with MS sql server, and most of them out there are mysql examples.

After a day or two, I finally manage how to retrieve data from MS SQL server to jqgrid and update back to the database with PHP and sqlsrv driver.

However, I don't like the grid send update request immediately, once the data in the grid was modified. This is because I need to do a batch insert/update to fulfill the logical design.

Let's see how much the jqgrid can do or any work-around on this issue.

update (12/29):
jqgrid, constructing option:
editurl:'clientArray',
the grid will not fire the update request to server if editurl is set to 'clientArray',
then I can manipulate the data, clientArray, anytime I want and send a batch update to the server.

PHP, MS SQL Server, sqlsrv_connect() "CharacterSet"=>"UTF-8" is recommended in $connectionInfo if you got character encoding problem like me.

If you're like me that is using MS SQL Server 2008 R2 and going to use sqlsrv driver, now I'm using php_sqlsrv_53_ts_vc9.dll, for PHP and then facing the problem of character encoding problem on the browser, I found out someone recommended putting "CharacterSet"=>"UTF-8" in $connectionInfo in sqlsrv_connect(). And it works like a charm for me.

I didn't even need to convert the datatype from varchar to nvarchar to work with, though many posts on the web recommended to make such a change; I'm not sure yet, since I just tested with "select" statement, I'll test with "update" and "insert" later.

Anyway, by now, 2008 r2 works just fine with adding this simple option, "CharacterSet"=>"UTF-8" , in $connectionInfo of sqlsrv_connect() for select statement.

Updated:3/3/2012
I've used this on stored-procedure, select statement, insert, update and delete without any changes from the database which is my primary objective. And now, I just put it on Amazon EC2 server with SQL Express 2008 as it is also free of charge for the first year. So good!

apache could not load, and start and stop

I'm using xampp to start the apache server, but it told my port 80 is in used and cannot be started.

This was caused by some other application/program taking the port 80. Some people uninstalled skype and work fine. Someone said uninstall the apache and working on the regedit and re-install and succeed, but it has too much works and risky. And I found some people were starting apache on a machine that has IIS running and didn't notice like me.

For me, I found a couple reasons that I started apache fail.
1. a dll doesn't exist in \ext\ folder that cannot be added in the php.ini (it's ok if it's commented)
2. I'm running PHP 5.3.8, apache 2.2.21, sqlsrv driver php_sqlsrv_53_ts_vc9 on windows 7. The version non thread safe (nts_vc9) of dll could not be loaded.
3. I disabled a service in SCM which is called "World Wide Web Publishing Service". I don't know why and when this service was installed, enabled and started automatically. Anyway, with the disable of this service, my apache starts running flawless!

sqlsrv_execute could not retrieve all rows?

Since I'm using sqlsrv driver for my php web application development, I started to test the usage of the commands.

One of my testings is using sqlsrv_prepare and sqlsrv_execute to execute a stored procedure which have 18 parameters input and 106 columns output, and it should returns 360 rows in this test.

However, I found that it only returned 35 rows. After spending a night, it seems there was not much usage and footprints left on the web about this issue. Then, I try to replace sqlsrv_execute with sqlsrv_query since they at least got similar purpose, and I found sqlsrv_query has no problem on this! weird...anyway, sqlsrv_query got the job done.

updated 1: However, when I tried to create a html table to contain the result set look like in a grid that the user used to work with. The result set now only returned 35 rows again!!! Don't know the problem yet, may be the problem of resource allocation being limited? Let's see what I'll find...

updated 2: It looks like the problem was caused by the field of strings/data it retrieved from database with the combination of php code embedding with html code, since my data contains non-latin characters.

After I added date_format() to the datetime specific column and "ReturnDatesAsStrings"=>false in the connectionInfo. The problem is gone again.

Web base application development - php, sqlsrv, apache

I'm trying to use sqlsrv driver for php to migrate my two-tier database application which was build in Powerbuilder since version 9 then was upgraded to 10.5 and then 12.5 today, and is running on MS SQL Server from 2000 then 2005 with mirroring, and today on 2008 R2.

The version migration of Powerbuilder was interesting, from ansi to unicode, from classic to .net (but not success ><" since I'm a noob of .net). Also, setting up mirroring in SQL Server 2005 & 2008 has a lot of funs that would got someone interests, I guess these would be shared later someday.

Back to the topic, in order to minimal the efforts on database migration; indeed, I did consider mysql, as it seemed more comfortable with PHP; however, for the consideration of running the old and the new applications concurrently, I decided not to make changes for the back-end as a smooth and successful migration to web base is the primary objective.

I have some options to pick which database driver to communicate the MS SQL Server.
1. MSSQL
2. SQLSRV
3. PDO

I decided to give SQLSRV a shot, since it is newer than MSSQL and it seems have more features than PDO.

Updated: now, I'm using sqlserver driver to work with SQL 2008 and php, and executed stored-procedure for returning a result set, and porting into a jqgrid.