What is wrong? MSSQL + PHP+ JSON + GRID [Archive]

elynegrao

28 Jun 2007, 6:00 AM

Hi all...

I'm trying to use MSSQL + PHP+ JSON + GRID but i'm not success.

here is my php code:

mssql_connect("myhost","myuser","mypass");
mssql_select_db("intranet");

$sql = "SELECT * FROM USUARIO";
$query = mssql_query($sql);
$total = mssql_num_rows($query);

echo '({"total":'.$total.',"results":[';

while($row = mssql_fetch_array($query)){
echo json_encode($row);
}

echo ']})';

My.js

Ext.onReady(function(){
var ds = new Ext.data.Store({
proxy: new Ext.data.HttpProxy({
url: 'teste.php'
}),

reader: new Ext.data.JsonReader({
root: 'results',
totalProperty: 'total',
id: 'ID'

}, [
{name: 'LOGIN', mapping: 'LOGIN'},
{name: 'ID', mapping: 'ID'}
])

});

var cm = new Ext.grid.ColumnModel([{
id: 'LOGIN',
header: "Nome",
dataIndex: 'LOGIN',
width: 150
},{

header: "Codigo",
dataIndex: 'ID',
width: 100
}]);

var grid = new Ext.grid.Grid('grid-paging', {
ds: ds,
cm: cm,
trackMouseOver: true,
loadMask: true
});

grid.render();

ds.load();

});

Here is the output json_encode($row).

({"total":3,"results":[{"0":"1000","ID":"1000","1":"eliezer ","LOGIN":"eliezer ","2":"asd ","SENHA":"asd ","3":9,"NIVEL":9,"4":"Ely Negrao","NOME":"Ely Negrao","5":"9797","RAMAL":"9797","6":"eli@asdda.com","EMAIL":"eli@asdda.com","7":"Informatica ","DPTO":"Informatica ","8":"FCV ","MENU_SHOW":"FCV "}{"0":"1001","ID":"1001","1":"vendas ","LOGIN":"vendas ","2":"asd ","SENHA":"asd ","3":1,"NIVEL":1,"4":"Grupo Vendas","NOME":"Grupo Vendas","5":"0000","RAMAL":"0000","6":null,"EMAIL":null,"7":"Vendas ","DPTO":"Vendas ","8":"V ","MENU_SHOW":"V "}{"0":"1002","ID":"1002","1":"gkghjkgjk ","LOGIN":"gkghjkgjk ","2":"ghjkghjk ","SENHA":"ghjkghjk ","3":1,"NIVEL":1,"4":"dfghdfgh","NOME":"dfghdfgh","5":"3453","RAMAL":"3453","6":null,"EMAIL":null,"7":"dddd ","DPTO":"dddd ","8":"F ","MENU_SHOW":"F "}]})

Here my HTML page

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>Introduction to Ext: Starter Page</title>


<script type="text/javascript" src="../adapter/yui/yui-utilities.js"></script>
<script type="text/javascript" src="../adapter/yui/ext-yui-adapter.js"></script>
<script type="text/javascript" src="../ext-all-debug.js"></script>
<script type="text/javascript" src="ExtStart.js"></script>


<link rel="stylesheet" type="text/css" href="../resources/css/ext-all.css">
<link rel="stylesheet" type="text/css" href="../resources/css/ytheme-vista.css">
<link rel="stylesheet" type="text/css" href="ExtStart.css">
</head>
<body>
<h1> </h1>

<p> </p>
<div id="grid-paging"> </div>
<p>  </p>
</body>
</html>

The problem is, i dont see anything on screen. :)

sorry my english.

matjaz

28 Jun 2007, 6:12 AM

Your JSON string isn't valid.

elynegrao

28 Jun 2007, 6:15 AM

Why?

trbot

28 Jun 2007, 6:40 AM

The first thing I would do if I were trying to make this work, is to eliminate a few points of failure and simplify the example as much as possible.

If you are only going to test two fields, create JSON that only contains those two fields.

Also, the format of your JSON has a few problems:
1. You need commas after each row. Eg:
'rows': [{id: 'test', pass: 'testpass'}, {id: 'test2', pass: 'testpass2'}, ... ]
2. I'm not 100% sure but I don't think you can have fields that are named numerically. I don't think you can have a field called "0", or a field called "1"... I'm pretty sure all variables must start with a letter or an underscore...

VinylFox

28 Jun 2007, 6:41 AM

1) you should be passing json_encode an object, not an array straight from mysql....you can pass json_encode an associative array, but the mysql functions create two associations for each item, and thats just a waste to pass all your data twice.

2) each row of data you encode needs to be separated by a comma.

just use the code from here (http://extjs.com/learn/Tutorial:Creating_JSON_Data_in_PHP), that will solve all your problems.

elynegrao

28 Jun 2007, 7:14 AM

1) you should be passing json_encode an object, not an array straight from mysql....you can pass json_encode an associative array, but the mysql functions create two associations for each item, and thats just a waste to pass all your data twice.

2) each row of data you encode needs to be separated by a comma.

just use the code from here (http://extjs.com/learn/Tutorial:Creating_JSON_Data_in_PHP), that will solve all your problems.
Tks man!!!
but is possible too by using an array i just add a ',' between the rows.

Now my code is

mssql_connect("myhost","myuser","mypass");
mssql_select_db("intranet");

$sql = "SELECT * FROM USUARIO";
$query = mssql_query($sql);
$total = mssql_num_rows($query);
$ii = 1; //i add this var to count how much ',' must be printed after each row.
echo '({"total":'.$total.',"results":[';

while($row = mssql_fetch_array($query)){
echo json_encode($row);
if($ii < $total){
echo ",";
$ii++;
}
}
echo ']})';

All works fine now. Tks guys!!

VinylFox

28 Jun 2007, 7:30 AM

Tks man!!!
but is possible too by using an array i just add a ',' between the rows.

I didnt say it was not possible, in fact I said just the opposite. Your returning two pieces of data for each column, one for the numerical reference, and one for the named reference. To top it off, you will never be able to access the numerical reference because of the reason trbot gave, JS vars cannot start with a numeral.

elynegrao

28 Jun 2007, 9:02 AM

I didnt say it was not possible, in fact I said just the opposite. Your returning two pieces of data for each column, one for the numerical reference, and one for the named reference. To top it off, you will never be able to access the numerical reference because of the reason trbot gave, JS vars cannot start with a numeral.

Ok! :)

azid

1 Jul 2007, 6:29 AM

Use mssql_fetch_assoc if you don't want the numerical indexes (http://no.php.net/mssql_fetch_assoc)

~o)

elynegrao

2 Jul 2007, 11:45 AM

:-? ok!