Friday, August 20, 2010

JQGrid 3.8 Beta Newbie Installation Guide

I really needed the grouping ability of jqGrid 3.8 beta up and running on my laptop so that I could start working on a project. So I had some time this week to figure out how to get the jqGrid 3.8 beta installed.

I had a lot of difficulty, as explained in my previous post, with rolling out jqGrid 3.8 beta do to newbieness and lack of knowledge. In my last post I reverted to using jqGrid 3.7.2, since the documentation and demos were more complete, and I was able to get that version working.

This week, I went back to jqGrid 3.8 beta to try to figure out what I did wrong. It was much easier to figure out now that I have a little bit of understanding.

To install 3.8:

1. I downloaded and extracted the jqGrid Demo and Source files.

2. I then created folder in my apache htdocs directory called 38beta. Inside this folder I copied over the js and themes directory from the extracted Demo and Source files.

3. I modified my test.htm file for the jqGrid 3.8 format and I used the same database and php files from my previous post. (I know that database is the dumbest thing in the world I could have used grouping on, since everything is unique, but I just wanted to get it working.)

I am going to post my test.htm code below. Remember that you have to view the file from a localhost url as in http://localhost/38beta/test.htm. This will have form edit, delete, add, and search working along with grouping. You also need the three PHP files from my previous post.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Ticket List</title>

<link rel="stylesheet" type="text/css" media="screen" href="themes/redmond/jquery-ui-1.8.2.custom.css" />
<link rel="stylesheet" type="text/css" media="screen" href="themes/ui.jqgrid.css" />

<script src="js/jquery-1.4.2.min.js" type="text/javascript"></script>
<script src="js/i18n/grid.locale-en.js" type="text/javascript"></script>
<script src="js/jquery.jqGrid.js" type="text/javascript"></script>
</head>

<body>
<table id ="list"></table>
<div id = "pager"></div>
</body>
</html>

<script type = "text/javascript">

jQuery(document).ready(function(){
jQuery("#list").jqGrid({
url:'server.php',
datatype: "json",
mtype: 'GET',
colNames:['Inv ID','Inv Date','Client ID','Amount','Tax','Total', 'Note'],
colModel:[
{name:'invid', index:'invid', width:55, align:'center',
searchoptions:{sopt:['eq','ne','lt','le','gt','ge','bw','ew','cn','nc']},
editable:false},
{name:'invdate', index:'invdate', width: 90, align:'center',
searchoptions:{sopt:['eq','ne','lt','le','gt','ge','bw','ew','cn','nc']},
editable:true},
{name:'client_id', index:'client_id', width: 90, align:'center',
searchoptions:{sopt:['eq','ne','lt','le','gt','ge','bw','ew','cn','nc']},
editable:true},
{name:'amount', index:'amount', width:80, align:'center',
searchoptions:{sopt:['eq','ne','lt','le','gt','ge','bw','ew','cn','nc']},
editable:true},
{name:'tax', index:'tax', width:80, align:'center',
searchoptions:{sopt:['eq','ne','lt','le','gt','ge','bw','ew','cn','nc']},
editable:true},
{name:'total', index:'total', width:80, align:'center',
searchoptions:{sopt:['eq','ne','lt','le','gt','ge','bw','ew','cn','nc']},
editable:true},
{name:'note', index:'note', width:150, align:'left',
searchoptions:{sopt:['eq','ne','lt','le','gt','ge','bw','ew','cn','nc']},
editable:true},
],
rowNum: 10,
rowList:[10,20,30],
autowidth: true,
height: "100%",
pager: '#pager',
sortname: 'client_id',
viewrecords: true,
sortorder: "asc",
caption:"My first grid",
grouping: true,
groupingView : {
groupField : ['client_id']
},
editurl:"edit.php"
});
jQuery("#list").jqGrid('navGrid','#pager',{edit:true,add:true,del:true});
});
</script>

Friday, August 13, 2010

JQGrid 3.7 Newbie Installation Guide

It has been forever since my last blog post but I have not had a chance to do any work on web sites until recently. I had someone who wanted a grid embedded in a web page that pulled data from a SQL database. After searching the web at various grid options I came across JQGrid which I really liked the way it looked and it seemed that it easily connected to MySQL.

Forty hours later I finally had a grid up and running.

Bear in mind, I had limited JavaScript exposure, no PHP experience, no experience with JQuery, no Apache server experience, and no MySQL experience. There was a lot of documentation out there but either I just could not grasp it or it was not suited for newbies. And from the tons of other questions I had seen on the forums I was not the only one having these issues.

I finally got a bit of a clue on how to role this out among the 3.7 grid demo pages and the JQGrid WIKI documentation. I wanted to outline what I did here and post my code so that other newbies can get up and running quickly and start figuring out how JQGrid works. Maybe it will save them 30+ hours of trial and error time.

I remind you I have no experience in the platform or languages represented here. I just kept trying different code until I got it to work. There is most likely a much better way to do the things. I didn't even bother with trying to figure out how to set a password on MySQL.

This is based on JQGrid version 3.7.2. There is a version 3.8 out that does some cool things but I could not find demos for everything I needed. I am going to go back and try to do the same with 3.8 now that I have somewhat of an idea of how it works. The code below will have the form search, edit, add and delete working.

1. First thing I did was download and install XAMPP from the Apache Friends web site. I took the default on everything and in no time I had an Apache web server with MySQL up and running.

2. Downloaded JQGrid from the Trirand web site. I was not sure what I needed so I just checked everything.

3. Downloaded a theme from the JQuery site. I tried using the latest themes but the grid never worked. Once I downloaded the legacy version then it worked fine. So I recommend doing the same, as I have no idea what caused the issue. I had seen other post of people having the same problem.

4. Unpack the zip files according to the Wiki installation. You will end up with two folders, one called js and the other called css. I created all my files and put these two folders in folder called test inside of the Apache htdocs directory.

5. Created the sample table from the information provided in the Wiki documentation. They even have sample data you can import. It took a bit of time to figure out how to import it into MySQL. They only way I could get it to go was using the csv load data with commas as a separator.

6. Lastly I created the four files that I will be posting below in the test folder. When you load the web page you will need to use the localhost path. For example on my workstation I had to use http://localhost/test/test.htm.

test.htm - the main page for viewing the grid

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Ticket List</title>

<link rel="stylesheet" type="text/css" media="screen" href="css/redmond/jquery-ui-1.7.3.custom.css" />
<link rel="stylesheet" type="text/css" media="screen" href="css/ui.jqgrid.css" />

<script src="js/jquery-1.4.2.min.js" type="text/javascript"></script>
<script src="js/i18n/grid.locale-en.js" type="text/javascript"></script>
<script src="js/jquery.jqGrid.min.js" type="text/javascript"></script>
</head>

<body>
<table id ="list"></table>
<div id = "pager"></div>
</body>
</html>

<script type = "text/javascript">

jQuery(document).ready(function(){
jQuery("#list").jqGrid({
url:'server.php',
datatype: "json",
mtype: 'GET',
colNames:['Inv ID','Inv Date','Client ID','Amount','Tax','Total', 'Note'],
colModel:[
{name:'invid', index:'invid', width:55, align:'center',
searchoptions:{sopt:['eq','ne','lt','le','gt','ge','bw','ew','cn','nc']},
editable:false},
{name:'invdate', index:'invdate', width: 90, align:'center',
searchoptions:{sopt:['eq','ne','lt','le','gt','ge','bw','ew','cn','nc']},
editable:true},
{name:'client_id', index:'client_id', width: 90, align:'center',
searchoptions:{sopt:['eq','ne','lt','le','gt','ge','bw','ew','cn','nc']},
editable:true},
{name:'amount', index:'amount', width:80, align:'center',
searchoptions:{sopt:['eq','ne','lt','le','gt','ge','bw','ew','cn','nc']},
editable:true},
{name:'tax', index:'tax', width:80, align:'center',
searchoptions:{sopt:['eq','ne','lt','le','gt','ge','bw','ew','cn','nc']},
editable:true},
{name:'total', index:'total', width:80, align:'center',
searchoptions:{sopt:['eq','ne','lt','le','gt','ge','bw','ew','cn','nc']},
editable:true},
{name:'note', index:'note', width:150, align:'left',
searchoptions:{sopt:['eq','ne','lt','le','gt','ge','bw','ew','cn','nc']},
editable:true},
],
rowNum: 10,
rowList:[10,20,30],
autowidth: true,
height: "100%",
pager: '#pager',
sortname: 'invid',
viewrecords: true,
sortorder: "asc",
caption:"My first grid",
editurl:"edit.php"
});
jQuery("#list").jqGrid('navGrid','#pager',{edit:true,add:true,del:true});
});
</script>

dbconfig.php - stores the database name and password

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpassword = '';
$database = 'test';
?>



server.php - used for grid filling and searching

<?php

// database connection information is stored here
include("dbconfig.php");

// The first four variables retrieve parameters for
// table creation. (Some for searching also.)
$page = $_REQUEST['page']; // requested page
$limit = $_REQUEST['rows']; // rows per page
$sidx = $_REQUEST['sidx']; // index row
$sord = $_REQUEST['sord']; // sorting order

// if index isn't passed then use the first column
if(!$sidx) $sidx=1;

// these three variables are used to search for records
$where = $_REQUEST['searchField']; // field to be searched
$whereValue = $_REQUEST['searchString']; // value to be looked for
$whereOper = $_REQUEST['searchOper']; // operator used

// If $whereOper was passed then cofigure the SQL operator
// and what we are searching for. There are a couple of
// these remarked out because honestly I do not know the mySQL
// syntax and I really don't need it for this project.
if ($whereOper <> NULL) {

switch($whereOper){
case("eq"):
$sqlOperator = " = ";
$whereValue = "'".$whereValue."'";
break;
case("ne"):
$sqlOperator = " <> ";
$whereValue = "'".$whereValue."'";
break;
case("lt"):
$sqlOperator = " < ";
$whereValue = "'".$whereValue."'";
break;
case("le"):
$sqlOperator = " <= ";
$whereValue = "'".$whereValue."'";
break;
case("gt"):
$sqlOperator = " > ";
$whereValue = "'".$whereValue."'";
break;
case("ge"):
$sqlOperator = " >= ";
$whereValue = "'".$whereValue."'";
break;
case("bw"):
$sqlOperator = " REGEXP '^";
$whereValue = $whereValue."'" ;
break;
// case("bn"):
// $sqlOperator = "=";
// $whereValue = "'".$whereValue."'";
// break;
// case("in"):
// $sqlOperator = "=";
// $whereValue = "'".$whereValue."'";
// break;
// case("ni"):
// $sqlOperator = "=";
// $whereValue = $whereValue."'";
// break;
case("ew"):
$sqlOperator = " LIKE '%";
$whereValue = $whereValue."'";
break;
// case("en"):
// $sqlOperator = "=";
// $whereValue = "'".$whereValue."'";
// break;
case("cn"):
$sqlOperator = " LIKE '%";
$whereValue = $whereValue."%'";
break;
case("nc"):
$sqlOperator = " NOT LIKE '%";
$whereValue = $whereValue."%'";
break;
}
}

// Connect to the MySQL database server
$db = mysql_connect($dbhost, $dbuser, $dbpassword) or die("Connection Error: " . mysql_error());

// Select the database
mysql_select_db($database) or die("Error connecting to db.");

// if $where is not NULL then get the count on the search criteria,
// else get the count on everything.
if ($where <> NULL){
$result = mysql_query("SELECT COUNT(*) AS count FROM invheader WHERE ".$where.$sqlOperator.$whereValue);
} else {
$result = mysql_query("SELECT COUNT(*) AS count FROM invheader");
}

// Calculate the number of rows for the query.
// This is used for paging the result.
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$count = $row['count'];

// calculate the total pages for the query
if($count > 0) {
$total_pages = ceil($count/$limit);
} else {
$total_pages = 0;
}

// if for some reasons the requested page is greater than the total
// set the requested page to total page
if ($page > $total_pages) $page=$total_pages;

// calculate the starting position of the rows
$start = $limit*$page-$limit;

// if for some reasons start position is negative set it to 0
// typical case is that the user type 0 for the requested page
if($start < 0) $start = 0;

// if where is not null then retrieve requested rows based on search
// else retrieve everything
if ($where <> NULL) {

$SQL = "SELECT invid, invdate, client_id, amount, tax, total, note FROM invheader WHERE ".$where.$sqlOperator.$whereValue." ORDER BY ".$sidx." ".$sord." LIMIT ".$start." , ".$limit;
$result = mysql_query($SQL) or die("Could not execute query ".$SQL." ".mysql_error());

} else {

$SQL = "SELECT invid, invdate, client_id, amount, tax, total, note FROM invheader ORDER BY ".$sidx." ".$sord." LIMIT ".$start." , ".$limit;
$result = mysql_query($SQL) or die("Could not execute query. ".mysql_error());
}

// response back to web page
$response->page = $page;
$response->total = $total_pages;
$response->records = $count;

$i=0;
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
$response->rows[$i]['id']=$row[invid];
$response->rows[$i]['cell']=array($row[invid], $row[invdate], $row[client_id], $row[amount], $row[tax],
$row[total], $row[note]);
$i++;
}
echo json_encode($response);
mysql_close($db);
?>



edit.php - used for editing, adding, and deleting rows in the grid

<?php

//database connection information
include("dbconfig.php");

$invid = $_REQUEST['invid'];
$invdate = $_REQUEST['invdate'];
$client_id = $_REQUEST['client_id'];
$amount = $_REQUEST['amount'];
$tax = $_REQUEST['tax'];
$total = $_REQUEST['total'];
$note = $_REQUEST['note'];
$id = $_REQUEST['id'];
$oper = $_REQUEST['oper'];

// Connect to the MySQL database server
$db = mysql_connect($dbhost, $dbuser, $dbpassword) or die("Connection Error: " . mysql_error());

// Select the database
mysql_select_db($database) or die("Error connecting to db.");

// if operation is edit a row
if ($oper == "edit") {

$SQL = "UPDATE invheader";
$SQL = $SQL." SET invdate = '".$invdate;
$SQL = $SQL."', client_id = '".$client_id;
$SQL = $SQL."', amount = '".$amount;
$SQL = $SQL."', tax = '".$tax;
$SQL = $SQL."', total = '".$total;
$SQL = $SQL."', note = '".$note;
$SQL = $SQL."' WHERE invid = '".$id."'";
$result = mysql_query($SQL) or die("Could not execute query ".mysql_error());

// if operation is add a row
} elseif ($oper == "add"){

$SQL = "INSERT INTO invheader (invdate, client_id, amount, tax, ";
$SQL = $SQL."total, note) Values('";
$SQL = $SQL.$invdate."','";
$SQL = $SQL.$client_id."','";
$SQL = $SQL.$amount."','";
$SQL = $SQL.$tax."','";
$SQL = $SQL.$total."','";
$SQL = $SQL.$note."')";
$result = mysql_query($SQL) or die("Could not execute query ".mysql_error());

// if operation is delete a row
} elseif ($oper == "del"){

$SQL = "DELETE FROM invheader WHERE invid = '".$id."'";
$result = mysql_query($SQL) or die("Could not execute query ".mysql_error());

}

mysql_close($db);
?>

Thursday, February 18, 2010

Using Excel VBA to Open and Autofill Web Pages

This post is more about manipulating web pages than creating them.

My employer had a 2007 excel spreadsheet that they used to track several hundred projects. They would open a browser then copy/paste the information into a modified Google map. My boss asked me if there was a way I could automate this process.

I found parts to the solution here and there. I hope this will help someone else. It was a fun challenge and led to other similiar projects. Please remember, I am not a professional programmer so feel free to add better suggestions to comments.

Since the actual project cannot be duplicated here let’s just say we have a spreadsheet with a customer’s Name, Address, City, State and Zip. We want to send that information to Google and see the map.

Click here to download the example created in 2007 and saved as xls: http://www.webdebris.com/test/GetMapExample.zip

First thing I will need is a button. Switching over to the developer tab I add an active X button and call it GetMap. Adding it above the header, which can be froze, will let the button be available always.

Next I need to add a reference to Microsoft’s web browser to the excel spreadsheet. In the visual basic editor, go to Tools References and add Microsoft Internet Controls.

Now it is time to start coding.
Option Explicit

Public ie As Object

Public strAddress As String 'Builds the address to send to Google

'The following line will let the code sleep while waiting for the page to load
Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
'When working in excel the browser will stay in the background. This will bring the browser to the top
Private Declare Function BringWindowToTop Lib "user32" (ByVal hwnd As Long) As Long

Private Sub GetMap_Click()

'There is a bug/feature with Excel that when the IE window closes the ie object still has a reference.
'Normally you would check for the IE window to close and then set the reference to nothing.
'Web Control does not have an on_quit event. So you have to trap by error.
On Error GoTo ErrHandle:

Dim strCursorLocation As String 'The location of the cursor when the button was clicked
Dim strRow As String 'Will hold the row number
Dim i As Integer

strCursorLocation = ActiveCell.Address(False, False) 'Get the current cursor location
strRow = ""

'Remove letters from strCursorLocation
For i = 1 To Len(strCursorLocation)

If IsNumeric(Mid(strCursorLocation, i, 1)) Then

strRow = strRow & Mid(strCursorLocation, i, 1)

End If

Next i

'Build the address by adding columns B thru E
strAddress = Range("B" + strRow).Value + " "
strAddress = strAddress + Range("C" + strRow).Value + " "
strAddress = strAddress + Range("D" + strRow).Value + " "
strAddress = strAddress + Range("E" + strRow).Value + " "

'Check to see if an IE window has been opened
If ie Is Nothing Then

Call OpenGoogle

End If

'Bring IE to top. Does not bring to top 100 percent of the time.
If Not ie Is Nothing Then

BringWindowToTop (ie.hwnd)

End If

Call EnterAddress

Exit Sub

ErrHandle:

'Reopen IE window if it was closed
If Err.Number = -2147417848 Or Err.Number = 462 Then

Call OpenGoogle
Resume

Else

'Something else has happened
MsgBox "Error: " + CStr(Err.Number) + " " + Err.Description, vbCritical

End If

End Sub

Private Sub OpenGoogle()

'Create IE Window
Set ie = CreateObject("Internetexplorer.Application")
'Show IE Window
ie.Visible = True
'Navigate to the google map page
ie.Navigate "http://maps.google.com/maps?hl=en&tab=wl"

'Wait for the page to load
Do While ie.Busy = True

DoEvents
Sleep 500

Loop

End Sub

Private Sub EnterAddress()

'Check to see if map page was navigated away from
If ie.document.URL <> "http://maps.google.com/maps?hl=en&tab=wl" Then

'Reload map page
ie.Navigate "http://maps.google.com/maps?hl=en&tab=wl"

'Wait for the page to load
Do While ie.Busy = True

DoEvents
Sleep 500

Loop

End If

With ie

.document.all("q_d").Value = strAddress 'enter the address
.document.all("q-sub").Click 'click the search button

End With

End Sub


Wednesday, February 17, 2010

Liquid, left, or middle? What to do, what to do.

I used to be all about liquid layouts. But these days there are more and more people using wide screen monitors. The liquid layout design can turn ugly quick on one of these displays. If you have a lot of information on web page it seems to work okay. For me, more often then not the amount of information on a page will vary greatly.

When I see sites built on the left through a wide screen it all seems out of balance. I think the key to left built sites could be a background graphic that would balance it out.

On the other hand, I am now seeing web sites that have been developed on wide screens. When I display them on my older CRT monitor the columns are really scrunched up and hard to read. When I move the page to my wide screen it looks fine.

So now I am becoming a fan of centered pages. All the sites I have done recently are centered and usually have a width of 1000 pixels. I spent some time on http://www.statowl.com/ and looked through the information they have. Looks like 800 x 600 display modes and less are almost gone. 1024 x 768 and greater are the norm.

Even though I like strict, clean, and bare bones code I do not like to sacrifice design because of a few people using older browsers or displays. (Hmmm... wonder if the people developing sites on wide screens feel the same way about non wide screen monitors.)

Just curious as to other viewpoints.