Sigma Grid 2.1 Tutorial 2 - Load data from database

steven

Moderator
Hero Member

Posts: 544

Sigma Grid 2.1 Tutorial 2 - Load data from database

« on: July 17, 2008, 03:51:05 AM »

In tutorial 1, we have talked about how to present data existing at client. Actually, we need to retrieve data from server/database. In this section, we Will be concerned about how to load data from the server. You are supposed to have basic knowledge of PHP.

We will get started with mydemo1.html, production of tutorial 1, which contains a grid running well. If you don't have mydemo1.html in hand, I recommand you to read tuorial 1 and get one copy. Before starting, we save mydemo1.html as mydemo2.html.

Firstly, we need to create a new php file for data feed, say mydemo2.php. The file structure looks like

Secondly, we amend the code in mydemo2.html. Since we will take data from the server, data definition in mydemo2.html is not necessary. So we move everything about variable data1 to mydemo2.php, but keep the schema in place.
Well we have data1 in mydemo2.php. How can javascript exist in php file? I am sorry, I forget to change them to be in line with php. Here are codes changed.

Then we need to change loadURL and make it point to mydemo.php.

As you noticed, we replace getData function with a real url. Since getData function will not be in use, we should remove it or comment it out. By far, mydemo2.html reads

<html>  
<head>  
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />  
<meta http-equiv="Pragma" content="no-cache" />  
<meta http-equiv="Expires" content="-1" />  
<meta http-equiv="Cache-Control" content="no-cache" />  
<title>Sigma Grid Tutorial 1</title>  
  
<link rel="stylesheet" type="text/css" href="grid/gt_grid.css" />  
<script type="text/javascript" src="grid/gt_msg_en.js"></script>
<script type="text/javascript" src="grid/gt_const.js"></script>
<script type="text/javascript" src="grid/gt_grid_all.js"></script>

var colsOption = [
     {id: 'serialNo' , header: "Order No" , width :60 },
     {id: 'employee' , header: "Employee" , width :80  },
	   {id: 'country' , header: "Country" , width :70  },
	   {id: 'customer' , header: "Customer" , width :80  },
	   {id: 'order2005' , header: "2005" , width :60},
	   {id: 'order2006' , header: "2006" , width :60},
	   {id: 'order2007' , header: "2007" , width :60},
	   {id: 'order2008' , header: "2008" , width :60},
	   {id: 'lastDate' , header: "Delivery Date" , width :100}
       
];

var gridOption={
	id : "grid1",
	loadURL : "mydemo2.php",
	container : 'grid1_container', 
	dataset : dsOption ,
	columns : colsOption
};

var mygrid=new Sigma.Grid(gridOption);
Sigma.Util.onLoad( Sigma.Grid.render(mygrid) );
  
</script>  
</head>  
  
<body>  
  
<div id="grid1_container" style="width:700px;height:300px">  
</div>  
  
 </body>  
</html>

Copy the mydemo2.php, mydemo.html together with grid directory to a apache web directory, say /sigma_test, and then type http://localhost/sigma_test/mydemo2.php in your browser, you will see grid show up with the data. This time, grid really pulls data from server side.


« Last Edit: November 28, 2008, 05:18:05 AM by steven »	Logged

Sigma AJAX Data Grid - Easy to Integrate with PHP, ASP Classic, ASP.NET & Java
Sigma PHP Ajax Framework - WYSIWYG GUI Builder With Tree, Grid, Treegrid, Dialog, Tab & More Component

steven

Moderator
Hero Member

Posts: 544

Re: Sigma Grid 2.1 Tutorial 2

« Reply #1 on: July 21, 2008, 01:14:59 AM »

It is straightforward to pull whole data from database to grid for showing. When the dataset comes to large, it's not a good idea. At this time, you need paginal output. Sigma grid enables easy paging through data. I think most of you have been bored with false data above, and it's time to set up a database to get some real stuff.

Before we make a paginal grid, we need to learn some knowledges between grid and server. The paging process is: When a paging button(first, previous, next and last) is pressed, sigma grid will send paging relevant info to server.
Here is a sample grid posts to server.

The most import parameter is pageNum, which means page index grid is asking for. In the sample above, end user attempts to go to the second page. With the following 2 lines, we can get the page index.

I would like to highlight three point.
1. All the posted info is contained by post variable _gt_json in json string.
2. Note that function json_decode, supported since php5.24(If I am correct), is used to convert json string to php object.
3. Be sure to remove extra slashes by calling function stripslashes.

Once we get page index, we can retrieve the right data from database. If you are using MySQL, you could use key limit, or something like that, to get slice of data. If we suppose every page holds 10 records, we get the code as follows.

This time, we use json_encode to convert php object back to json string. Thank php for these two functions. If you are using older version without these two ones supported, you need extra model for them.


« Last Edit: November 28, 2008, 05:13:33 AM by steven »	Logged

Sigma AJAX Data Grid - Easy to Integrate with PHP, ASP Classic, ASP.NET & Java
Sigma PHP Ajax Framework - WYSIWYG GUI Builder With Tree, Grid, Treegrid, Dialog, Tab & More Component

steven

Moderator
Hero Member

Posts: 544

Re: Sigma Grid 2.1 Tutorial 2

« Reply #2 on: July 21, 2008, 02:19:32 AM »

Another thing you should let grid know is how many records in total so that grid can stop end user pressing next button when it comes to the last.

The last thing we need do is to combine records and total records number to make a json string.

The output should be consist with declaration at http://www.sigmawidgets.com/products/sigma_grid2/docs/dataexchange.html.


« Last Edit: November 28, 2008, 05:12:30 AM by steven »	Logged

Sigma AJAX Data Grid - Easy to Integrate with PHP, ASP Classic, ASP.NET & Java
Sigma PHP Ajax Framework - WYSIWYG GUI Builder With Tree, Grid, Treegrid, Dialog, Tab & More Component

steven

Moderator
Hero Member

Posts: 544

Re: Sigma Grid 2.1 Tutorial 2 - Load data from database

« Reply #3 on: July 21, 2008, 02:45:55 AM »

Put all php code together, we will get the following lines in mydemo2.php. Put mydemo2.php and mydemo2.html, as well as grid package, to apache web directory, you will see a grid with paging features.

<?php
header('Content-type:text/javascript;charset=UTF-8');

$json=json_decode(stripslashes($_POST["_gt_json"]));
$pageNo = $json->{'pageInfo'}->{'pageNum'};
$pageSize = 10;//10 rows per page

mysql_connect("localhost","root","") or
    die("Could not connect: " . mysql_error());
mysql_select_db("sigma_grid_server") or 
    die("Could not select database: " . mysql_error());
	
//to get how many records totally.
$sql = "select count(*) as cnt from orders";
$handle = mysql_query($sql);
$row = mysql_fetch_object($handle);
$totalRec = $row->cnt;

//make sure pageNo is inbound
if($pageNo<1||$pageNo>ceil(($totalRec/$pageSize))){
  $pageNo = 1;
}

//pageno starts with 1 instead of 0
$sql = "select * from orders limit " . ($pageNo - 1)*$pageSize . ", " . $pageSize;
$handle = mysql_query($sql);	
$retArray = array();
while ($row = mysql_fetch_object($handle)) {
  $retArray[] = $row;
}

$data = json_encode($retArray);
$ret = "{data:" . $data .",\n";
$ret .= "pageInfo:{totalRowNum:" . $totalRec . "},\n";
$ret .= "recordType : 'object'}";
echo $ret;


« Last Edit: November 28, 2008, 05:11:27 AM by steven »	Logged

Sigma AJAX Data Grid - Easy to Integrate with PHP, ASP Classic, ASP.NET & Java
Sigma PHP Ajax Framework - WYSIWYG GUI Builder With Tree, Grid, Treegrid, Dialog, Tab & More Component

alabdulelah

Jr. Member

Posts: 9

Re: Sigma Grid 2.1 Tutorial 2 - Load data from database

« Reply #4 on: January 09, 2009, 08:29:35 AM »

Hi,
I need HELP please regarding the loading of the data from the database. I am receiving an error which I cann't find solution to it.. the error is --> LOAD Failed! Exception :XMLHttpRequest Status :500 <---. I have the Apache working properly MySql working properly. I tried everything I know but I cann't find a reason for this error.
I really hope you can help me with that.

I am using the same code of mydemo2.php and mydemo2,html, ofcourse I changed the database info according to my settings which works 100% fine with my other webpages on my local server.

Thanks in advance


	Logged

humi

Hero Member

Posts: 284

Re: Sigma Grid 2.1 Tutorial 2 - Load data from database

« Reply #5 on: January 09, 2009, 09:08:34 AM »

If you have any .htaccess it can cause error 500. It means there is a server configuration fault. Usually in the page's header.

Not sure how else to help. Are you running the grid on a password protected page?


« Last Edit: January 09, 2009, 09:11:07 AM by humi »	Logged

alabdulelah

Jr. Member

Posts: 9

Re: Sigma Grid 2.1 Tutorial 2 - Load data from database

« Reply #6 on: January 09, 2009, 09:18:56 AM »

Hi,
I am not running it under any password protection or .htaccess. All I am trying to do is to try and learn how to use and control the Grid. I am following the Tutorial steps and it's not working...

I have all the websites working properly... I even used my connection ready settings incase something is not matching but it's not working either.
I cann't figure out the problem ... because I can access the database from all my pages except the mydemo2.php which gives this error.

Thanks


	Logged

alabdulelah

Jr. Member

Posts: 9

Re: Sigma Grid 2.1 Tutorial 2 - Load data from database

« Reply #7 on: January 10, 2009, 06:34:05 PM »

Anyone who can help ? Huh


	Logged

steven

Moderator
Hero Member

Posts: 544

Re: Sigma Grid 2.1 Tutorial 2 - Load data from database

« Reply #8 on: January 12, 2009, 12:44:44 AM »

500 = Internal Server Error
That means you have some syntax error inside your php file. Please check your php error log to see what really happened.


	Logged

Sigma AJAX Data Grid - Easy to Integrate with PHP, ASP Classic, ASP.NET & Java
Sigma PHP Ajax Framework - WYSIWYG GUI Builder With Tree, Grid, Treegrid, Dialog, Tab & More Component

alabdulelah

Jr. Member

Posts: 9

Re: Sigma Grid 2.1 Tutorial 2 - Load data from database

« Reply #9 on: January 12, 2009, 07:41:37 AM »

Hi,
Thank you for your reply, I checked every single line.. I can't find any error in it... I even changed the connection to what I am using and working with other pages, but even with that I receive the same error , so I included the code which is the code I copied for your subject. please tell me if you find any problem with it.
Thanks


	Logged

djblade311

Newbie

Posts: 3

Re: Sigma Grid 2.1 Tutorial 2 - Load data from database

« Reply #10 on: January 13, 2009, 08:50:43 AM »

your code looks fine with no syntax errors. The other possibility is that the error resides in Connect/htndb.php and/or both php scripts may not have the correct file permissions (775)


	Logged

alabdulelah

Jr. Member

Posts: 9

Re: Sigma Grid 2.1 Tutorial 2 - Load data from database

« Reply #11 on: January 14, 2009, 04:45:47 AM »

Thanks for your reply, I downloaded a ready example from the website and it is working perfectly with my php file. it seems that the code was not checked before it was published, also I found an error on the code because it tries to call a file called gt_const.js which does not exist at all in the specified folder.
Thanks again


	Logged

titobundy

Jr. Member

Posts: 18

Re: Sigma Grid 2.1 Tutorial 2 - Load data from database

« Reply #12 on: January 16, 2009, 12:45:29 PM »

Hi.
I have a database with records codified in iso-8859-1 and the php function json_encode throws an error to me because of it.

example
field_name : 'Ñandú'

How can I solve it?


	Logged

amit.patel

Jr. Member

Posts: 15

Re: Sigma Grid 2.1 Tutorial 2 - Load data from database

« Reply #13 on: February 13, 2009, 04:43:51 AM »

Thanks steven For your Good Work
   Now i want to know is it possible to post data from server side when we click on paging button i.e next,previous,first,last. Means in load url we get Page num as json string but i want to also pass some extra parameter so how can i pass this extra parameters. As i used "parameters":{} but it doesn't change the value of parameter . It's give value only when we refresh the page.

   So i want to get the extra parameter's value on server side when i click on paging button for my sql query? so how it is possible.Waiting for your reply.

Thanks and regards,
---Amit Patel


« Last Edit: February 13, 2009, 04:46:39 AM by amit.patel »	Logged

NanoThoro

Newbie

Posts: 1

Re: Sigma Grid 2.1 Tutorial 2 - Load data from database

« Reply #14 on: March 30, 2011, 01:15:24 AM »

I have data 40 record, but I can show 20 record (2 page, 10 record per page). How to I can show all record?


	Logged

Pages: [1] 2

« previous next »