jqxGrid with MySQLi

[webwiredMember]

I was wondering if anyone has successfully gotten the Grid to work with MySQLi? I have spent about 6 hours attempting to convert the MySQL to MySQLi in the data.php page,… and if I look at just the data.php page in the browser, It shows the Totalcount and result Rows in Json format, but when I go to the Grid and have it pull up the newly coded page, it just says, No data to display

Here’s my new page code, with the MySQLi

<?php
session_start();
$ExaminerID = $_SESSION['ExaminerID'];
include('connect.php');
$pagenum = $_GET['pagenum'];
$pagesize = $_GET['pagesize'];
$start = $pagenum * $pagesize;
if (!isset($_GET['sortdatafield']))
{
    $query = "SELECT SQL_CALC_FOUND_ROWS OrderTickets.OrderTicketID, OrderTickets.OrderTicketFirstName, OrderTickets.OrderTicketLastName, OrderTickets.OrderTicketDateReceived, OrderTickets.OrderTicketDateRejectedCanceled, Agencies.AgencyName, Agents.AgentFirstName, Agents.AgentLastName, InsuranceCarriers.InsuranceCarrier 
                FROM OrderTickets 
                LEFT JOIN Agencies ON OrderTickets.AgencyID = Agencies.AgencyID
                LEFT JOIN Agents ON OrderTickets.AgentID = Agents.AgentID
                LEFT JOIN InsuranceCarriers ON OrderTickets.InsuranceCarrierID = InsuranceCarriers.InsuranceCarrierID
                WHERE ((ExaminerID = 0 OR ExaminerID = ?) AND OrderTicketCurrentStatus = 'Rejected') LIMIT ?, ?";
    $stmt = $mysqli->prepare($query);
    $stmt->bind_param("iii", $ExaminerID, $start, $pagesize);
    $stmt->execute();
    $stmt->bind_result($OrderTicketID, $OrderTicketFirstName, $OrderTicketLastName, $OrderTicketDateReceived, $OrderTicketDateRejectedCanceled, $AgencyName, $AgentFirstName, $AgentLastName, $InsuranceCarrier);
    while ($stmt -> fetch()) {
            $customers[] = array(
            'OrderTicketID' => $OrderTicketID,
            'ClientName' => $OrderTicketLastName .', '. $OrderTicketFirstName,
            'AgencyName' => $AgencyName,
            'AgentName' => $AgentLastName .', '. $AgentFirstName,
            'InsuranceCarrier' => $InsuranceCarrier,
            'OrderTicketDateReceived' => $OrderTicketDateReceived,
            'OrderTicketDateRejectedCanceled' => $OrderTicketDateRejectedCanceled
          );
    }
    $sql = "SELECT FOUND_ROWS()";
    $stmt = $mysqli->prepare($sql);
    $stmt->execute();
    $stmt->bind_result($total_rows);
    $stmt->fetch();
}
if (isset($_GET['sortdatafield']))
{
    $sortfield = $_GET['sortdatafield'];
    if ($sortfield == "ClientName") {
        $sortfield = "OrderTicketLastName";
    }
    if ($sortfield == "AgentName") {
        $sortfield = "AgentLastName";
    }
    $sortorder = $_GET['sortorder'];
    if ($sortfield != NULL)
    {
        if ($sortorder == "desc")
        {
                $query = "SELECT OrderTickets.*, Agencies.AgencyName, Agents.AgentFirstName, Agents.AgentLastName, InsuranceCarriers.InsuranceCarrier
                    FROM OrderTickets
                    LEFT JOIN Agencies ON OrderTickets.AgencyID = Agencies.AgencyID
                    LEFT JOIN Agents ON OrderTickets.AgentID = Agents.AgentID
                    LEFT JOIN InsuranceCarriers ON OrderTickets.InsuranceCarrierID = InsuranceCarriers.InsuranceCarrierID
                    WHERE ((ExaminerID = 0 OR ExaminerID = ?) AND OrderTicketCurrentStatus = 'Rejected') ORDER BY ? DESC LIMIT ?, ?";
        }
        else if ($sortorder == "asc")
        {
                $query = "SELECT OrderTickets.*, Agencies.AgencyName, Agents.AgentFirstName, Agents.AgentLastName, InsuranceCarriers.InsuranceCarrier
                    FROM OrderTickets
                    LEFT JOIN Agencies ON OrderTickets.AgencyID = Agencies.AgencyID
                    LEFT JOIN Agents ON OrderTickets.AgentID = Agents.AgentID
                    LEFT JOIN InsuranceCarriers ON OrderTickets.InsuranceCarrierID = InsuranceCarriers.InsuranceCarrierID
                    WHERE ((ExaminerID = 0 OR ExaminerID = ?) AND OrderTicketCurrentStatus = 'Rejected') ORDER BY ? ASC LIMIT ?, ?";
        }	
        $stmt = $mysqli->prepare($query);
        $stmt->bind_param("isii", $ExaminerID, $sortfield, $start, $pagesize);
        $stmt->execute();
        $stmt->bind_result($OrderTicketID, $OrderTicketFirstName, $OrderTicketLastName, $OrderTicketDateReceived, $OrderTicketDateRejectedCanceled, $AgencyName, $AgentFirstName, $AgentLastName, $InsuranceCarrier);	
        while ($stmt -> fetch()) {
                $customers[] = array(
                'OrderTicketID' => $OrderTicketID,
                'ClientName' => $OrderTicketLastName .', '. $OrderTicketFirstName,
                'AgencyName' => $AgencyName,
                'AgentName' => $AgentLastName .', '. $AgentFirstName,
                'InsuranceCarrier' => $InsuranceCarrier,
                'OrderTicketDateReceived' => $OrderTicketDateReceived,
                'OrderTicketDateRejectedCanceled' => $OrderTicketDateRejectedCanceled
              );
        }
        $sql = "SELECT FOUND_ROWS()";
        $stmt = $mysqli->prepare($sql);
        $stmt->execute();
        $stmt->bind_result($total_rows);
        $stmt->fetch();		
    }
}
$data[] = array(
    'TotalRows' => $total_rows,
    'Rows' => $customers
    );
echo json_encode($data);
?>

[Peter StoevKeymaster]

Hi webwired,

I suggest you to debug your PHP page and check the result that json_encode returns to the client. In addition, you can use the jqxDataAdapter’s helpful callbacks for debugging – loadError and loadComplete. For more information about them, look at: http://www.jqwidgets.com/jquery-widgets-documentation/documentation/jqxdataadapter/jquery-data-adapter.htm

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com/

[webwiredMember]

I did debug on the data.php page, that’s what I said up top, that the data comes out perfectly and exactly the same, in the browser, as when I pull it up with the original mysql syntax, here’s an example of what it puts out …

[{“TotalRows”:2,”Rows”:[{“OrderTicketID”:52,”ClientName”:”*****, *****”,”AgencyName”:”John Nowak Agency”,”AgentName”:”Nowak, John”,”InsuranceCarrier”:”Nationwide Life Insurance”,”OrderTicketDateReceived”:”2013-07-31″,”OrderTicketDateRejectedCanceled”:”2013-10-10″},{“OrderTicketID”:68,”ClientName”:”*****, *****”,”AgencyName”:”John Nowak Agency”,”AgentName”:”Nowak, John”,”InsuranceCarrier”:”Nationwide Life Insurance”,”OrderTicketDateReceived”:”2013-08-12″,”OrderTicketDateRejectedCanceled”:”2013-10-10″}]}]

Isn’t that perfectly formatted json for the Grid page to consume?

I just took a look at the jqxDataAdapter documenation, I’ll try to implement that tomorrow, I’ll post back.

[webwiredMember]

Well, I had a look at the jqxDataAdapter documentation, and from what I can tell from the examples, it only works by showing HTML Tables…

I’m sorry to say that after careful consideration, I will not be continuing to use jqxwidgets products, for the lack of current standards integration and documentation, and the lack of support (as in unanswered forum posts).

[Peter StoevKeymaster]

Hi webwired,

jqxDataAdapter works with JSON, JSONP, XML, TSV, CSV and Array. And its output is always Array. It is not the jqxDataAdapter’s job to render a Table. The documentation page points how you can Read data from the jqxDataAdapter and Load data into it.

I am sorry to learn that you will not use jQwidgets and thank you about your opinion about the Support Team. Unfortunately, it seems that we still do not have the specific sample for that you are looking for. However, I prepared a sample with MySQLI as a proof that our widget works with it:

Index.php

<!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.10.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/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()
				{
					  return dataadapter.records;     
				},
                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>

connect.php

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

data.php

<?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();
?>

Best Wishes,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com/

[Ben1Participant]

Hello Guys,

I am using a fresh download of the jqwidgets zip files with demos, version 3.9.1.

I have been trying to do these PHP MySQL data binding examples but I keep getting the same result… no data is displayed. I do see it working on your demos, but when I try to replicate this on my local server, it just does not work. I tried the previous example that Peter describes and it gives the same results. Ultimately, In the debug process I think I should be able to browse to the URL for the “data.php” file and which should “echo” the data in JSON format.

Instead the browser display a portion of the “data.php” file. as follows:

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(); ?>

Your help would be appreciated.
Thanks,
Ben

[Peter StoevKeymaster]

Hi Ben,

First test whether your server returns any data and then try to build a page with a Grid which binds to that data. That is the advice I can give you.

Best Wishes,
Peter Stoev
jQWidgets Team
http://www.jqwidgets.com/

[Ben1Participant]

Hi Peter and thanks for the response. I should have mentioned that I had confirmed that the MySQL server was returning data to other applications, just not within the browser using the example files. The PHP Mysql binding examples are fairly straight forward so that is why I started there. I have never used the data.php method and it was a bit hard to diagnose that at first, but that was obstacle. It had nothing to do with the grid.

I made progress and was able to get the examples I was testing to work…

To run these tests I have been using XAMPP v3.2.2. I installed the same day I started my testing. In short the PHP/MSQLi functionality was not performing as expected. It had nothing to do with the JQwidgets technology.

I am correct about the the expected output of the data.php file for the simpler JQwidget examples. See my post above. You should be able to browse to the data.php file and the browser should display JSON encoded text contents (of something like)…

[{"CompanyName":"Alfreds Futterkiste","ContactName":"Maria Anders","ContactTitle":"Sales Representative","Address":"Obere Str. 57","City":"Berlin"},{"CompanyName":"Ana Trujillo Emparedados y helados","ContactName":"Ana Trujillo","ContactTitle":"Owner","Address":"Avda. de la Constitucin 2222","City":"Mxico D.F."},{"CompanyName":"Antonio Moreno Taquera","ContactName":"Antonio Moreno","ContactTitle":"Owner","Address":"Mataderos 2312","City":"Mxico D.F."},{"CompanyName":"Around the Horn","ContactName":"Thomas Hardy","ContactTitle":"Sales Representative","Address":"120 Hanover Sq.","City":"London"},{"CompanyName":"Berglunds snabbkp","ContactName":"Christina Berglund","ContactTitle":"Order Administrator","Address":"Berguvsvgen 8","City":"Lule"},{"CompanyName":"Blauer See Delikatessen","ContactName":"Hanna Moos","ContactTitle":"Sales Representative","Address":"Forsterstr. 57","City":"Mannheim"},{"CompanyName":"Blondesddsl pre et fils","ContactName":"Frdrique Citeaux","ContactTitle":"Marketing Manager","Address":"24, place Klber","City":"Strasbourg"},{"CompanyName":"Blido Comidas preparadas","ContactName":"Martn Sommer","ContactTitle":"Owner","Address":"C\/ Araquil, 67","City":"Madrid"},{"CompanyName":"Bon app'","ContactName":"Laurence Lebihan","ContactTitle":"Owner","Address":"12, rue des Bouchers","City":"Marseille"},{"CompanyName":"Bottom-Dollar Markets","ContactName":"Elizabeth Lincoln","ContactTitle":"Accounting Manager","Address":"23 Tsawassen Blvd.","City":"Tsawassen"},{"CompanyName":"B's Beverages","ContactName":"Victoria Ashworth","ContactTitle":"Sales Representative","Address":"Fauntleroy Circus","City":"London"},{"CompanyName":"Cactus Comidas para llevar","ContactName":"Patricio Simpson","ContactTitle":"Sales Agent","Address":"Cerrito 333","City":"Buenos Aires"},{"CompanyName":"Centro comercial Moctezuma","ContactName":"Francisco Chang","ContactTitle":"Marketing Manager","Address":"Sierras de Granada 9993","City":"Mxico D.F."},{"CompanyName":"Chop-suey Chinese","ContactName":"Yang Wang","ContactTitle":"Owner","Address":"Hauptstr. 29","City":"Bern"},{"CompanyName":"Comrcio Mineiro","ContactName":"Pedro Afonso","ContactTitle":"Sales Associate","Address":"Av. dos Lusadas, 23","City":"Sao Paulo"},{"CompanyName":"Consolidated Holdings","ContactName":"Elizabeth Brown","ContactTitle":"Sales Representative","Address":"Berkeley Gardens 12 Brewery","City":"London"},{"CompanyName":"Drachenblut Delikatessen","ContactName":"Sven Ottlieb","ContactTitle":"Order Administrator","Address":"Walserweg 21","City":"Aachen"},{"CompanyName":"Du monde entier","ContactName":"Janine Labrune","ContactTitle":"Owner","Address":"67, rue des Cinquante Otages","City":"Nantes"},{"CompanyName":"Eastern Connection","ContactName":"Ann Devon","ContactTitle":"Sales Agent","Address":"35 King George","City":"London"},{"CompanyName":"Ernst Handel","ContactName":"Roland Mendel","ContactTitle":"Sales Manager","Address":"Kirchgasse 6","City":"Graz"},{"CompanyName":"Familia Arquibaldo","ContactName":"Aria Cruz","ContactTitle":"Marketing Assistant","Address":"Rua Ors, 92","City":"Sao Paulo"},{"CompanyName":"FISSA Fabrica Inter. Salchichas S.A.","ContactName":"Diego Roel","ContactTitle":"Accounting Manager","Address":"C\/ Moralzarzal, 86","City":"Madrid"},{"CompanyName":"Folies gourmandes","ContactName":"Martine Ranc","ContactTitle":"Assistant Sales Agent","Address":"184, chausse de Tournai","City":"Lille"},{"CompanyName":"Folk och f HB","ContactName":"Maria Larsson","ContactTitle":"Owner","Address":"kergatan 24","City":"Brcke"},{"CompanyName":"Frankenversand","ContactName":"Peter Franken","ContactTitle":"Marketing Manager","Address":"Berliner Platz 43","City":"Mnchen"},{"CompanyName":"France restauration","ContactName":"Carine Schmitt","ContactTitle":"Marketing Manager","Address":"54, rue Royale","City":"Nantes"},{"CompanyName":"Franchi S.p.A.","ContactName":"Paolo Accorti","ContactTitle":"Sales Representative","Address":"Via Monte Bianco 34","City":"Torino"},{"CompanyName":"Furia Bacalhau e Frutos do Mar","ContactName":"Lino Rodriguez","ContactTitle":"Sales Manager","Address":"Jardim das rosas n. 32","City":"Lisboa"},{"CompanyName":"Galera del gastrnomo","ContactName":"Eduardo Saavedra","ContactTitle":"Marketing Manager","Address":"Rambla de Catalua, 23","City":"Barcelona"},{"CompanyName":"Godos Cocina Tpica","ContactName":"Jos Pedro Freyre","ContactTitle":"Sales Manager","Address":"C\/ Romero, 33","City":"Sevilla"}]

Peter’s example files also work (in his explanation above). But, Peter’s example data.php file is a bit more complex and does not return the JSON encoded data, if browsed directly. But, it works fine in conjunction with the rest of its example files.

Cause of the issue…The data.php file is using MSQLi methodology to connect to the database but was having issues on my system yesterday. I suspect the XAMPP PHP functionality was not working right and was not displaying any useful information. I found a suggestion on the Internet from someone else that the XAMPP PHP ext folder may need to be given read permissions to the /User. Mine did already have the READ permissions, so I gave my /User all permissions to this folder (test environment only) and the data.php file is working now.

I know this is a long winded explanation, but I am hoping some of the OTHER PHP testers having a similar issue can benefit from this info.
Regards,
Ben

[jcansellParticipant]

Hi Peter,
Can you explain what the ‘ii’ does in to following statement in your example:

$result->bind_param(‘ii’, $start, $pagesize);

I can see that $start, $pagesize replace the ?, ? in the $mysqli->prepare( statement, but google is not helping me understand the ‘ii’ parameter.

many thanks,
jonathan

[Peter StoevKeymaster]

Hi Jonathan,

I would suggest you to take a look at this manual http://php.net/manual/en/mysqli-stmt.bind-param.php

Best Wishes,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com/

[Author]

Posts