In this blog post, we will demonstrate 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’.
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.
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.
The post Data Binding with PHP’s mysqli Extension appeared first on Javascript, HTML5, jQuery Widgets.