In this post, we will show you how to connect our
jQuery Grid to a MySql Database using PHP. We will obtain the data from MySql Database and especially the Northwind Database. The data will be loaded on demand when the user scrolls. You can download the Northwind database .sql script
here and run it into MySQL to create the database.
The first thing we need to do is create the file we’ll connect with. We’ll call this file connect.php.
<?php# FileName="connect.php"$hostname = "localhost";$database = "northwind";$username = "root";$password = "";?>
The next thing is to create a php file called data.php that will run queries and bring the data in JSON format. The Grid’s purpose will be to display the JSON data and to make requests for new data. In virtual mode, jqxGrid sends additional parameters in the requests like: ‘recordstartindex’ and ‘recordendindex’. We’ll use these to return to the Grid only the data it needs to show. In the code below, the params are used in the “LIMIT” part of the query.
<?php#Include the connect.php fileinclude('connect.php');#Connect to the database//connection String$connect = mysql_connect($hostname, $username, $password)or die('Could not connect: ' . mysql_error());//select databasemysql_select_db($database, $connect);//Select The database$bool = mysql_select_db($database, $connect);if ($bool === False){ print "can't find $database";} // get first visible row. $firstvisiblerow = $_GET['recordstartindex']; // get the last visible row. $lastvisiblerow = $_GET['recordendindex']; $rowscount = $lastvisiblerow - $firstvisiblerow; // build query. $query = "SELECT SQL_CALC_FOUND_ROWS * FROM orders"; $query .= " LIMIT $firstvisiblerow, $rowscount"; $result = mysql_query($query) or die("SQL Error 1: " . mysql_error()); $sql = "SELECT FOUND_ROWS() AS `found_rows`;"; $rows = mysql_query($sql); $rows = mysql_fetch_assoc($rows); $total_rows = $rows['found_rows']; // get data and store in a json array while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { $orders[] = array( 'OrderID' => $row['OrderID'], 'OrderDate' => $row['OrderDate'], 'ShippedDate' => $row['ShippedDate'], 'ShipName' => $row['ShipName'], 'ShipAddress' => $row['ShipAddress'], 'ShipCity' => $row['ShipCity'], 'ShipCountry' => $row['ShipCountry'] ); } $data[] = array( 'TotalRows' => $total_rows, 'Rows' => $orders ); echo json_encode($data); ?>
The final step is to create a file called index.php and add the jqxGrid widget. To use the Grid widget in virtual mode, we need to do the following things – set its ‘virtualmode’ property to true, add a callback function called rendergridrows which returns an array of rows to display in the Grid. Then, we need to set the source object’s totalrecords property to point to the total number of records in the Grid. We need to set the total number of records, because in virtual mode, the Grid widget does not know how many records it should display, but the end-user should be able to scroll through them and the Grid should load the records on demand when the user scrolls and depending on the current scroll position. The records count is returned from the data.php file in a field called TotalRows.
<!DOCTYPE html><html lang="en"><head> <link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" /> <link rel="stylesheet" href="../../jqwidgets/styles/jqx.classic.css" type="text/css" /> <script type="text/javascript" src="../../scripts/jquery-1.7.2.min.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxcore.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxbuttons.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxscrollbar.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxmenu.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.selection.js"></script> <script type="text/javascript"> $(document).ready(function () { // prepare the data var source = { url: 'data.php', dataType: 'json', cache: false, datafields: [ { name: 'OrderID' }, { name: 'ShippedDate' }, { name: 'ShipName' }, { name: 'ShipAddress' }, { name: 'ShipCity' }, { name: 'ShipCountry' } ], root: 'Rows', beforeprocessing: function (data) { source.totalrecords = data[0].TotalRows; } }; var dataAdapter = new $.jqx.dataAdapter(source); $("#jqxgrid").jqxGrid( { source: source, theme: 'classic', virtualmode: true, rendergridrows: function(obj) { return obj.data; }, columns: [ { text: 'ID', datafield: 'OrderID', width: 200 }, { text: 'Shipped Date', datafield: 'ShippedDate', cellsformat: 'd', width: 200 }, { text: 'Ship Name', datafield: 'ShipName', width: 200 }, { text: 'Address', datafield: 'ShipAddress', width: 180 }, { text: 'City', datafield: 'ShipCity', width: 100 }, { text: 'Country', datafield: 'ShipCountry', width: 140 } ] }); }); </script></head><body class='default'> <div id="jqxgrid"></div></body></html> 