jQuery UI Widgets Forums Grid Binding jqxGrid to JSON using PHP

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

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
  • Binding jqxGrid to JSON using PHP #89169

    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

    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/

    Binding jqxGrid to JSON using PHP #89195

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

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

You must be logged in to reply to this topic.