We consider two types of dates - Date and DateTime.
By default the format of the date and datetime accepted server side is Y-m-d for the date and Y-m-d H:i:s for datetime.
The default format of date and datetime represented client side is d/m/Y and d/m/Y H:i:s
For more information on date formatting in PHP refer here
You can change these formats using the following methods:
setDbDate
setDbTime
for the underlaing database and
setUserDate
setUserTime
for the client side representation
In order to synchronize date formats between the client and server a datearray variable should be used.
This is array which tells the script which fields are dates. The value(s) in the array should correspond to the name in colModel
This conversion is used when a search or CRUD operations are performed
In order to ilistrate this we again will refere to our example
Let suppose that the date format accepted from the server is Y-m-d and at client the format should be m/d/Y.
In order to display and search correct the html file should be changed this way.
<!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>PHP jqGrid Class Example</title> <link rel="stylesheet" type="text/css" media="screen" href="themes/redmond/jquery-ui-1.7.1.custom.css" /> <link rel="stylesheet" type="text/css" media="screen" href="themes/ui.jqgrid.css" /> <script src="js/jquery-1.3.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> <script type="text/javascript"> jQuery(document).ready(function(){ .... // Craeate the grid manually jQuery("#grid").jqGrid({ "colModel":[ {"name":"OrderID","index":"OrderID","label":"ID","width":60, "key":true}, {"name":"OrderDate","index":"OrderDate", "formatter":"date", "formatoptions":{"srcformat":"Y-m-d", "newformat":"m/d/Y"} }, {"name":"CustomerID","index":"CustomerID"}, {"name":"Freight","index":"Freight"}, {"name":"ShipName","index":"ShipName"} ], "url":"querygrid.php", "datatype":"json", "jsonReader":{repeatitems:false}, "pager":"#pager" }); // Set navigator with search enabled. jQuery("#grid").jqGrid('navGrid','#pager',{add:false,edit:false,del:false}); ...... }); </script> </head> <body> ...... <table id="grid"></table> <div id="pager"></div> ....... </body> </html>
The changes in PHP script should be:
<?php require_once 'jq-config.php'; // include the jqGrid Class require_once "php/jqGrid.php"; // include the PDO driver class require_once "php/jqGridPdo.php"; // Connection to the server $conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD); // Create the jqGrid instance $grid = new jqGrid($conn); // Write the SQL Query $grid->SelectCommand = 'SELECT OrderID, OrderDate, CustomerID, Freight, ShipName FROM orders'; // set the user date format to m/d/Y $grid->setUserDate('m/d/Y'); // tell the grid which field is date $grid->datearray = array('OrderDate'); $grid->dataType = "json"; $grid->queryGrid(); ?>
That is all. Now your display and search will be correct.