jQuery Grid Plugin - jqGrid

<table id="list" class="scroll" cellpadding="0" cellspacing="0"></table>
<div id="pager" class="scroll" style="text-align:center;"></div>
 <script type="text/javascript">
var lastSelectedId;
var theme = "steel";

$("head").append("<link>");
css = $("head").children(":last");
css.attr({
   rel:  "stylesheet",
   type: "text/css",
   href: "scripts/themes/"+theme+"/grid.css",
   title: theme,
   media: "screen"
});

$('#list').jqGrid({
     url:'grid.php',
  datatype: 'json',
  mtype: 'POST',
     colNames:['ID','Race_Name', 'Race_Year', 'Race_Result'],
     colModel:[    
       {name:'ID',index:'ID', width:55,editable:false},     
       {name:'Race_Name',index:'Race_Name', width:100,editable:true, edittype:'text',editoptions:{size:30,maxlength:50}},
       {name:'Race_Year',index:'Race_Year', width:80, editable:true, edittype:'text',editoptions:{size:30,maxlength:50}},
       {name:'Race_Result',index:'Race_Result', width:80, editable:true, edittype:'text',editoptions:{size:30,maxlength:50}}  
     ],
     rowNum:10,
     rowList:[5,10,20,30],
     imgpath: 'scripts/themes/'+theme+'/images',//alters buttons
     pager: $('#pager'),
     sortname: 'ID',
    viewrecords: true,
    sortorder: "desc",
    caption:"JSON Example",
  width:600,
  height:250,  
  onSelectRow: function(id){
    if(id && id!==lastSelectedId){
      $('#list').restoreRow(lastSelectedId);
      $('#list').editRow(id,true,null,onSaveSuccess);
      lastSelectedId=id;
    }
  },
  editurl:'grid.php?action=save'
});
function onSaveSuccess(xhr)
{
  response = xhr.responseText;
  if(response == 1)
    return true;
  return false;
}
</script>

<?php
// load error handling script and the Grid class
require_once('error_handler.php');
require_once('grid.class.php');
$action = 'load';
if(isset($_GET['action']))    
    $action = $_GET['action'];
if($action == 'load')
{
    $page = $_POST['page'];
    $limit = $_POST['rows'];
    $sidx = $_POST['sidx'];
    $sord = $_POST['sord'];
    $grid = new Grid($page,$limit,$sidx,$sord);
    $response->page = $page;
    $response->total = $grid->getTotalPages();
    $response->records = $grid->getTotalItemsCount();
    $currentPageItems = $grid->getCurrentPageItems();

    for($i=0;$i<count($currentPageItems);$i++) {
        $response->rows[$i]['id']=$currentPageItems[$i]['ID'];
        $response->rows[$i]['cell']=array(
                                $currentPageItems[$i]['ID'],
                                $currentPageItems[$i]['Race_Name'],
                                $currentPageItems[$i]['Race_Year'],
                                $currentPageItems[$i]['Race_Result']
                            );    
    }
    echo json_encode($response);
}
elseif ($action == 'save')
{
    $ID = $_POST['id'];
    $Race_Name = $_POST['Race_Name'];
    $Race_Year = $_POST['Race_Year'];
    $Race_Result = $_POST['Race_Result'];
    $grid = new Grid();
    echo $grid->updateItem($ID,$Race_Result,$Race_Year,$Race_Name);
}
?>

<?php
// load configuration file
require_once('config.php');
// start session
session_start();

// includes functionality to manipulate the products list
class Grid
{      
  // grid pages count
  private $mTotalPages;
  // grid items count
  private $mTotalItemsCount;
  private $mItemsPerPage;
  private $mCurrentPage;
 
  private $mSortColumn;
  private $mSortDirection;
  // database handler
  private $mMysqli;
    
  // class constructor  
  function __construct( $currentPage =1, $itemsPerPage=5, $sortColumn='ID', $sortDirection='asc')
  {   
    // create the MySQL connection
    $this->mMysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD,
                                DB_DATABASE);
    $this->mCurrentPage = $currentPage;
    $this->mItemsPerPage = $itemsPerPage;
    $this->mSortColumn = $sortColumn;
    $this->mSortDirection = $sortDirection;
    // call countAllRecords to get the number of grid records
    $this->mTotalItemsCount = $this->countAllItems();
    if($this->mTotalItemsCount >0)    
        $this->mTotalPages = ceil($this->mTotalItemsCount/$this->mItemsPerPage);
    else
        $this->mTotalPages=0;    
    if($this->mCurrentPage > $this->mTotalPages)
        $this->mCurrentPage = $this->mTotalPages;
  }
 
 
 
  // read a page of products and save it to $this->grid
  public function getCurrentPageItems()
  {
    // create the SQL query that returns a page of products
    $queryString = 'SELECT * FROM userCake_races';    
    $queryString .= ' ORDER BY '.$this->mMysqli->real_escape_string($this->mSortColumn).' '
                .$this->mMysqli->real_escape_string($this->mSortDirection);        
    $start = $this->mItemsPerPage* $this->mCurrentPage - $this->mItemsPerPage; // do not put $limit*($page - 1)
    if ($start<0)
        $start = 0;
    $queryString .= ' LIMIT '.$start.','.$this->mItemsPerPage;
    
    // execute the query
    if ($result = $this->mMysqli->query($queryString))
    {
      for($i = 0; $items[$i] = $result->fetch_assoc(); $i++) ;   

      // Delete last empty one
      array_pop($items);
      
      // close the results stream                     
      $result->close();
      return $items;
    }       
  }
 
  public function getTotalPages()
  {
    return $this->mTotalPages;
  }
 
 
  // update a product
  public function updateItem($ID, $Race_Name, $Race_Year, $Race_Result)
  {
    // escape input data for safely using it in SQL statements
    $ID = $this->mMysqli->real_escape_string($ID);
    $Race_Name = $this->mMysqli->real_escape_string($Race_Name);
    $Race_Year = $this->mMysqli->real_escape_string($Race_Year);
    $Race_Result = $this->mMysqli->real_escape_string($Race_Result);
    // build the SQL query that updates a product record
    $queryString =  'UPDATE userCake_races SET Race_Name="' . $Race_Name . '", ' .
                    'Race_Year=' . $Race_Year . ',' .
                    'Race_Result=' . $Race_Result .
                    ' WHERE ID=' . $ID;        
    
    // execute the SQL command      
    $this->mMysqli->query($queryString);  
    return $this->mMysqli->affected_rows;
  }

 

  // returns the total number of records for the grid
  private function countAllItems()
  {
      // the query that returns the record count
      $count_query = 'SELECT COUNT(*) FROM userCake_races';
      // execute the query and fetch the result
      if ($result = $this->mMysqli->query($count_query))
      {
        // retrieve the first returned row
        $row = $result->fetch_row();         
        // close the database handle
        $result->close();
        return $row[0];
      }    
    return 0;
  }         
 
  public function getTotalItemsCount()
  {
    return $this->mTotalItemsCount;
  }
    
// end class Grid
}
?>