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);
?>
