¿´¿´Å·ÃÀÒ»¼¶ÊÓÆµ¿á¹·

Data Binding with PHP's mysqli Extension

In this help topic, we will show you how to use jQWidgets with the PHP’s mysqli Extension.

Let’s start with a brief introduction of the PHP’s mysqli extension. The mysqli extension, or as it is sometimes known, the MySQL improved extension, was developed to take advantage of new features found in MySQL systems versions 4.1.3 and newer. The mysqli extension is included with PHP versions 5 and later. The mysqli extension has a number of benefits, the key enhancements over the mysql extension being:

Object-oriented interface
Support for Prepared Statements
Support for Multiple Statements
Support for Transactions
Enhanced debugging capabilities
Embedded server support

For further information on the mysql extension, see Mysql.

After we have learned what is mysqli, now let’s see how to create a web page with jqxGrid and implement server paging and sorting using PHP and mysqli.

1. The first step is to create the file weâ€™ll connect with. We will call the file â€˜connect.phpâ€™.

<?php
# FileName="connect.php"
$hostname = "localhost";
$database = "northwind";
$username = "root";
$password = "";
?>

2. The second step is to create the file that will handle the queries. We will call the file data.php. The data.php file connects to the â€˜Customersâ€™ table from the Northwind Database and returns the data as JSON.
Our goal is to send JSON data to the client in small pieces that the client requests, and respond when the page number, page size, sort column or sort order is changed by the end user. In the data.php file, we get the values of the â€˜pagenumâ€™ and â€˜pagesizeâ€™ members which the Grid sends to the server and we use them to specify the range of records in the query to the MySQL Database. We also make a query to find the total rows of the â€˜Customersâ€™ table in order to display the total rows count in the Grid’s Pager. The sql query depends on the ‘sortdatafield’(Column) and the ‘sortorder’(â€˜ascendingâ€™ or â€˜descendingâ€™) and also the ‘pagenum’ and ‘pagesize’. The ‘pagenum’, ‘pagesize’, â€˜sortdatafieldâ€™ and â€˜sortorderâ€™ parameters are sent to the server automatically by jqxGrid when the jqxDataAdapter plug-in makes an Ajax call. The returned JSON data has two members – the total rows of the â€˜Customersâ€™ table and the records to be displayed on the Grid widget.

<?php
	#Include the connect.php file
	include('connect.php');
	#Connect to the database
	$mysqli = new mysqli("$hostname", "$username", "$password", $database);
	/* check connection */
	if (mysqli_connect_errno()) {
		printf("Connect failed: %s\n", mysqli_connect_error());
		exit();
	}
	// Initialize pagenum and pagesize
	$pagenum = $_POST['pagenum'];
	$pagesize = $_POST['pagesize'];		
	$start = $pagenum * $pagesize;
	if (isset($_POST['sortdatafield']))
	{
		$sortfield = $_POST['sortdatafield'];
		$sortorder = $_POST['sortorder'];
		
		if ($sortorder != '')
		{			
			if ($sortorder == "desc")
			{
				$query = "SELECT CompanyName, ContactName, ContactTitle, Address, City, Country FROM Customers ORDER BY" . " " . $sortfield . " DESC LIMIT ?, ?";
			}
			else if ($sortorder == "asc")
			{
				$query = "SELECT CompanyName, ContactName, ContactTitle, Address, City, Country FROM Customers ORDER BY" . " " . $sortfield . " ASC LIMIT ?, ?";
			}			
			$result = $mysqli->prepare($query);
			$result->bind_param('ii', $start, $pagesize);
		}
		else
		{
			$result = $mysqli->prepare("SELECT SQL_CALC_FOUND_ROWS CompanyName, ContactName, ContactTitle, Address, City, Country FROM Customers LIMIT ?, ?");
			$result->bind_param('ii', $start, $pagesize);		
		}
	}
	else
	{
		$result = $mysqli->prepare("SELECT SQL_CALC_FOUND_ROWS CompanyName, ContactName, ContactTitle, Address, City, Country FROM Customers LIMIT ?, ?");
		$result->bind_param('ii', $start, $pagesize);		
	}
	
	 /* execute query */
	$result->execute();
	/* bind result variables */
	$result->bind_result($CompanyName, $ContactName, $ContactTitle, $Address, $City, $Country);
	/* fetch values */
	while ($result -> fetch()) {
		$customers[] = array(
		'CompanyName' => $CompanyName,
		'ContactName' => $ContactName,
		'ContactTitle' => $ContactTitle,
		'Address' => $Address,
		'City' => $City,
		'Country' => $Country
	  );
	}	
	
	// get the total rows.
	$result = $mysqli->prepare("SELECT FOUND_ROWS()");
	$result->execute();
	$result->bind_result($total_rows);
	$result->fetch();
	$data[] = array(
       'TotalRows' => $total_rows,
	   'Rows' => $customers
	);
	echo json_encode($data);
	/* close statement */
	$result->close();
	/* close connection */
	$mysqli->close();
?>

In the above code we have used mysqli prepared statements. ‘mysqli_prepare’ prepares the SQL query, and returns a statement handle to be used for further operations on the statement. The query must consist of a single SQL statement. The parameter markers must be bound to application variables using mysqli_stmt_bind_param()(see $result->bind_param(‘ii’, $start, $pagesize);) and/or mysqli_stmt_bind_result()(see $result->bind_result($CompanyName, $ContactName, $ContactTitle, $Address, $City, $Country);) before executing the statement or fetching rows.
For more information about “bind_param” and “bind_result”, see: mysqli-stmt.bind-param.php and mysqli-stmt.bind-result.php.
The mysqli_stmt_execute function call executes the query that has been previously prepared using the mysqli_prepare() function. When executed the parameter markers are automatically replaced with the appropriate data.
For more details about the “execute” function, please visit: mysqli-stmt.execute.php.

3. The final step is to create the index.php page where we will add the Grid widget and set up the jqxDataAdapter plug-in.

<!DOCTYPE html>
<html lang="en">
<head>
    <link rel="stylesheet" href="/jquery-widgets-documentation/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="none"></script>  
    <script type="text/javascript" src="none"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxbuttons.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxscrollbar.js"></script>
    <script type="text/javascript" src="none"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxcheckbox.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxlistbox.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxdropdownlist.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.pager.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.selection.js"></script>	
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.sort.js"></script>		
    <script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script>	
    <script type="text/javascript">
        $(document).ready(function () {
            // prepare the data
            var theme = 'classic';
        
            var source =
            {
                 datatype: "json",
                 datafields:
                 [
		      { name: 'CompanyName', type: 'string'},
	              { name: 'ContactName', type: 'string'},
	     	      { name: 'ContactTitle', type: 'string'},
		      { name: 'Address', type: 'string'},
		      { name: 'City', type: 'string'},
		      { name: 'Country', type: 'string'}
                 ],
         	 cache: false,
	         url: 'data.php',
		 type: "POST",
		 root: 'Rows',
		 beforeprocessing: function(data)
		 {		
			source.totalrecords = data[0].TotalRows;
		 },
		 sort: function()
	  	 {
		    // update the grid and send a request to the server.
		    $("#jqxgrid").jqxGrid('updatebounddata', 'sort');
      	         }
            };		
			
	    var dataadapter = new $.jqx.dataAdapter(source);
            // initialize jqxGrid
            $("#jqxgrid").jqxGrid(
            {
                width: 600,
	        source: dataadapter,
                theme: theme,
	        autoheight: true,
	 	pageable: true,
		virtualmode: true,
		sortable: true,
		rendergridrows: function(params)
		{
		    return params.data;     
		},
                columns: [
                      { text: 'Company Name', datafield: 'CompanyName', width: 250 },
                      { text: 'Contact Name', datafield: 'ContactName', width: 200 },
                      { text: 'Contact Title', datafield: 'ContactTitle', width: 200 },
                      { text: 'Address', datafield: 'Address', width: 180 },
                      { text: 'City', datafield: 'City', width: 100 },
                      { text: 'Country', datafield: 'Country', width: 140 }
                  ]
            });
        });
    </script>
</head>
<body class='default'>
    <div id='jqxWidget'">
        <div id="jqxgrid"></div>
    </div>
</body>
</html>