PDA

View Full Version : MySQL and ExtJS



Tascha
22 Jul 2009, 12:09 AM
Good Morning!

I want to display some IPv4- numbers in a tab with ExtJS. I get the numbers from my MySQL- database.
The IP-numbers are binary, so I have to use the following query to read them:

$query = "SELECT *, ipv4+0 FROM ******** limit 3";
In terminal that works fine, but in the ExtJS- Grid, I only get some small boxes with
unknowable characters.



test_DataStore = new Ext.data.Store({



id: 'test_DataStore',
proxy: new Ext.data.HttpProxy({
url:'database.php',
method: 'POST'
}),
baseParams:{task: "TEST"},
autoLoad: true,
reader: new Ext.data.JsonReader({
root:'results',
totalProperty:'total',
id: 'id'
},[
{name: 'hostname', type:'string', mapping:'hostname'},
{name: 'ipv4', type:'string', mapping:'ipv4'},
{name: 'datetime', type:'string', mapping:'datetime'},
{name: 'profile', type:'string', mapping:'profile'},
{name: 'description', type:'string', mapping:'description'},
{name: 'result', type:'string', mapping:'result'},

]),
sortInfo: {field: 'hostname', direction: "ASC"}



});


test_ListingEditorGrid = new Ext.grid.GridPanel({
id: 'test_ListingEditorGrid',
store: test_DataStore,
columns: [
{header: 'hostname', dataIndex: 'hostname', width: 160},
{header: 'ipv4', dataIndex: 'ipv4', width: 130},
{header: 'datetime', dataIndex:'datetime', width: 150},
{header: 'profile', dataIndex: 'profile', width: 100},
{header: 'description', dataIndex:'description', width: 200},
{header: 'result', dataIndex:'result', width: 200}
],
viewConfig: {forceFit: true}
});

hostname,datetime, etc... works.

Someone has an idea? Perhaps IPv4 isn't a string. Float, double and int didn't work too.

greetings :)

Tascha

Condor
22 Jul 2009, 12:15 AM
Can you post what database.php actually returns?

Tascha
22 Jul 2009, 12:56 AM
Hello, I don't know if this helps. That's only an aperture and I post for first time in that form.
I have to encode a lot. But you see in the last line, that the IP-number arrives rightly or in the right form.




"ipv4":"\n\u0370\n","2":null,"ipv6":null,"3":"*******","profile":"*****","4":"0","*******":"0","5":"**** ***************","command":"*****************","6":"2009-03-25 15:51:37","datetime":"2009-03-25 15:51:37","7":"********************************","description":"**********************
","8":"********************* ' 10.1.1.1 is variably subnetted, **********, ***********\nC *************** is directly connected, ********** ******* is directly connected, ***********'","result":"****************' 10.0.1.0\/8 is variably ************************ **************** is directly connected, Ethernet0\/0\nC **************** is directly connected, ***********'","9":"34.123.456.78","ipv4+0":"12.456.789.123"
I hope you have some apprecition for a newbie. ;)

greetings

Tascha

Tascha
22 Jul 2009, 1:08 AM
My database.php



<?php
mysql_connect("***********", "**********", "**********") or
die("Could not connect: " . mysql_error());
mysql_select_db("************");


$task = "";
if (isset($_POST['task']))
{
$task = $_POST['task'];
}

function JEncode($arr){
if (version_compare(PHP_VERSION,"5.2","<"))
{
require_once("./JSON.php"); //if php<5.2 need JSON class
$json = new Services_JSON();//instantiate new json object
$data=$json->encode($arr); //encode the data in json format
} else
{
$data = json_encode($arr); //encode the data in json format
}
return $data;
}
switch($task){

case "TEST":
get_1_CICLE();
break;

default:
echo "{failure:true}";
break;
}




......



function get_1_Cicle(){
$query = "SELECT *, ipv4+0 FROM ******* limit 3";
$result = mysql_query($query);
$nbrows = mysql_num_rows($result);


if($nbrows>0){
while($rec = mysql_fetch_array($result)){

$arr[] = $rec;
}
$jsonresult = JEncode($arr);
echo '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
}
else
{
echo '({"total":"0", "results":""})';
}
}

?>

steffenk
22 Jul 2009, 2:00 AM
i think this cause the problem:
"ipv4":"\n\u0370\n"

try to convert it to native strings without escape sequences in php

There are also useful functions for ip
php: ip2long, long2ip, ...
MySQL: INET_ATON, INET_NTOA, ...

Tascha
22 Jul 2009, 3:03 AM
I don't understand, what you mean with "try to convert it to native strings without escape sequences in php". Where should I convert it?

I tried to use INET_ATON, INET_NTOA, but nothing happened.
Unless, that the IP-numbers in terminal have dots now ;)

Could it be, that it's due to the utf8-encode?
I don't have any idea.


greetings

Tascha

steffenk
22 Jul 2009, 3:08 AM
I don't understand, what you mean with "try to convert it to native strings without escape sequences in php". Where should I convert it?


in your php. replace \n, \u etc I also don't get what this should be: \n\u0370\n

Tascha
22 Jul 2009, 3:27 AM
There are no \n's and \u's in my php-file.

Or I'm a little bit confused.(:| sorry

steffenk
22 Jul 2009, 3:51 AM
and what is this coming from?
http://extjs.com/forum/showthread.php?p=362046#post362046

Tascha
22 Jul 2009, 3:55 AM
That's the response of the database.php. I can see it in firebug. Sorry, if it wasn't explicit enough.

steffenk
22 Jul 2009, 4:15 AM
hm - i think you don't get my point. Your json data starts with a string having \n\u in it. That's what i try to say. Try to remove this one and see if data is displayed.

Tascha
22 Jul 2009, 4:27 AM
Do you mean this one?
It's from the JSON.php

....



for ($c = 0; $c < $strlen_var; ++$c) {

$ord_var_c = ord($var{$c});

switch (true) {
case $ord_var_c == 0x08:
$ascii .= '\b';
break;
case $ord_var_c == 0x09:
$ascii .= '\t';
break;
case $ord_var_c == 0x0A:
$ascii .= '\n';
break;
case $ord_var_c == 0x0C:
$ascii .= '\f';
break;
case $ord_var_c == 0x0D:
$ascii .= '\r';
break;

case $ord_var_c == 0x22:
case $ord_var_c == 0x2F:
case $ord_var_c == 0x5C:
// double quote, slash, slosh
$ascii .= '\\'.$var{$c};
break;

case (($ord_var_c >= 0x20) && ($ord_var_c <= 0x7F)):
// characters U-00000000 - U-0000007F (same as ASCII)
$ascii .= $var{$c};
break;

case (($ord_var_c & 0xE0) == 0xC0):
// characters U-00000080 - U-000007FF, mask 110XXXXX
// see http://www.cl.cam.ac.uk/~mgk25/unicode.html#utf-8
$char = pack('C*', $ord_var_c, ord($var{$c + 1}));
$c += 1;
$utf16 = $this->utf82utf16($char);
$ascii .= sprintf('\u%04s', bin2hex($utf16));


....
Thanks for your patience!!

steffenk
22 Jul 2009, 4:56 AM
is this the PEAR one?
I use native json_encode all the time

But you can try an easy extracting before


while($rec = mysql_fetch_array($result)){
$arr[] = array_map("extractEscape", $rec);
}
function extractEscape($a) {
return strtr($a, array(
"\n" => '',
"\t" => '',
"\u" => '',
));
}

httpdotcom
22 Jul 2009, 5:15 AM
alias your "ipv4+0" query so that it actually returns an accurate column header and use that as your data mapping field

SELECT *, ipv4+0 as ipv4_ext FROM ******** limit 3
or
select *,inet_ntoa( ipv4 ) as ipv4_addr from ****** limit 3 (if the ip is stored in number format)

Tascha
22 Jul 2009, 5:40 AM
Hmm... both doesn't work...I slowly give up...

Above all I've problems with understanding the context between native json_encode and my JSON.php.

But that's probably going too far.

Hm hm hm.:((

httpdotcom
22 Jul 2009, 5:59 AM
If you're using PHP5, is there any reason you aren't using the native json_encode() and json_decode() functions in PHP (as steffenk suggested)? I see the check against the PHP version in your code, but if you are using PHP5, that check is redundant, and only adds complexity to a simple solution.

//spaghetti code
//- do query
$query = "..." ;
$result = mysql_query( $query ) ;
//- loop through result set
while ( $rec = mysql_fetch_array($result) ) { $rows[] = $rec ; }
//- return results
echo json_encode( array( "total" => count($rows), "results" => $rows ) ) ;


It would probably also help if we could see an actual row in the database so that we knew what to expect in the encoded JSON and what the actual output should look like.

steffenk
22 Jul 2009, 6:05 AM
<ot>i like spaghetti :)</ot>

and make sure you use utf-8, maybe escapes are from wrong multibyte handling.

Tascha
22 Jul 2009, 6:12 AM
But now the response in firebug looks like these:




({"total":"3","results":[{"0":"12.345.67.10","ipv4_addr":"12.345.67.11"},{"0":"10.345.67.11","ipv4_addr":"12.345.99.44"},{"0":"12.345.99.44","ipv4_addr":"12.345.99.44"}]})
Before:




({"total":"3","results":[{"0":"\n\u0375\n","ipv4":"\n\u0375\n"},{"0":"\n\u0380+","ipv4":"\n\u0380+"},{"0":"\n\u0380*","ipv4":"\n\u0380*"}]})

with this query:




select *,inet_ntoa( ipv4 ) as ipv4_addr from ****** limit 3


Perhaps it's an advice?

steffenk
22 Jul 2009, 7:12 AM
this looks fine now. Don't work either? Don't forget to update fieldname in your reader.

Tascha
22 Jul 2009, 10:06 PM
Yeah! It works!!

Thanks a lot :D


greetings

Tascha