Binding jqxGrid to JSON using PHP - Angular, Vue, React, Web Components, Javascript, HTML5 Widgets

Tagged: angular grid, binding, datafields, grid, jquery grid, jqxgrid, json, mssql, name, php

This topic contains 2 replies, has 2 voices, and was last updated by christophe.toussaint 7 years, 10 months ago.

Viewing 3 posts - 1 through 3 (of 3 total)

Author

Binding jqxGrid to JSON using PHP Posts

November 19, 2016 at 9:47 pm Binding jqxGrid to JSON using PHP #89169

Participant

Hello,
I carefully studied the example given in the demo and tried to reproduce it but using MSSQL server query.
For that, I’m using 3 different pages:
connect.php :

<?php
	$hostname = "localhost";
	$database = "test";
	$username = "sa";
	$password = "azerty";

	//SQL connection info
	$ConnectionInfo = array( "Database"=>$database, "UID"=>$username, "PWD"=>$password );
	//Connect to the database
	$conn = sqlsrv_connect( $hostname, $ConnectionInfo );
	//If connection problem then display "Connection to the server failed !"
	if ($conn === false)
	{
		echo '<script language="javascript">';
		echo 'alert("Connection to the server failed !")';
		echo '</script>';
		die( print_r( sqlsrv_errors(), true ));
		exit;
	}
?>

data.php :

<?php
// ---------------------------------------------------------
//		SQL server connection and query
//----------------------------------------------------------

	//Include the connect.php file
	include('connect.php');

	// get data and store in a json array
	$query = "select * from DataSelectionStatement";
	$result = sqlsrv_query( $conn, $query );
	if( $result === false) 
	{
		echo '<script language="javascript">';
		echo 'alert("A problem in the query is detected!")';
		echo '</script>';
		die( print_r( sqlsrv_errors(), true) );
		exit();
	}
	while( $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_ASSOC ) ) 
		{
			$listdbname[] = array('Name' => $row['Name'], 'SessionBased' => $row['SessionBased'], 'TestBased' => $row['TestBased']);
		}

	header("Content-type: application/json"); 
	echo json_encode($listdbname);

	// Frees all resources
	sqlsrv_free_stmt( $result);
?>

And the last index.php :

<html>
	<head>
		<title>Example008-ConnectSQLServerAndPopulatejqxGrid</title>
		<link rel="stylesheet" href="jqwidgets/styles/jqx.base.css" type="text/css" />
		<script type="text/javascript" src="scripts/jquery-1.11.1.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/jqxdropdownlist.js"></script>
		<script type="text/javascript" src="jqwidgets/jqxmenu.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" src="jqwidgets/jqxgrid.columnsresize.js"></script> 
		<script type="text/javascript" src="jqwidgets/jqxdata.js"></script> 

		<script type="text/javascript">
			$(document).ready(function () {
				var url = "data.php";
				// prepare the data
				var source =
				{
					datatype: "json",
					datafields: 
					[
					{ name: 'Name' },
					{ SessionBased: 'SessionBased' },
					{ TestBased: 'TestBased' }
					],
					url: url
					
				};
				var dataAdapter = new $.jqx.dataAdapter(source);
				$("#jqxgrid").jqxGrid(
				{
					width: 850,
					source: dataAdapter,
					columnsresize: true,
					columns: 
					[
					  { text: 'Name', datafield: 'Name', width: 250 },
					  { text: 'Session Based', datafield: 'SessionBased', width: 250 },
					  { text: 'Test Based', datafield: 'TestBased', width: 180 }
					]
				});
			});
		</script>
	</head>
	<body>
		<div id="jqxgrid"></div>
	</body>
</html>

When I check the json done by data.php, I receive that and it’s perfect:
[{"Name":"Fin d'abonnement","SessionBased":" AND Sessions.SessionId IN ( Select Sessions.SessionId from Sessions, FileList where Sessions.FileId = FileList.FileId and FileList.CollectionName IN ('Fin abonnement')) AND Sessions.SessionId <= (Select LastProcessedSessionId from SQSystem) ","TestBased":" AND Sessions.SessionId IN ( Select Sessions.SessionId from Sessions, FileList where Sessions.FileId = FileList.FileId and FileList.CollectionName IN ('Fin abonnement')) AND Sessions.SessionId <= (Select LastProcessedSessionId from SQSystem) "},{"Name":"Part 10 valid test AtoB","SessionBased":" AND Sessions.SessionId IN ( Select Sessions.SessionId from Sessions, FileList where Sessions.FileId = FileList.FileId and FileList.CollectionName IN ('Part 10 20161017')) AND Sessions.SessionId IN ( Select SessionId from TestInfo where TestInfo.Direction IN ('A->B')) AND Sessions.SessionId IN ( Select SessionId from TestInfo WHERE Valid = 1) AND Sessions.SessionId <= (Select LastProcessedSessionId from SQSystem) ","TestBased":" AND Sessions.SessionId IN ( Select Sessions.SessionId from Sessions, FileList where Sessions.FileId = FileList.FileId and FileList.CollectionName IN ('Part 10 20161017')) AND TestInfo.TestId IN ( Select TestId from TestInfo where TestInfo.Direction IN ('A->B')) AND TestInfo.TestId IN ( Select TestId from TestInfo WHERE Valid = 1) AND Sessions.SessionId <= (Select LastProcessedSessionId from SQSystem) "}]

If I copy/paste this json result in a text file and try to populate jqxGrid with the file (using the demo example “Binding to JSON“, it’s working perfectly and all my 3 columns are correctly populated (the first one with the name, the second and third ones with some parts of SQL query – that’s good because it’s really what I have in the SQL table).

Now, when I want to populate it directly from the JSON done with data.php, only the first column “Name” is populated, the 2 others are empty.
Why? What should I do to populate all columns?

Best regards,
Christophe Toussaint

November 21, 2016 at 7:56 am Binding jqxGrid to JSON using PHP #89175

Dimitar

Participant

Hello Christophe,

Your datafields array is not correctly set. Please use the following modification:

datafields: [{
  name: 'Name'
}, {
  name: 'SessionBased'
}, {
  name: 'TestBased'
}],

Best Regards,
Dimitar

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

November 21, 2016 at 3:33 pm Binding jqxGrid to JSON using PHP #89195

christophe.toussaint

Participant

Hello Dimitar,
Thank you! You’re right…
Best regards,
Christophe Toussaint

Author

Posts

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.